asp.net 批量操作数据库方法
asp.net 批量操作数据库方法
作者:Clear 日期:2007-01-11
进入2007年发的第一个帖子居然是技术贴,看来今天对于我来说可能会是一个技术年了.
.NET的门槛相对于JAVA来说要低一点点,因为一想起当年架BSD的时候,看着满屏的字符,天天ls,ls的心有余悸.还有那痛苦的bind配置历程让我真的对开源环境不敢涉足.所以我决定还是好好搞好.NET:
一是身边.NET高手如云,有绝对的优势;
二是要详细地学好高级语言,这样将来对JAVA入门会方便一点点,不过很多人说搞了.NET再搞JAVA是很痛苦的,但是之前也有人说搞了JS再搞高级语言是很痛苦的,既然第一关我已经熬过来了，也无所谓后面会怎么痛苦,反正目的很简单,我只想更加了解WEB开发的过程;
三是对WPF/E的强化,个人认为这个东西的前途比FLASH光明,应用性更广阔,毫不夸张地说,将来会出现许多新的WEB应用.
今天封装了一下对数据库的批量操作,很明显代码里面有JS的痕迹,没办法,这也许就是后遗症吧.先看看封装的代码:
程序代码
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// 数据操作扩展
/// </summary>
public class ExSQLAction
{
private static string connString = ConfigurationManager.ConnectionStrings["DefaultConnectionstrings"].ConnectionString;
private SqlConnection DBconn = new SqlConnection(connString);
public ExSQLAction()
{
}
/// <summary>
/// 批量数据处理
/// <para><param name="ProcedureName">存储过程或SQL语句</param></para>
/// <para><param name="NameArray">存储过程中定义的变量名称</param></para>
/// <para><param name="ValueArray">需要填充的值</param></para>
/// <example>NameArray["category_ID","category_Sort"];
/// new string[][] ValueArray = new string[][]{
/// new string[] = {"1","2","3"},
/// new string[] = {"4","5","6"}
/// }
/// </example>
/// </summary>
public string BatchSetDB(string ProcedureName, string[] NameArray, string[][] ValueArray)
{
SqlTransaction tran = null;
SqlConnection DBconn = this.DBconn;
try
{
DBconn.Open();
tran = DBconn.BeginTransaction();
SqlCommand mySqlCommand = new SqlCommand(ProcedureName, DBconn);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Transaction = tran;
for (int i = 0, j = ValueArray[0].Length; i < j; i++)
{
mySqlCommand.Parameters.Clear();
for (int m = 0, n = ValueArray.Length; m < n; m++)
{
mySqlCommand.Parameters.Add(new SqlParameter("@" + NameArray[m], ValueArray[m][i]));
}
mySqlCommand.ExecuteNonQuery();
}
tran.Commit();
}
catch (SqlException ex)
{
tran.Rollback();
throw ex;
}
finally
{
DBconn.Close();
}
return "ok";
}
}
首先需要传入存储过程的名称,或者SQL语句,然后将在存储过程中定语的名称放在一个一维数组中传入,然后将名称所对应的值使用交错数组传入.关于交错数组我也写了一个由Request获取值然后生成交错数组的方法:
程序代码
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// 扩展数组方法
/// </summary>
public class ExArray
{
public ExArray()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public string[][] GetRequestValue(string[] RequestName, HttpRequest Request)
{
string[][] myString = new string[RequestName.Length][];
for (int i = 0, j = RequestName.Length; i < j; i++)
{
myString.SetValue(Request[RequestName[i]].Split(Convert.ToChar(",")),i);
}
return myString;
}
}
传入一个Request需要得到值名称的一维数组,然后将Request对象传入,产生一个交错数组.
完整操作如下:
程序代码
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
public partial class admin_site_category : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string UpdateCategorySort = "";
if (Request.Form["action"] == "SaveSort")
{
string[] NameArray = new string[] { "category_ID", "category_Sort" };
string[][] ValueArray = new ExArray().GetRequestValue(new string[] { "id", "sort" }, Request);
UpdateCategorySort = new ExSQLAction().BatchSetDB("UpdateCategorySort", NameArray, ValueArray);
}
else if (Request.QueryString["action"] == "del")
{
string[] NameArray = new string[] { "category_ID" };
string[][] ValueArray = new ExArray().GetRequestValue(new string[] { "id" }, Request);
UpdateCategorySort = new ExSQLAction().BatchSetDB("DelCategory", NameArray, ValueArray);
}
if (UpdateCategorySort == "ok")
{
Response.Redirect(Request.Path.ToString(), true);
}
批量删除的存储过程如下:
程序代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Alter PROCEDURE [dbo].[DelCategory]
@category_ID int
AS
BEGIN
SET NOCOUNT ON;
-- 定义一个被删除数据数据的变量
DECLARE
@DelCount INT,
@category_Level INT,
@category_Parent INT
-- 查找父ID
Select
@category_Level = category_Level,
@category_Parent = category_Parent
FROM [viewdatabase0716].[dbo].[view_category]
Where category_ID = @category_ID
-- 删除该ID的数据
Delete FROM [viewdatabase0716].[dbo].[view_category]
Where category_ID = @category_ID
SET @DelCount = 1
--如果为子集,在父级中减去
IF @category_Level > 1
BEGIN
Update view_category
SET category_Subs = category_Subs-@DelCount
Where category_ID = @category_Parent
End
--如果为父级，需要批量删除所有子集
ELSE IF @category_Level = 1
BEGIN
-- 查找改数据的子级数量并加上之前删除的数据
Select @DelCount = count(category_Parent)+@DelCount
FROM [viewdatabase0716].[dbo].[view_category]
Where category_Parent = @category_ID
-- 删除所有相关子级
Delete FROM [viewdatabase0716].[dbo].[view_category]
Where category_Parent = @category_ID
END
-- 在总信息里面减去删除的数据数量
Update view_site_info
SET site_Category = site_Category-@DelCount
END
其实很多事情使用微软提供的控件可能更加方便,但我的一贯思想是,通用的控件一定效率不高,所以我并没有使用Dataset的Update去批量操作.
目前View的后台开发已经达到40%,估计在月底View就可以上线了.
--
http://www.cnrui.cn/blog/article.asp?id=170
.NET的门槛相对于JAVA来说要低一点点,因为一想起当年架BSD的时候,看着满屏的字符,天天ls,ls的心有余悸.还有那痛苦的bind配置历程让我真的对开源环境不敢涉足.所以我决定还是好好搞好.NET:
一是身边.NET高手如云,有绝对的优势;
二是要详细地学好高级语言,这样将来对JAVA入门会方便一点点,不过很多人说搞了.NET再搞JAVA是很痛苦的,但是之前也有人说搞了JS再搞高级语言是很痛苦的,既然第一关我已经熬过来了，也无所谓后面会怎么痛苦,反正目的很简单,我只想更加了解WEB开发的过程;
三是对WPF/E的强化,个人认为这个东西的前途比FLASH光明,应用性更广阔,毫不夸张地说,将来会出现许多新的WEB应用.
今天封装了一下对数据库的批量操作,很明显代码里面有JS的痕迹,没办法,这也许就是后遗症吧.先看看封装的代码:
程序代码using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// 数据操作扩展
/// </summary>
public class ExSQLAction
{
private static string connString = ConfigurationManager.ConnectionStrings["DefaultConnectionstrings"].ConnectionString;
private SqlConnection DBconn = new SqlConnection(connString);
public ExSQLAction()
{
}
/// <summary>
/// 批量数据处理
/// <para><param name="ProcedureName">存储过程或SQL语句</param></para>
/// <para><param name="NameArray">存储过程中定义的变量名称</param></para>
/// <para><param name="ValueArray">需要填充的值</param></para>
/// <example>NameArray["category_ID","category_Sort"];
/// new string[][] ValueArray = new string[][]{
/// new string[] = {"1","2","3"},
/// new string[] = {"4","5","6"}
/// }
/// </example>
/// </summary>
public string BatchSetDB(string ProcedureName, string[] NameArray, string[][] ValueArray)
{
SqlTransaction tran = null;
SqlConnection DBconn = this.DBconn;
try
{
DBconn.Open();
tran = DBconn.BeginTransaction();
SqlCommand mySqlCommand = new SqlCommand(ProcedureName, DBconn);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Transaction = tran;
for (int i = 0, j = ValueArray[0].Length; i < j; i++)
{
mySqlCommand.Parameters.Clear();
for (int m = 0, n = ValueArray.Length; m < n; m++)
{
mySqlCommand.Parameters.Add(new SqlParameter("@" + NameArray[m], ValueArray[m][i]));
}
mySqlCommand.ExecuteNonQuery();
}
tran.Commit();
}
catch (SqlException ex)
{
tran.Rollback();
throw ex;
}
finally
{
DBconn.Close();
}
return "ok";
}
}
首先需要传入存储过程的名称,或者SQL语句,然后将在存储过程中定语的名称放在一个一维数组中传入,然后将名称所对应的值使用交错数组传入.关于交错数组我也写了一个由Request获取值然后生成交错数组的方法:
程序代码using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// 扩展数组方法
/// </summary>
public class ExArray
{
public ExArray()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public string[][] GetRequestValue(string[] RequestName, HttpRequest Request)
{
string[][] myString = new string[RequestName.Length][];
for (int i = 0, j = RequestName.Length; i < j; i++)
{
myString.SetValue(Request[RequestName[i]].Split(Convert.ToChar(",")),i);
}
return myString;
}
}
传入一个Request需要得到值名称的一维数组,然后将Request对象传入,产生一个交错数组.
完整操作如下:
程序代码using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
public partial class admin_site_category : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string UpdateCategorySort = "";
if (Request.Form["action"] == "SaveSort")
{
string[] NameArray = new string[] { "category_ID", "category_Sort" };
string[][] ValueArray = new ExArray().GetRequestValue(new string[] { "id", "sort" }, Request);
UpdateCategorySort = new ExSQLAction().BatchSetDB("UpdateCategorySort", NameArray, ValueArray);
}
else if (Request.QueryString["action"] == "del")
{
string[] NameArray = new string[] { "category_ID" };
string[][] ValueArray = new ExArray().GetRequestValue(new string[] { "id" }, Request);
UpdateCategorySort = new ExSQLAction().BatchSetDB("DelCategory", NameArray, ValueArray);
}
if (UpdateCategorySort == "ok")
{
Response.Redirect(Request.Path.ToString(), true);
}
批量删除的存储过程如下:
程序代码set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Alter PROCEDURE [dbo].[DelCategory]
@category_ID int
AS
BEGIN
SET NOCOUNT ON;
-- 定义一个被删除数据数据的变量
DECLARE
@DelCount INT,
@category_Level INT,
@category_Parent INT
-- 查找父ID
Select
@category_Level = category_Level,
@category_Parent = category_Parent
FROM [viewdatabase0716].[dbo].[view_category]
Where category_ID = @category_ID
-- 删除该ID的数据
Delete FROM [viewdatabase0716].[dbo].[view_category]
Where category_ID = @category_ID
SET @DelCount = 1
--如果为子集,在父级中减去
IF @category_Level > 1
BEGIN
Update view_category
SET category_Subs = category_Subs-@DelCount
Where category_ID = @category_Parent
End
--如果为父级，需要批量删除所有子集
ELSE IF @category_Level = 1
BEGIN
-- 查找改数据的子级数量并加上之前删除的数据
Select @DelCount = count(category_Parent)+@DelCount
FROM [viewdatabase0716].[dbo].[view_category]
Where category_Parent = @category_ID
-- 删除所有相关子级
Delete FROM [viewdatabase0716].[dbo].[view_category]
Where category_Parent = @category_ID
END
-- 在总信息里面减去删除的数据数量
Update view_site_info
SET site_Category = site_Category-@DelCount
END
其实很多事情使用微软提供的控件可能更加方便,但我的一贯思想是,通用的控件一定效率不高,所以我并没有使用Dataset的Update去批量操作.
目前View的后台开发已经达到40%,估计在月底View就可以上线了.
--
http://www.cnrui.cn/blog/article.asp?id=170
