【利用存储过程和三层架构完成新闻发布】

1，本实例是新闻发布系统，采用三层架构，执行操作全部使用存储过程，实现添加新闻，删除新闻，查看新闻，修改新闻。并且最后用存储过程实现分页公共。本实例在vs2010中能够完整运行，以下是具体实现：

2 数据设计和存储过程的实现

数据库内容：

数据库设计和已创建的存储过程：

具体存储过程：

1添加新闻信息存储过程：

View Code

 1 USE [Blogs]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[AddNews]    Script Date: 02/26/2013 13:42:45 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 
 9 -- =============================================
10 -- Author:        白宁超
11 -- Create date: 2013-2-24 10:08:20
12 -- Description:    添加新闻
13 -- =============================================
14 ALTER PROCEDURE [dbo].[AddNews]
15     @n_name varchar(50),
16     @n_class varchar(50),
17     @n_auter varchar(50),
18     @n_time datetime,
19     @n_content nvarchar(max),
20     @id int output
21 AS
22 BEGIN
23     insert into News values(@n_name,@n_class,@n_auter,@n_time,@n_content)
24     select @id=MAX(n_id) from News
25 END

2删除新闻信息存储过程

View Code

USE [Blogs]
GO
/****** Object:  StoredProcedure [dbo].[DeleteNews]    Script Date: 02/26/2013 13:44:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        白宁超
-- Create date: 2013-2-24 10:47:22
-- Description:    删除新闻
-- =============================================
ALTER PROCEDURE [dbo].[DeleteNews] 
    @id int
AS
BEGIN
    delete News where n_id=@id
    select * from News 
END

3修改加新闻信息存储过程

View Code

USE [Blogs]
GO
/****** Object:  StoredProcedure [dbo].[UpdateNews]    Script Date: 02/26/2013 13:45:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        白宁超
-- Create date: 2013-2-24 10:30:34
-- Description:    修改新闻信息
-- =============================================
ALTER PROCEDURE [dbo].[UpdateNews] 
    @n_name varchar(50),
    @n_class varchar(50),
    @n_auter varchar(50),
    @n_time datetime,
    @n_content nvarchar(max),
    @id int 
AS
BEGIN
    update News set n_name=@n_name,n_class=@n_class,
    n_auter=@n_auter,n_time=@n_time,n_content=@n_content
    where n_id=@id 
    select * from News where n_id=@id 
END

4查看新闻信息存储过程

View Code

USE [Blogs]
GO
/****** Object:  StoredProcedure [dbo].[SelectNews]    Script Date: 02/26/2013 13:46:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        白宁超
-- Create date: 2013-2-24 10:40:33
-- Description:    查看新闻信息
-- =============================================
ALTER PROCEDURE [dbo].[SelectNews] 
    
AS
BEGIN
    select * from News 
END

5新闻信息分页存储过程

View Code

USE [Blogs]
GO
/****** Object:  StoredProcedure [dbo].[GetDataByIndex]    Script Date: 02/26/2013 13:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        白宁超
-- Create date: 2013-2-24 17:05:23
-- Description:    实现分页功能
-- =============================================
ALTER PROCEDURE [dbo].[GetDataByIndex]
     @pageindex int,
     @pagecount int output
AS
BEGIN
     declare @sql nvarchar(1000)
     declare @Pagec int
     set @sql='select top 5 * from News where n_id not in (select top '+CAST(@pageindex*5 as nvarchar(10))+' n_id from News)'
     select @pagecount=COUNT(*) from News
     select @pagec = COUNT(*) from News 
     set @pagecount = (@pagec+9)/5
     exec (@sql)
END

2，vs2010中对存储过程的调用

运行整体效果：

前台代码：

View Code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="prodemo.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        Height="230px" Width="706px">
        <Columns>
            <asp:BoundField DataField="n_id" HeaderText="编号" />
            <asp:BoundField DataField="n_name" HeaderText="新闻题目" />
            <asp:BoundField DataField="n_class" HeaderText="新闻类别" />
            <asp:BoundField DataField="n_auter" HeaderText="新闻作者" />
            <asp:BoundField DataField="n_time" HeaderText="发布时间" />
            <asp:BoundField DataField="n_content" HeaderText="新闻内容" />
        </Columns>
    </asp:GridView>
     <table>
         <tr>
                 <td>
                     <asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click" >首页</asp:LinkButton></td>
                 <td>
                    <asp:TextBox ID="TextBox7" runat="server" Width="29px"></asp:TextBox></td>
                   
                <td>
                     <asp:LinkButton ID="LinkButton5" runat="server" onclick="LinkButton5_Click" >GO</asp:LinkButton></td>
                 <td>
                     <asp:Label ID="Label1" runat="server" Text="第"></asp:Label></td>
                 <td>
                     <asp:Label ID="index" runat="server" Text="1"></asp:Label></td>
                 <td>
                     <asp:Label ID="Label3" runat="server" Text="页"></asp:Label></td>
                 <td>
                     <asp:Label ID="Label2" runat="server" Text="共"></asp:Label></td>
                 <td>
                     <asp:Label ID="labcount" runat="server" Text=""></asp:Label></td>
                 <td>
                     <asp:Label ID="Label5" runat="server" Text="页"></asp:Label></td>
                 <td>
                     <asp:LinkButton ID="LinkButton2" runat="server" onclick="LinkButton2_Click" >上一页</asp:LinkButton></td>
                 <td>
                     <asp:LinkButton ID="LinkButton3" runat="server" onclick="LinkButton3_Click" >下一页</asp:LinkButton></td>
                 <td>
                    <asp:LinkButton ID="LinkButton4" runat="server" onclick="LinkButton4_Click" >尾页</asp:LinkButton></td>
             </tr>
         </table>
    <br />
    <hr style=" font-weight:bolder; background-color:Red">
    <br />
    <asp:TextBox ID="TextBox2" runat="server" Height="27px" Width="231px"></asp:TextBox>
    <asp:DropDownList ID="DropDownList1" runat="server" Height="17px" Width="151px">
        <asp:ListItem>娱乐新闻</asp:ListItem>
        <asp:ListItem>时政新闻</asp:ListItem>
        <asp:ListItem>军事新闻</asp:ListItem>
    </asp:DropDownList>
    <asp:TextBox ID="TextBox3" runat="server" Height="21px" Width="207px" 
        ></asp:TextBox>
    <br />
    <br />
    <asp:TextBox ID="TextBox4" runat="server" Height="65px" TextMode="MultiLine" 
        Width="646px"></asp:TextBox>
    <asp:Button ID="Button2" runat="server" Text="添加" onclick="Button2_Click" />
    &nbsp;&nbsp;&nbsp;&nbsp;
    <br />
    <asp:TextBox ID="TextBox5" runat="server" Height="21px" Width="66px"></asp:TextBox>
    &nbsp;&nbsp;
    <asp:Button ID="Button3" runat="server" Text="修改" onclick="Button3_Click" />
    <p>
    <asp:TextBox ID="TextBox6" runat="server" Height="21px" Width="66px"></asp:TextBox>
    &nbsp;&nbsp;&nbsp;
    <asp:Button ID="Button4" runat="server" Text="删除" onclick="Button4_Click" />
    </p>
    </form>
</body>
</html>

后台代码：
1三层架构实现，创建ui层prodemo，model层，和DAl层。然后dal对model引用，ui对dal引用。完成效果图如下：

删除信息DAl数据层代码：

View Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class DeleteNews
    {
        public bool DALDeleteNews(Model.SelectNews model)
        {
            SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true");
            SqlCommand cmd = new SqlCommand("DeleteNews", con);
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = model.id;
            int i = cmd.ExecuteNonQuery();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

ui层对dal层删除信息操作：

View Code

 /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button4_Click(object sender, EventArgs e)
        {
            DAL.DeleteNews ddn = new DAL.DeleteNews();
            Model.SelectNews msn = new Model.SelectNews();
            msn.id = Convert.ToInt32(TextBox6.Text);
            bool ds = ddn.DALDeleteNews(msn);
            if (ds == true)
            {
                BDSelectNews();
            }
            else
            {
                BDSelectNews();
            }
        }

以下查看/修改/添加信息与删除信息类似，仅显示ui层操作代码如下：

View Code

  /// <summary>
        /// 查看信息
        /// </summary>
        public void BDSelectNews()
        {
            DAL.Select das = new DAL.Select();
            DataSet ds = das.DALSelectNews();
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        /// <summary>
        /// 添加信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button2_Click(object sender, EventArgs e)
        {
            DAL.AddNews dan = new DAL.AddNews();
            Model.SelectNews msn = new Model.SelectNews();
            msn.name = TextBox2.Text;
            msn.myclass = DropDownList1.SelectedValue;
            msn.auter = TextBox3.Text;
            msn.time = DateTime.Now;
            msn.content = TextBox4.Text;
            bool ds = dan.DALAddNews(msn);
            if (ds == true)
            {
                BDSelectNews();
            }
            else
            {
                BDSelectNews();
            }
        }
        /// <summary>
        /// 更新信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button3_Click(object sender, EventArgs e)
        {
            DAL.updateNews dun = new DAL.updateNews();
            Model.SelectNews msn = new Model.SelectNews();
            msn.id = Convert.ToInt32(TextBox5.Text);
            msn.name = TextBox2.Text;
            msn.myclass = DropDownList1.SelectedValue;
            msn.auter = TextBox3.Text;
            msn.time = DateTime.Now;
            msn.content = TextBox4.Text;
            bool ds = dun.DALUpdateNews(msn);
            if (ds == true)
            {
                BDSelectNews();
            }
            else
            {
                BDSelectNews();
            }
        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button4_Click(object sender, EventArgs e)
        {
            DAL.DeleteNews ddn = new DAL.DeleteNews();
            Model.SelectNews msn = new Model.SelectNews();
            msn.id = Convert.ToInt32(TextBox6.Text);
            bool ds = ddn.DALDeleteNews(msn);
            if (ds == true)
            {
                BDSelectNews();
            }
            else
            {
                BDSelectNews();
            }
        }

以下是对分页的执行：

View Code

 /// <summary>
        /// 通用分页方法
        /// </summary>
        /// <param name="ide">页数参数</param>
        /// <returns></returns>
        public DataSet  bd(int ide)
        {
            using (SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true"))
            {
                SqlDataAdapter da = new SqlDataAdapter("GetDataByIndex", con);//加载分页的存储过程
                da.SelectCommand.CommandType = CommandType.StoredProcedure;//执行类型
                da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int)).Value = ide;//输入参数，显示第几页
                da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int)).Direction = ParameterDirection.Output;//输出参数，显示总页数
                DataSet ds = new DataSet();
                da.Fill(ds);
                labcount.Text = (Convert.ToInt32(da.SelectCommand.Parameters["@pagecount"].Value)-1).ToString();     //返回总页数
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds;
                }
                else{
                    return null;
                }
               
            }
        }

        /// <summary>
        /// 信息分页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="0">第一页显示数据</param>
        public void shuju()     //初始化数据
        {
            DataSet ds=bd(0);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            #region 初始页
            //using (SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true"))
            //{
            //    SqlDataAdapter da = new SqlDataAdapter("GetDataByIndex", con);
            //    da.SelectCommand.CommandType = CommandType.StoredProcedure;
            //    da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int)).Value = 0;
            //    da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int)).Direction = ParameterDirection.Output;
            //    DataSet ds = new DataSet();
            //    da.Fill(ds);
            //    labcount.Text = da.SelectCommand.Parameters["@pagecount"].Value.ToString();     //返回总页数
            //    this.GridView1.DataSource = ds;
            //    this.GridView1.DataBind();
            //}
            #endregion
            if (index.Text == "1")
            {
                LinkButton1.Enabled = false;
                LinkButton2.Enabled = false;
            }
            else if(index.Text==labcount.Text)
            {
                LinkButton3.Enabled = false;
                LinkButton4.Enabled = false;
            }
           
        }

        /// <summary>
        /// 首页事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton1_Click(object sender, EventArgs e)
        {
            index.Text = "1";
            DataSet ds = bd(0);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            if (index.Text == "1")
            {
                LinkButton1.Enabled = false;
                LinkButton2.Enabled = false;
                LinkButton3.Enabled = true;
                LinkButton4.Enabled = true;
            }
           
            #region
            //DataSet ds = bd(0);
            //GridView1.DataSource = ds;
            //GridView1.DataBind();
            //using (SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true"))
            //  {
                 
            //     SqlDataAdapter da= new SqlDataAdapter("GetDataByIndex", con);
            //     da.SelectCommand.CommandType = CommandType.StoredProcedure;
            //     da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
            //     da.SelectCommand.Parameters["@pageindex"].Value = 0;
            //     da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
            //     da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
            //    DataSet ds = new DataSet();
            //     da.Fill(ds);
            //     this.GridView1.DataSource = ds;
            //     this.GridView1.DataBind();
            // }

             //index.Text = "1";
 
             ////把首页和上一页设置为不可操作
             //LinkButton1.Enabled = false;
             //LinkButton2.Enabled = false;
             //LinkButton3.Enabled = true;
             //LinkButton4.Enabled = true;
            //LinkButton5.Enabled = true;
            #endregion
        }
        /// <summary>
        /// GO事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton5_Click(object sender, EventArgs e)
        {

              using (SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true"))
              {
                  int intpageindex = Convert.ToInt32(TextBox7.Text);
                  int pagecount = Convert.ToInt32(labcount.Text);
                  //判断，如果输入的数字大于总页数就返回
                  if (intpageindex > pagecount)
                      return;
                  else
                  {
                      DataSet ds = bd(intpageindex - 1);
                      GridView1.DataSource = ds;
                      GridView1.DataBind();
                      #region GO
                      //SqlDataAdapter da = new SqlDataAdapter("GetDataByIndex", con);
                      //da.SelectCommand.CommandType = CommandType.StoredProcedure;
                      //da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int)).Value = intpageindex - 1;
                      //da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int)).Direction = ParameterDirection.Output;
                      //DataSet ds = new DataSet();
                      //da.Fill(ds);
                      #endregion
                      if (TextBox7.Text == "1")       //判断如果输入1，首页和上一页不可操作，但是尾页和下一页可以操作
                      {
                          LinkButton1.Enabled = false;
                          LinkButton2.Enabled = false;

                          LinkButton3.Enabled = true;
                          LinkButton4.Enabled = true;
                      }
                      else if (TextBox7.Text == labcount.Text)        //判断如果输入的数等于总页数，首页和第一页可以操作，但是尾页可下一页不可操作
                      {
                          LinkButton3.Enabled = false;
                          LinkButton4.Enabled = false;

                          LinkButton1.Enabled = true;
                          LinkButton2.Enabled = true;
                      }
                      else                                           //如果是其他情况，首页第一页，尾页和下一页都可操作
                      {
                          LinkButton1.Enabled = true;
                          LinkButton2.Enabled = true;
                          LinkButton3.Enabled = true;
                          LinkButton4.Enabled = true;
                      }
                  }      
             }
             index.Text = TextBox7.Text;             //设置当前页数等于输入的页数     
        }
        /// <summary>
        /// 上一页事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton2_Click(object sender, EventArgs e)
        {
            int intpageindex = Convert.ToInt32(index.Text);//当前页数
            DataSet ds = bd(intpageindex-2);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            #region 上一页
            //using (SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true"))
            //  {
            //      string pageindex = index.Text;
            //      int intpageindex = Convert.ToInt32(pageindex);

            //      SqlDataAdapter da = new SqlDataAdapter("GetDataByIndex", con);
            //      da.SelectCommand.CommandType = CommandType.StoredProcedure;
            //     da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int)).Value = intpageindex - 2;
            //     da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int)).Direction = ParameterDirection.Output;
            //     DataSet ds = new DataSet();
            //     da.Fill(ds);
            //     labcount.Text = da.SelectCommand.Parameters["@pagecount"].Value.ToString();
            //     this.GridView1.DataSource = ds;
            //     this.GridView1.DataBind();
            //  }
#endregion
            index.Text = (intpageindex - 1).ToString();//上页数
             LinkButton3.Enabled = true;
             LinkButton4.Enabled = true;
 
             if (index.Text == "1")
             {
                 LinkButton1.Enabled = false;
                 LinkButton2.Enabled = false;
             }

        }
        /// <summary>
        /// 下一页事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton3_Click(object sender, EventArgs e)
        {
            int intpageindex = Convert.ToInt32(index.Text);//当前页数
            DataSet ds = bd(intpageindex);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            #region 下一页
            //using (SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true"))
            //  {
            //      string pageindex = index.Text;
            //      int intpageindex = Convert.ToInt32(pageindex);

            //      SqlDataAdapter da = new SqlDataAdapter("GetDataByIndex", con);
            //      da.SelectCommand.CommandType = CommandType.StoredProcedure;
            //     da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
            //     da.SelectCommand.Parameters["@pageindex"].Value = intpageindex;
 
            //     da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
            //     da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
            //     DataSet ds = new DataSet();
            //     da.Fill(ds);
 
            //     labcount.Text = da.SelectCommand.Parameters["@pagecount"].Value.ToString();
            //     this.GridView1.DataSource = ds;
            //     this.GridView1.DataBind();
            // }
            #endregion
            index.Text = (intpageindex + 1).ToString();//下页数
             LinkButton1.Enabled = true;
             LinkButton2.Enabled = true;
             if (index.Text == labcount.Text)
             {
                 LinkButton3.Enabled = false;
                 LinkButton4.Enabled = false;
             }

        }
        /// <summary>
        /// 尾页事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void LinkButton4_Click(object sender, EventArgs e)
        {
            int intindex = Convert.ToInt32(labcount.Text)-1;//当前页数
            DataSet ds = bd(intindex);
            GridView1.DataSource = ds;
            GridView1.DataBind();
           #region 尾页
            //using (SqlConnection con = new SqlConnection("server=.;database=Blogs;integrated security=true"))
            //{
            //    int intindex = Convert.ToInt32(labcount.Text);
            //    SqlDataAdapter da = new SqlDataAdapter("GetDataByIndex", con);
            //    da.SelectCommand.CommandType = CommandType.StoredProcedure;
            //    da.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
            //    da.SelectCommand.Parameters["@pageindex"].Value = intindex - 1;
            //    da.SelectCommand.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int));
            //    da.SelectCommand.Parameters["@pagecount"].Direction = ParameterDirection.Output;
            //    DataSet ds = new DataSet();
            //    da.Fill(ds);
            //    this.GridView1.DataSource = ds;
            //    this.GridView1.DataBind();
            //}
#endregion
             index.Text = labcount.Text;//尾页
             LinkButton1.Enabled = true;
             LinkButton2.Enabled = true;
             LinkButton3.Enabled = false;
             LinkButton4.Enabled = false;
        }

以上完成对存储过程基本操作，对三层架构深入理解。部分代码经过重构优化的，要不，分页的代码更多。当然还有优化之处，现在迫不及待把这些与大家分享。

posted @ 2013-02-26 14:00 伏草惟存阅读(1595) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部