.Net中DataTable的保存
總是非常懷念以前用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
轉載于:https://www.cnblogs.com/chenbg2001/archive/2010/06/26/1765689.html
總結
以上是生活随笔為你收集整理的.Net中DataTable的保存的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Linux下GCC的安装，GCC链接外部
- 下一篇： mysql 视图操作和存储过程