存储过程及Comm.cs类的创建

2013-09-25 13:08:59

一、准备工作

　　首先创建一个数据库，如创建“试用期公务员管理”数据库；再创建一个Comm.cs类，添加代码如下：

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

namespace Business
{
    /// <summary>
    /// 通用类
    /// </summary>
    public class Comm
    {
        /// <summary>
        /// 通过DH获取Code/Chinese
        /// </summary>
        /// <param name="DH">DH</param>
        /// <returns>返回DataTable</returns>
        public static DataTable GetCodeByDH(string DH)
        {
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CNHRConnectionStrings"].ConnectionString);

            try
            {
                conn.Open();

                //sda.SelectCommand = GenerateInsertCommand(conn, dropYear.SelectedValue, "%" + UnitName + "%", unsub, sub, strlock, base.AccountID);//
                SqlCommand cmd = new SqlCommand("根据DH获取代码表", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter para1 = new SqlParameter("@DH", SqlDbType.NVarChar);
                para1.Value = DH;
                cmd.Parameters.Add(para1);

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
            finally
            {
                conn.Close();
            }
        }            

}

二、数据获取

1、SQL中，在存储过程中新建“数据获取”，更改红色高亮部分。

USE [试用期公务员管理]
GO
/****** Object:  StoredProcedure [dbo].[数据获取]    Script Date: 2013/9/25 13:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER（先Create） PROCEDURE [dbo].[数据获取]
    -- Add the parameters for the stored procedure here
    @表名 nvarchar(50),
    @条件 nvarchar(100),
    @字段名 nvarchar(100),
    @排序字段    nvarchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @sql nvarchar(max)
    set @sql = 'select * from '+@表名+' where '+@字段名+' = '''+@条件 +''' order by '+@排序字段+''
    exec sp_executesql @sql
END

2、在Comm.cs类中，添加“获取数据”方法：

/// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="_表名">表名</param>
        /// <param name="_字段名">字段名</param>
        /// <param name="_条件">条件</param>
        /// <param name="_排序字段">排序字段</param>
        /// <returns>返回DataTable</returns>
        public static DataTable 获取数据(string _表名, string _字段名, string _条件, string _排序字段)
        {
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CNHRConnectionStrings"].ConnectionString);

            try
            {
                conn.Open();

                //sda.SelectCommand = GenerateInsertCommand(conn, dropYear.SelectedValue, "%" + UnitName + "%", unsub, sub, strlock, base.AccountID);//
                SqlCommand cmd = new SqlCommand("数据获取", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter para1 = new SqlParameter("@表名", SqlDbType.NVarChar);
                para1.Value = _表名;
                cmd.Parameters.Add(para1);

                SqlParameter para2 = new SqlParameter("@字段名", SqlDbType.NVarChar);
                para2.Value = _字段名;
                cmd.Parameters.Add(para2);

                SqlParameter para3 = new SqlParameter("@条件", SqlDbType.NVarChar);
                para3.Value = _条件;
                cmd.Parameters.Add(para3);

                SqlParameter para4 = new SqlParameter("@排序字段", SqlDbType.NVarChar);
                para4.Value = _排序字段;
                cmd.Parameters.Add(para4);

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;

            }
            finally
            {
                conn.Close();
            }
        }

三、数据更新

1、SQL中，在存储过程中新建“数据更新”，更改红色高亮部分。

USE [试用期公务员管理]
GO
/****** Object:  StoredProcedure [dbo].[数据更新]    Script Date: 2013/9/25 13:17:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[数据更新]
    -- Add the parameters for the stored procedure here
    @ID uniqueidentifier,
    @表名 nvarchar(max),
    @更新值 nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @sql nvarchar(max)
    set @sql = 'update '+@表名+' set '+@更新值+' where ID = '''+cast(@ID as nvarchar(max))+''' '
    exec sp_executesql @sql
END

2、在Comm.cs类中，添加“更新数据”方法：

/// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="_表名">表名</param>
        /// <param name="_字段名">字段名</param>
        /// <param name="_条件">条件</param>
        /// <param name="_排序字段">排序字段</param>
        /// <returns>返回DataTable</returns>
        public static int 更新数据(string ID, string 表名, string 更新值)
        {
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CNHRConnectionStrings"].ConnectionString);

            try
            {
                conn.Open();

                //sda.SelectCommand = GenerateInsertCommand(conn, dropYear.SelectedValue, "%" + UnitName + "%", unsub, sub, strlock, base.AccountID);//
                SqlCommand cmd = new SqlCommand("[数据更新]", conn);
                cmd.CommandType = CommandType.StoredProcedure;

               

                SqlParameter para1 = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                para1.Value = new Guid(ID);
                cmd.Parameters.Add(para1);

                

                SqlParameter para2 = new SqlParameter("@表名", SqlDbType.NVarChar);
                para2.Value = 表名;
                cmd.Parameters.Add(para2);

                SqlParameter para3 = new SqlParameter("@更新值", SqlDbType.NVarChar);
                para3.Value = 更新值;
                cmd.Parameters.Add(para3);

                return cmd.ExecuteNonQuery();

            }
            finally
            {
                conn.Close();
            }
        }

四、数据新增

1、SQL中，在存储过程中新建“数据新增”，更改红色高亮部分。

USE [试用期公务员管理]
GO
/****** Object:  StoredProcedure [dbo].[数据新增]    Script Date: 2013/9/25 13:20:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[数据新增]
    -- Add the parameters for the stored procedure here
    @表名 nvarchar(max)
    ,@新增字段 nvarchar(max) --id,人员id,……
    ,@新增值 nvarchar(max)  --'aa','bb'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @sql nvarchar(max)
    set @sql = 'insert into '+@表名+' ('+@新增字段+') select '+@新增值+' '
    exec sp_executesql @sql
END

2、在Comm.cs类中，添加“新增数据”方法：

/// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="_表名">表名</param>
        /// <param name="_字段名">字段名</param>
        /// <param name="_条件">条件</param>
        /// <param name="_排序字段">排序字段</param>
        /// <returns>返回DataTable</returns>
        public static int 新增数据(string _表名, string 新增字段, string 新增值)
        {
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CNHRConnectionStrings"].ConnectionString);

            try
            {
                conn.Open();

                //sda.SelectCommand = GenerateInsertCommand(conn, dropYear.SelectedValue, "%" + UnitName + "%", unsub, sub, strlock, base.AccountID);//
                SqlCommand cmd = new SqlCommand("[数据新增]", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter para1 = new SqlParameter("@表名", SqlDbType.NVarChar);
                para1.Value = _表名;
                cmd.Parameters.Add(para1);

                SqlParameter para2 = new SqlParameter("@新增字段", SqlDbType.NVarChar);
                para2.Value = 新增字段;
                cmd.Parameters.Add(para2);

                SqlParameter para3 = new SqlParameter("@新增值", SqlDbType.NVarChar);
                para3.Value = 新增值;
                cmd.Parameters.Add(para3);


                return cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

五、删除数据

1、SQL中，在存储过程中新建“数据删除”，更改红色高亮部分。

USE [试用期公务员管理]
GO
/****** Object:  StoredProcedure [dbo].[数据删除]    Script Date: 2013/9/25 13:23:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[数据删除]
    -- Add the parameters for the stored procedure here
    @ID uniqueidentifier,
    @表名 nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @sql nvarchar(max)
    set @sql = 'delete '+@表名+' where ID = '''+cast(@ID as nvarchar(max))+''' '
    exec sp_executesql @sql
END
2、在Comm.cs类中，添加“删除数据”方法：

/// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="_表名">表名</param>
        /// <param name="_字段名">字段名</param>
        /// <param name="_条件">条件</param>
        /// <param name="_排序字段">排序字段</param>
        /// <returns>返回DataTable</returns>
        public static int 删除数据(string _表名, string ID)
        {
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CNHRConnectionStrings"].ConnectionString);

            try
            {
                conn.Open();

                //sda.SelectCommand = GenerateInsertCommand(conn, dropYear.SelectedValue, "%" + UnitName + "%", unsub, sub, strlock, base.AccountID);//
                SqlCommand cmd = new SqlCommand("数据删除", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter para1 = new SqlParameter("@表名", SqlDbType.NVarChar);
                para1.Value = _表名;
                cmd.Parameters.Add(para1);

                SqlParameter para2 = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                para2.Value = new Guid(ID);
                cmd.Parameters.Add(para2);

              
                return cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

六、调用Comm.cs中的方法

　　xxx=Comm.删除数据();

　　yyy=Comm.更新数据();

七、DataGrid中，按钮编辑实现数据更新的另一种写法，不需要重新写一个专门的更新存储过程，直接调用公用的更新即可，如下：

　　在存储过程中对应的类中，加类似这样的代码：

　　   /// <summary>
        /// 更新试用期人员
        /// </summary>
        /// <param name="AccountID">ID</param>
        /// <param name="_信息ID">信息</param>
        /// <param name="_用户名">用户名</param>
        /// <param name="_姓名">姓名</param>
        /// <param name="_性别">性别</param>
        /// <returns>返回受影响行数</returns>
        public static int UpdateAccount(string AccountID, string _信息ID, string _用户名, string _姓名, string _性别)
        {
            StringBuilder sb_UpdateValue = new StringBuilder();

            sb_UpdateValue.AppendFormat("信息='{0}',", _信息ID);
            sb_UpdateValue.AppendFormat("用户名='{0}',", _用户名);
            sb_UpdateValue.AppendFormat("姓名='{0}',", _姓名);
            sb_UpdateValue.AppendFormat("性别='{0}',", _性别);           

            return Comm.更新数据(AccountID, "信息表", sb_UpdateValue.ToString());
        }

　　在更新按钮的事件中代码如下：

　　  protected void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                AccountRule.UpdateAccount(ID, Drp信息.SelectedValue, txt用户名.Text.Trim(), txtName.Text.Trim(), DrpSex.SelectedValue);

　　　　　　Response.Write("<script>alert('修改成功!');window.opener.location.href=window.opener.location.href;window.close();</script>");
            }
            catch (Exception ex)
            {
                OutInfo(ex.Message);
            }
        }

八、总结

　　以上是一种通用的增删改查的方法，可以单独针对自己需要的方法创建SQL语句于存储过程中，并创建相应的类，进而实现调用。