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语句于存储过程中，并创建相应的类，进而实现调用。