总是非常怀念以前用PB写数据库应用的时候DataWindow功能的强大，现在用.Net开发系统的时候总感觉Microsoft提供的ADO.Net功能不够强大，执行一些数据操作很是麻烦，就自己写了一个自动生成更新DataTable的类，类源码如下：
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Collections;
using System.Text;
namespace DataAccess.SQLServerDAL
{
/// <summary>
/// SqlClass 的摘要说明。
/// </summary>
public class SqlClass
{
#region 返回执行Insert操作的SqlCommand
/// <summary>
/// 返回执行Insert操作的SqlCommand
/// </summary>
/// <param name="tablename">要更新的表名</param>
/// <param name="columns">要更新的列名的数组</param>
/// <param name="DbTypes">要更新的列的类型的数组</param>
/// <param name="srcColumns">要更新的列对应于数据源中的列名的数组</param>
/// <param name="identityCol">identity列</param>
/// <param name="trans">事务对象</param>
/// <returns>执行Insert操作的SqlCommand</returns>
public static SqlCommand InsertCommand(SqlTransaction trans, string tablename, UpdateDataColumnCollection updateDataColumnCollection, string identityCol)
{
StringBuilder insertString = new StringBuilder(), valueString = new StringBuilder();
SqlParameter MySqlParameter;
SqlCommand MyInsertCommand = new SqlCommand();
MyInsertCommand.Connection = trans.Connection;
MyInsertCommand.Transaction = trans;
insertString.Append("INSERT " + tablename + "(");
valueString.Append(") Values (");
foreach (UpdateDataColumn updateDataColumn in updateDataColumnCollection)
{
//对于IDENTITY列无需赋值
if (updateDataColumn.ColumnName != identityCol)
{
insertString.Append("[" + updateDataColumn.ColumnName + "], ");
valueString.Append("@" + updateDataColumn.ColumnName + ", ");
MySqlParameter = new SqlParameter();
MySqlParameter.ParameterName = "@" + updateDataColumn.ColumnName;
MySqlParameter.SqlDbType = updateDataColumn.SqlDbType;
MySqlParameter.Direction = ParameterDirection.Input;
MySqlParameter.SourceColumn = updateDataColumn.SourceColumn;
MySqlParameter.SourceVersion = DataRowVersion.Current;
MyInsertCommand.Parameters.Add(MySqlParameter);
}
}
insertString.Remove(insertString.Length - 2, 2);
valueString.Remove(valueString.Length - 2, 2);
insertString.Append(valueString.ToString() + ")");
if (identityCol != null && identityCol.Trim().Length > 0)
{
MySqlParameter = new SqlParameter();
MySqlParameter.ParameterName = "@" + identityCol;
MySqlParameter.SqlDbType = SqlDbType.Int;
MySqlParameter.Direction = ParameterDirection.Output;
MySqlParameter.SourceColumn = identityCol;
MySqlParameter.SourceVersion = DataRowVersion.Current;
MyInsertCommand.Parameters.Add(MySqlParameter);
insertString.Append(";SELECT @" + identityCol + " = @@IDENTITY");
}
MyInsertCommand.CommandText = insertString.ToString();
return MyInsertCommand;
}
/// <summary>
/// 返回执行Insert操作的SqlCommand，自动从系统表中取该表的列作为可更新的列
/// </summary>
/// <param name="tablename">要更新的表名</param>
/// <param name="identityCol">identity列</param>
/// <param name="trans">事务对象</param>
/// <returns>执行Insert操作的SqlCommand</returns>
public static SqlCommand InsertCommand(SqlTransaction trans, string tablename, string identityCol)
{
return InsertCommand(trans, tablename, GetColumnCollection(trans, tablename), identityCol);
}
/// <summary>
/// 返回执行Insert操作的SqlCommand，自动从系统表中取该表的列作为可更新的列
/// </summary>
/// <param name="tablename">要更新的表名</param>
/// <param name="trans">事务对象</param>
/// <returns>执行Insert操作的SqlCommand</returns>
public static SqlCommand InsertCommand(SqlTransaction trans, string tablename)
{
return InsertCommand(trans, tablename, GetIdentityColumn(trans, tablename));
}
#endregion InsertCommand
#region 返回执行Update操作的SqlCommand
/// <summary>
/// 返回执行Update操作的SqlCommand
/// </summary>
/// <param name="trans">事务对象</param>
/// <param name="tablename">要更新的表名</param>
/// <param name="updateDataColumnCollection">数据列集合</param>
/// <param name="keyColumnCollection">主键列集合</param>
/// <param name="identityCol">identity列</param>
/// <returns>执行Update操作的SqlCommand</returns>
public static SqlCommand UpdateCommand(SqlTransaction trans, string tablename,
UpdateDataColumnCollection updateDataColumnCollection, UpdateDataColumnCollection keyColumnCollection,
string identityCol)
{
StringBuilder execSqlString = new StringBuilder(), whereString = new StringBuilder();
SqlParameter MySqlParameter;
SqlCommand MyUpdateCommand = new SqlCommand();
MyUpdateCommand.Connection = trans.Connection;
MyUpdateCommand.Transaction = trans;
execSqlString.Append("UPDATE " + tablename + " SET ");
whereString.Append(" WHERE 1 = 1");
foreach (UpdateDataColumn keyColumn in keyColumnCollection)
{
whereString.Append(" AND [" + keyColumn + "] = @Original_" + keyColumn);
MySqlParameter = new SqlParameter();
MySqlParameter.ParameterName = "@Original_" + keyColumn.ColumnName;
MySqlParameter.SqlDbType = keyColumn.SqlDbType;
MySqlParameter.Direction = ParameterDirection.Input;
MySqlParameter.SourceColumn = keyColumn.SourceColumn;
MySqlParameter.SourceVersion = DataRowVersion.Original;
MyUpdateCommand.Parameters.Add(MySqlParameter);
}
foreach (UpdateDataColumn updateDataColumn in updateDataColumnCollection)
{
//对于IDENTITY列无需赋值
if (updateDataColumn.ColumnName != identityCol)
{
execSqlString.Append("[" + updateDataColumn.ColumnName + "] = @" + updateDataColumn.ColumnName + ", ");
MySqlParameter = new SqlParameter();
MySqlParameter.ParameterName = "@" + updateDataColumn.ColumnName;
MySqlParameter.SqlDbType = updateDataColumn.SqlDbType;
MySqlParameter.Direction = ParameterDirection.Input;
MySqlParameter.SourceColumn = updateDataColumn.SourceColumn;
MySqlParameter.SourceVersion = DataRowVersion.Current;
MyUpdateCommand.Parameters.Add(MySqlParameter);
}
//Key字段已包括在strWhere中
if (keyColumnCollection.FindByColumnName(updateDataColumn.ColumnName) == null && updateDataColumn.SqlDbType != SqlDbType.Text &&
updateDataColumn.SqlDbType != SqlDbType.NText && updateDataColumn.SqlDbType != SqlDbType.Image)
{
whereString.Append(" AND ([" + updateDataColumn.ColumnName + "] = @Original_" + updateDataColumn.ColumnName +
" OR (@Original_" + updateDataColumn.ColumnName + " IS NULL AND [" + updateDataColumn.ColumnName + "] IS NULL))");
MySqlParameter = new SqlParameter();
MySqlParameter.ParameterName = "@Original_" + updateDataColumn.ColumnName;
MySqlParameter.SqlDbType = updateDataColumn.SqlDbType;
MySqlParameter.Direction = ParameterDirection.Input;
MySqlParameter.SourceColumn = updateDataColumn.SourceColumn;
MySqlParameter.SourceVersion = DataRowVersion.Original;
MyUpdateCommand.Parameters.Add(MySqlParameter);
}
}
execSqlString = execSqlString.Remove(execSqlString.Length - 2, 2);
execSqlString.Append(whereString);
MyUpdateCommand.CommandText = execSqlString.ToString();
return MyUpdateCommand;
}
/// <summary>
/// 返回执行Update操作的SqlCommand，自动从系统表中取该表的列作为可更新的列
/// </summary>
/// <param name="trans">事务对象</param>
/// <param name="tablename">要更新的表名</param>
/// <param name="keyColumnCollection">主键列集合</param>
/// <param name="identityCol">identity列</param>
/// <returns>执行Update操作的SqlCommand</returns>
public static SqlCommand UpdateCommand(SqlTransaction trans, string tablename,
UpdateDataColumnCollection keyColumnCollection, string identityCol)
{
return UpdateCommand(trans, tablename, GetColumnCollection(trans, tablename), keyColumnCollection, identityCol);
}
/// <summary>
/// 返回执行Update操作的SqlCommand，自动从系统表中取该表的列作为可更新的列
/// </summary>
/// <param name="trans">事务对象</param>
/// <param name="tablename">要更新的表名</param>
/// <returns>执行Update操作的SqlCommand</returns>
public static SqlCommand UpdateCommand(SqlTransaction trans, string tablename)
{
return UpdateCommand(trans, tablename, GetKeyColumnCollection(trans, tablename), GetIdentityColumn(trans, tablename));
}
#endregion
#region 返回执行Delete操作的SqlCommand
/// <summary>
/// 返回执行Delete操作的SqlCommand
/// </summary>
/// <param name="trans">事务对象</param>
/// <param name="tablename">要更新的表名</param>
/// <param name="updateDataColumnCollection">数据列集合</param>
/// <param name="keyColumnCollection">主键列集合</param>
/// <returns>执行Delete操作的SqlCommand</returns>
public static SqlCommand DeleteCommand(SqlTransaction trans, string tablename,
UpdateDataColumnCollection updateDataColumnCollection, UpdateDataColumnCollection keyColumnCollection)
{
StringBuilder execSqlString = new StringBuilder();
SqlParameter MySqlParameter;
SqlCommand MyDeleteCommand = new SqlCommand();
MyDeleteCommand.Connection = trans.Connection;
MyDeleteCommand.Transaction = trans;
execSqlString.Append("DELETE FROM " + tablename + " WHERE 1 = 1");
foreach (UpdateDataColumn keyColumn in keyColumnCollection)
{
execSqlString.Append(" AND [" + keyColumn.ColumnName + "] = @Original_" + keyColumn.ColumnName);
MySqlParameter = new SqlParameter();
MySqlParameter.ParameterName = "@Original_" + keyColumn.ColumnName;
MySqlParameter.SqlDbType = keyColumn.SqlDbType;
MySqlParameter.Direction = ParameterDirection.Input;
MySqlParameter.SourceColumn = keyColumn.SourceColumn;
MySqlParameter.SourceVersion = DataRowVersion.Original;
MyDeleteCommand.Parameters.Add(MySqlParameter);
}
foreach (UpdateDataColumn updateDataColumn in updateDataColumnCollection)
{
//Key字段已包括在strWhere中
if (keyColumnCollection.FindByColumnName(updateDataColumn.ColumnName) == null && updateDataColumn.SqlDbType != SqlDbType.Text &&
updateDataColumn.SqlDbType != SqlDbType.NText && updateDataColumn.SqlDbType != SqlDbType.Image)
{
execSqlString.Append(" AND ([" + updateDataColumn.ColumnName + "] = @Original_" + updateDataColumn.ColumnName +
" OR (@Original_" + updateDataColumn.ColumnName + " IS NULL AND [" + updateDataColumn.ColumnName + "] IS NULL))");
MySqlParameter = new SqlParameter();
MySqlParameter.ParameterName = "@Original_" + updateDataColumn.ColumnName;
MySqlParameter.SqlDbType = updateDataColumn.SqlDbType;
MySqlParameter.Direction = ParameterDirection.Input;
MySqlParameter.SourceColumn = updateDataColumn.SourceColumn;
MySqlParameter.SourceVersion = DataRowVersion.Original;
MyDeleteCommand.Parameters.Add(MySqlParameter);
}
}
MyDeleteCommand.CommandText = execSqlString.ToString();
return MyDeleteCommand;
}
/// <summary>
/// 返回执行Delete操作的SqlCommand，自动从系统表中取该表的列作为可更新的列
/// </summary>
/// <param name="trans">事务对象</param>
/// <param name="tablename">要更新的表名</param>
/// <param name="keyColumnCollection">主键列集合</param>
/// <returns>执行Delete操作的SqlCommand</returns>
public static SqlCommand DeleteCommand(SqlTransaction trans, string tablename,
UpdateDataColumnCollection keyColumnCollection)
{
return DeleteCommand(trans, tablename, GetColumnCollection(trans, tablename), keyColumnCollection);
}
/// <summary>
/// 返回执行Delete操作的SqlCommand，自动从系统表中取该表的列作为可更新的列
/// </summary>
/// <param name="trans">事务对象</param>
/// <param name="tablename">要更新的表名</param>
/// <returns>执行Delete操作的SqlCommand</returns>
public static SqlCommand DeleteCommand(SqlTransaction trans, string tablename)
{
return DeleteCommand(trans, tablename, GetKeyColumnCollection(trans, tablename));
}
#endregion
#region 获取表的列集合
/// <summary>
/// 获取表的列集合
/// </summary>
/// <param name="tablename">表名</param>
/// <returns>数据列结构数组</returns>
private static UpdateDataColumnCollection GetColumnCollection(SqlTransaction trans, string tableName)
{
//取表列的SQL语句
string SQLSelect = "SELECT a.name, b.name AS type FROM syscolumns a, systypes b WHERE a.xtype = b.xtype and b.name <> 'sysname' " +
"AND a.id = (SELECT id FROM sysobjects WHERE name = @tablename)";
SqlParameter paramTableName = new SqlParameter("@tableName", SqlDbType.NVarChar, 128);
paramTableName.Value = tableName;
DataSet ds = new DataSet();
string[] tableNames = {tableName};
SqlHelper.FillDataset(trans, CommandType.Text, SQLSelect, ds, tableNames, paramTableName);
UpdateDataColumnCollection updateDataColumnCollection = new UpdateDataColumnCollection();
foreach (DataRow dr in ds.Tables[tableName].Rows)
{
updateDataColumnCollection.Add(dr["name"].ToString(), dr["type"].ToString());
}
return updateDataColumnCollection;
}
#endregion
#region 由SqlDbType名称返回SqlDbType枚举值
/// <summary>
/// 由SqlDbType名称返回SqlDbType枚举值
/// </summary>
/// <param name="type">类型名称</param>
/// <returns>对应的SqlDbType枚举值</returns>
public static SqlDbType GetSqlDbType(string type)
{
try
{
return (SqlDbType)Enum.Parse(typeof(SqlDbType), type, true);
}
catch
{
return SqlDbType.NVarChar;
}
}
#endregion GetSqlDbType
#region 更新数据行
/// <summary>
/// 更新数据行
/// </summary>
/// <param name="trans"></param>
/// <param name="tableName"></param>
/// <param name="dr"></param>
public static void UpdateDataRow(SqlTransaction trans, string tableName, DataRow dr)
{
SqlCommand c;
switch (dr.RowState)
{
case DataRowState.Deleted:
c = DeleteCommand(trans, tableName);
break;
case DataRowState.Modified:
c = UpdateCommand(trans, tableName);
break;
case DataRowState.Added:
c = InsertCommand(trans, tableName);
break;
default:
return;
}
foreach (SqlParameter p in c.Parameters)
{
p.Value = dr[p.SourceColumn, p.SourceVersion];
}
if (c.ExecuteNonQuery() == 0)
throw new DBConcurrencyException();
dr.AcceptChanges();
}
#endregion
#region 返回表的Identity列
/// <summary>
/// 返回表的Identity列
/// </summary>
/// <param name="trans">事物对象</param>
/// <param name="tableName">表名</param>
/// <returns>Identity列</returns>
public static string GetIdentityColumn(SqlTransaction trans, string tableName)
{
SqlParameter paramObjName = new SqlParameter("@objName", SqlDbType.NVarChar, 128);
paramObjName.Value = tableName;
string selectString = "SELECT name FROM syscolumns WHERE id = object_id(@objName) AND colstat & 1 = 1;";
object obj = SqlHelper.ExecuteScalar(trans, CommandType.Text, selectString, paramObjName);
if (obj != null && obj != DBNull.Value)
return obj.ToString();
else
return "";
}
#endregion
#region 返回表的主键列
/// <summary>
/// 返回表的主键列
/// </summary>
/// <param name="trans">事物对象</param>
/// <param name="tableName">表名</param>
/// <returns>主键列集合</returns>
public static UpdateDataColumnCollection GetKeyColumnCollection(SqlTransaction trans, string tableName)
{
UpdateDataColumnCollection keyDataColumnCollection = new UpdateDataColumnCollection();
string selectString = "select a.name as TableName, rtrim(b.name) as ColName, c.name as TypeName, " +
"case when h.id is not null then 'PK' else '' end as primarykey from sysobjects a, syscolumns b " +
"left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and " +
"f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id " +
"and b.colid = h.colid, systypes c where a.id = b.id and b.xtype = c.xtype and h.id is not null " +
"and a.Name = @tableName order by a.name, rtrim(b.name)";
SqlParameter paramTableName = new SqlParameter("@tableName", SqlDbType.NVarChar, 128);
paramTableName.Value = tableName;
DataSet ds = new DataSet();
string[] tableNames = {tableName};
SqlHelper.FillDataset(trans, CommandType.Text, selectString, ds, tableNames, paramTableName);
foreach (DataRow dr in ds.Tables[tableName].Rows)
{
keyDataColumnCollection.Add(dr["ColName"].ToString(), dr["TypeName"].ToString());
}
return keyDataColumnCollection;
}
#endregion
}
#region UpdateDataColumn集合
/// <summary>
/// UpdateDataColumn集合
/// </summary>
public class UpdateDataColumnCollection : CollectionBase
{
public UpdateDataColumnCollection()
{
}
#region 获取UpdateDataColumn对象
/// <summary>
/// 获取UpdateDataColumn对象
/// </summary>
public UpdateDataColumn this[string columnName]
{
get
{
return this.FindByColumnName(columnName);
}
}
/// <summary>
/// 获取UpdateDataColumn对象
/// </summary>
public UpdateDataColumn this[int index]
{
get
{
return (UpdateDataColumn)base.List[index];
}
}
#endregion
#region 通过数据列名称查找数据列
/// <summary>
/// 通过数据列名称查找数据列
/// </summary>
/// <param name="columnName">数据列名称</param>
/// <returns>数据列</returns>
public UpdateDataColumn FindByColumnName(string columnName)
{
foreach (object o in base.List)
{
UpdateDataColumn updateDataColumn = (UpdateDataColumn)o;
if (updateDataColumn.ColumnName == columnName)
return updateDataColumn;
}
return null;
}
#endregion
#region 向集合中添加数据列
/// <summary>
/// 向集合中添加数据列
/// </summary>
/// <param name="updateDataColumn">数据列对象</param>
public void Add(UpdateDataColumn updateDataColumn)
{
this.List.Add(updateDataColumn);
}
/// <summary>
/// 向集合中添加数据列
/// </summary>
/// <param name="columnName">数据列的名称</param>
/// <param name="sqlDbType">数据列的类型</param>
public void Add(string columnName, SqlDbType sqlDbType)
{
UpdateDataColumn updateDataColumn = new UpdateDataColumn(columnName, sqlDbType, columnName);
this.Add(updateDataColumn);
}
/// <summary>
/// 向集合中添加数据列
/// </summary>
/// <param name="columnName">数据列的名称</param>
/// <param name="sqlDbType">数据列的类型</param>
/// <param name="sourceColumn">源列的名称</param>
public void Add(string columnName, SqlDbType sqlDbType,string sourceColumn)
{
UpdateDataColumn updateDataColumn = new UpdateDataColumn(columnName, sqlDbType, sourceColumn);
this.Add(updateDataColumn);
}
/// <summary>
/// 向集合中添加数据列
/// </summary>
/// <param name="columnName">数据列的名称</param>
/// <param name="sqlDbType">数据列的类型</param>
public void Add(string columnName, string sqlDbType)
{
UpdateDataColumn updateDataColumn = new UpdateDataColumn(columnName, sqlDbType, columnName);
this.Add(updateDataColumn);
}
/// <summary>
/// 向集合中添加数据列
/// </summary>
/// <param name="columnName">数据列的名称</param>
/// <param name="sqlDbType">数据列的类型</param>
/// <param name="sourceColumn">源列的名称</param>
public void Add(string columnName, string sqlDbType, string sourceColumn)
{
UpdateDataColumn updateDataColumn = new UpdateDataColumn(columnName, sqlDbType, sourceColumn);
this.Add(updateDataColumn);
}
#endregion
#region 从集合中移除数据列
/// <summary>
/// 从集合中移除数据列
/// </summary>
/// <param name="updateDataColumn"></param>
public void Remove(UpdateDataColumn updateDataColumn)
{
this.List.Remove(updateDataColumn);
}
#endregion
}
#endregion
#region 数据列
/// <summary>
/// 数据更新时用到的数据列
/// </summary>
public class UpdateDataColumn
{
private string columnName; //数据列的名称
private SqlDbType sqlDbType;//数据列的类型
private string sourceColumn;//源列的名称
/// <summary>
/// 数据列
/// </summary>
/// <param name="columnName">数据列的名称</param>
/// <param name="sqlDbType">数据列的类型</param>
/// <param name="sourceColumn">源列的名称</param>
public UpdateDataColumn(string columnName, SqlDbType sqlDbType,string sourceColumn)
{
this.columnName = columnName;
this.sqlDbType = sqlDbType;
this.sourceColumn = sourceColumn;
}
/// <summary>
/// 数据列
/// </summary>
/// <param name="columnName">数据列的名称</param>
/// <param name="sqlDbType">数据列的类型</param>
/// <param name="sourceColumn">源列的名称</param>
public UpdateDataColumn(string columnName, string sqlDbType,string sourceColumn)
{
this.columnName = columnName;
this.sqlDbType = SqlClass.GetSqlDbType(sqlDbType);
this.sourceColumn = sourceColumn;
}
/// <summary>
/// 获取数据列的名称
/// </summary>
public string ColumnName
{
get{return this.columnName;}
}
/// <summary>
/// 获取数据列的类型
/// </summary>
public SqlDbType SqlDbType
{
get{return this.sqlDbType;}
}
/// <summary>
/// 获取源列的名称
/// </summary>
public string SourceColumn
{
get{return this.sourceColumn;}
}
/// <summary>
/// 返回ToString()
/// </summary>
/// <returns></returns>
public override string ToString()
{
return this.columnName.ToString ();
}
}
#endregion
}
调用示例：
DataSet ds = new DataSet();
//填充DataSet
SqlHelper.FillDataset(connectingString, CommandType.Text, "select * from Test", ds, new string[]{"Test"});
...
//更新DataSet
using (SqlConnection conn = new SqlConnection(connectingString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlHelper.UpdateDataset(SqlClass.InsertCommand(trans, "Test"), SqlClass.DeleteCommand(trans, "Test"),
SqlClass.UpdateCommand(trans, "Test"), ds, "Test");
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
个人感觉使用该类来更新DataTable比较方便，隐约找到了一点使用DataWindow的感觉，但由于时间和精力上的原因，目前该类只适用于SQL Server，暂时不支持其他DBMS
代码中用到了Microsoft的Data Access Application Block，可以到http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp下载
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/langmafeng/archive/2004/07/02/32287.aspx