将DataSet更新自动提交到数据库工具类实现
大家好！
在项目中遇到一些要把DataSet的更改提交到数据库的需求，因此，实现了一个工具类来完成这个工作。
设计思想：DataSet中的每一个DataTable中的每一行都有状态(RowState), 并且DataTable提供了一个GetChanges(DataRowState state)的方法来获取变化了的数据集合，返回一个新的DataTable对象。因此，我们根据数据提交的顺序，将分别处理 Insert, Update, Delete三种情况，并且根据与数据库表的映射，自动构造SQL语句，利用SqlDataAdapter完成数据提交工作。
由于.net基础数据类型与SQL Server数据类型存在多对一的关系，且使用数据适配器(DataAdapter)时，构造SQL参数对象必须要求指定每个参数对象的长度以及更多信息（映射数据有版本的情况下），因此，不好根据DataColumn的类型来推导对应的SqlDbType，故根据需要实现了一个新的类，来保存每个列的映射及参数。
下面是实现的原代码，有许多需要改进的地方，希望大家提出更好的意见和建议。在此谢过，先！
1
/* ***********************************************************************
2
* Created by : Steven He [2006/02/16]
3 * Descritpion: Sql Server 数据访问工具类。
4
* ***********************************************************************/
5
using System;
6using System.Data;
7using System.Data.Common;
8using System.Data.SqlClient;
9using System.Text;
10
11namespace Newegg.Data
12{
13
///
14 /// Summary description for SqlDbAccess.
15
///
16 public class SqlDbAccess
17 {
18 static SqlDbAccess()
19 {
20 }
21
22 ///
23 /// 处理传入的数据库表名，加上[]，避免因表名是数据库中的关键字而出错。
24 /// 并且在不存在所有者名的时候，加上所有者名。
25 ///
26 /// 待处理的数据库表名。
27 /// 处理后的数据库表名。
28 private static string PrepareDbTableName(string dbTableName)
29 {
30 string[] tableName = dbTableName.Split('.');
31 if(tableName.Length == 1) //只有表名
32 {
33 if(tableName[0][0] == '[')
34 return tableName[0]; // [tableName] 格式
35 else
36 return string.Concat("dbo.[", dbTableName, "]"); //tableName 格式
37 }
38 else
39 {
40 StringBuilder text = new StringBuilder(100);
41 for(int i=0; i<tableName.Length; i++)
42 {
43 if(tableName[ i][0] == '[') //[xx] 格式
44 text.Append(tableName[ i][0] + '.');
45 else // xx 格式
46 text.Append("[" + tableName[ i] + "].");
47 }
48 text.Remove(text.Length-1, 1);
49
50 return text.ToString();
51 }
52 }
53
54 ///
55 /// 更新DataTable的更改到数据库中。
56 /// 并发控制采用“最后的更新生效”
57 ///
58 /// 有效的要提交的DataTable对象
59 /// 有效的数据为连接对象
60 /// 有效的数据库表名
61 /// 有效的要提交的列及参数映射对象数组
62 /// 有效的条件列及参数映射对象数组
63 /// 是否需要事务
64 public static void UpdateData(DataTable table, SqlConnection sqlConn, string dbTableName,
65 ParameterColumnMapping[] dataColumnMappings, ParameterColumnMapping[] keyColumnMappings, bool needTransaction)
66 {
67 if(table == null) throw new ArgumentNullException("table");
68 if(sqlConn == null) throw new ArgumentNullException("sqlConn");
69 if(dbTableName == null || dbTableName.Length == 0) throw new ArgumentNullException("dbTableName");
70 if(dataColumnMappings == null || dataColumnMappings.Length == 0) throw new ArgumentNullException("dataColumnMappings");
71 if(keyColumnMappings == null || keyColumnMappings.Length == 0) throw new ArgumentNullException("keyColumnMappings");
72 if(table.Rows.Count == 0) return;
73
74 dbTableName = PrepareDbTableName(dbTableName);
75
76 // 设置连接是否需要在完成后关闭的标记。
77 // 如果连接对象传进来时是关闭的，则用完后也应该关闭。即保持连接传入前的状态。
78 bool connNeedClose = (sqlConn.State == ConnectionState.Closed);
79
80 SqlDataAdapter sqlAdapter = new SqlDataAdapter();
81
82 // 添加表映射及列映射
83 DataTableMapping tableMapping = sqlAdapter.TableMappings.Add(dbTableName, table.TableName);
84 for(int i=0; i<dataColumnMappings.Length; i++)
85 {
86 tableMapping.ColumnMappings.Add(dataColumnMappings[ i].SourceColumn, dataColumnMappings[ i].DataTableColumn);
87 }
88
89 // 构造WHERE条件部分
90 StringBuilder sqlWhere = new StringBuilder(256);
91 sqlWhere = new StringBuilder(256);
92 sqlWhere.Append(" WHERE ");
93 for(int i=0; i<keyColumnMappings.Length; i++)
94 {
95 if(i == 0)
96 {
97 sqlWhere.Append(string.Format("[{0}] = @{0}", keyColumnMappings[ i].SourceColumn));
98 }
99 else
100 {
101 sqlWhere.Append(string.Format(" AND [{0}] = @{0}", keyColumnMappings[ i].SourceColumn));
102 }
103 }
104
105 StringBuilder sqlText = new StringBuilder(1024);
106 SqlTransaction sqlTran = null;
107 DataTable tempTable = null;
108 DataRow[] rows = new DataRow[1];
109
110 // 开始提交数据
111 try
112 {
113 if(connNeedClose) sqlConn.Open();
114
115 if(needTransaction) sqlTran = sqlConn.BeginTransaction();
116 SqlCommand sqlCmd = new SqlCommand("", sqlConn, sqlTran);
117
118 // 处理Insert
119 tempTable = table.GetChanges(DataRowState.Added);
120 if(tempTable != null)
121 {
122 sqlAdapter.InsertCommand = sqlCmd;
123 StringBuilder valueText = new StringBuilder(256);
124 // 循环每一行，只处理不为Null的列
125 foreach(DataRow dr in tempTable.Rows)
126 {
127 sqlCmd.Parameters.Clear();
128 sqlText.Length = 0;
129 sqlText.Append("INSERT INTO " + dbTableName + " (");
130 for(int i=0; i<dataColumnMappings.Length; i++)
131 {
132 if(dr[dataColumnMappings[ i].DataTableColumn] != DBNull.Value)
133 {
134 sqlText.Append(string.Format(" [{0}],", dataColumnMappings[ i].SourceColumn));
135 valueText.Append(string.Format(" @{0},", dataColumnMappings[ i].SourceColumn));
136 sqlCmd.Parameters.Add(dataColumnMappings[ i].DbParameter);
137 }
138 }
139
140 // 移除末尾的','
141 sqlText.Remove(sqlText.Length-1, 1);
142 valueText.Remove(valueText.Length-1, 1);
143
144 // 完成Insert语句的构造并提交
145 sqlText.Append(string.Format(" ) VALUES ({0})", valueText.ToString()));
146 sqlCmd.CommandText = sqlText.ToString();
147 rows[0] = dr;
148 sqlAdapter.Update(rows);
149 }
150 }
151
152 // 处理Update
153 // 仅对已经发生了改变的列进行Update
154 tempTable = table.GetChanges(DataRowState.Modified);
155 if(tempTable != null)
156 {
157 int changedColumns = 0; //用于记录已经改变的列数
158 sqlAdapter.UpdateCommand = sqlCmd;
159 foreach(DataRow dr in tempTable.Rows)
160 {
161 sqlCmd.Parameters.Clear();
162 sqlText.Length = 0;
163 sqlText.Append(string.Format("UPDATE {0} SET", dbTableName));
164 changedColumns = 0;
165 for(int i=0; i<dataColumnMappings.Length; i++)
166 {
167 // 当前版本与原始版本不等
168 if(!dr[dataColumnMappings[ i].DataTableColumn, DataRowVersion.Current].Equals(
169 dr[dataColumnMappings[ i].DataTableColumn, DataRowVersion.Original]))
170 {
171 sqlText.Append(string.Format(" [{0}]=@{0},", dataColumnMappings[ i].SourceColumn));
172 sqlCmd.Parameters.Add(dataColumnMappings[ i].DbParameter);
173 changedColumns ++;
174 }
175
176 // 仅当有列改变了才进行处理，以防止行状态改变了，但值未变的情况。
177 if(changedColumns > 0)
178 {
179 sqlText.Remove(sqlText.Length-1, 1); //移除末尾的‘,'
180 sqlText.Append(" " + sqlWhere.ToString());
181
182 // 添加条件参数
183 for(int j=0; j<keyColumnMappings.Length; j++)
184 {
185 sqlCmd.Parameters.Add(keyColumnMappings[j].DbParameter);
186 }
187
188 // 提交数据
189 sqlCmd.CommandText = sqlText.ToString();
190 rows[0] = dr;
191 sqlAdapter.Update(rows);
192 }
193 }
194 }
195 }
196
197 // 处理Delete
198 tempTable = table.GetChanges(DataRowState.Deleted);
199 if(tempTable != null && tempTable.Rows.Count > 0)
200 {
201 sqlText.Length = 0;
202 sqlText.Append("DELETE FROM " + dbTableName + sqlWhere.ToString());
203
204 sqlCmd.CommandText = sqlText.ToString();
205 sqlCmd.Parameters.Clear();
206 // 添加条件参数
207 for(int j=0; j<keyColumnMappings.Length; j++)
208 {
209 sqlCmd.Parameters.Add(keyColumnMappings[j].DbParameter);
210 }
211
212 sqlAdapter.DeleteCommand = sqlCmd;
213 sqlAdapter.Update(tempTable);
214 }
215
216 if(sqlTran != null) sqlTran.Commit();
217 }
218 catch(Exception ex)
219 {
220 if(sqlTran != null)
221 {
222 sqlTran.Rollback();
223 }
224 throw ex;
225 }
226 finally
227 {
228 if(connNeedClose) sqlConn.Close();
229 }
230 }
231
232 ///
233 /// 定义参数与列的映射关系的类。
234 ///
235 public class ParameterColumnMapping
236 {
237 private string m_SourceColumn;
238 private string m_DataTableColumn;
239 private SqlParameter m_DbParameter;
240
241 ///
242 /// 构造函数。
243 ///
244 /// 数据库源列名
245 /// DataTable中的列名
246 /// 对应的参数对象
247 public ParameterColumnMapping(string sourceColumn, string dataTableColumn, SqlParameter dbParameter)
248 {
249 m_SourceColumn = sourceColumn;
250 m_DataTableColumn = dataTableColumn;
251 m_DbParameter = dbParameter;
252 }
253
254 public ParameterColumnMapping() : this(null, null, null)
255 {
256 }
257
258 ///
259 /// 获取或设置数据库源列名。
260 ///
261 public string SourceColumn {
262 get {
263 return m_SourceColumn;
264 }
265 set {
266 m_SourceColumn = value;
267 }
268 }
269
270 ///
271 /// 获取或设置DataTable对象中的列名。
272 ///
273 public string DataTableColumn {
274 get {
275 return m_DataTableColumn;
276 }
277 set {
278 m_DataTableColumn = value;
279 }
280 }
281
282 ///
283 /// 获取或设置列对应的Sql参数对象。
284 ///
285 public SqlParameter DbParameter {
286 get {
287 return m_DbParameter;
288 }
289 set {
290 m_DbParameter = value;
291 }
292 }
293 }
294 }
295}
296
/* ***********************************************************************
2
* Created by : Steven He [2006/02/16]3
* Descritpion: Sql Server 数据访问工具类。4
* ***********************************************************************/5
using System;6
using System.Data;7
using System.Data.Common;8
using System.Data.SqlClient;9
using System.Text;10
11
namespace Newegg.Data12
{
13
/// 
14
/// Summary description for SqlDbAccess.15
/// 16
public class SqlDbAccess17
{18
static SqlDbAccess()19
{20
}21
22
/// 23
/// 处理传入的数据库表名，加上[]，避免因表名是数据库中的关键字而出错。24
/// 并且在不存在所有者名的时候，加上所有者名。25
/// 26
/// 待处理的数据库表名。27
/// 处理后的数据库表名。28
private static string PrepareDbTableName(string dbTableName)29
{30
string[] tableName = dbTableName.Split('.');31
if(tableName.Length == 1) //只有表名32
{33
if(tableName[0][0] == '[') 34
return tableName[0]; // [tableName] 格式35
else 36
return string.Concat("dbo.[", dbTableName, "]"); //tableName 格式37
}38
else39
{40
StringBuilder text = new StringBuilder(100);41
for(int i=0; i<tableName.Length; i++)42
{43
if(tableName[ i][0] == '[') //[xx] 格式44
text.Append(tableName[ i][0] + '.');45
else // xx 格式46
text.Append("[" + tableName[ i] + "].");47
}48
text.Remove(text.Length-1, 1);49
50
return text.ToString();51
}52
}53
54
/// 55
/// 更新DataTable的更改到数据库中。56
/// 并发控制采用“最后的更新生效”57
/// 58
/// 有效的要提交的DataTable对象59
/// 有效的数据为连接对象60
/// 有效的数据库表名61
/// 有效的要提交的列及参数映射对象数组62
/// 有效的条件列及参数映射对象数组63
/// 是否需要事务64
public static void UpdateData(DataTable table, SqlConnection sqlConn, string dbTableName, 65
ParameterColumnMapping[] dataColumnMappings, ParameterColumnMapping[] keyColumnMappings, bool needTransaction)66
{67
if(table == null) throw new ArgumentNullException("table");68
if(sqlConn == null) throw new ArgumentNullException("sqlConn");69
if(dbTableName == null || dbTableName.Length == 0) throw new ArgumentNullException("dbTableName");70
if(dataColumnMappings == null || dataColumnMappings.Length == 0) throw new ArgumentNullException("dataColumnMappings");71
if(keyColumnMappings == null || keyColumnMappings.Length == 0) throw new ArgumentNullException("keyColumnMappings");72
if(table.Rows.Count == 0) return;73
74
dbTableName = PrepareDbTableName(dbTableName);75
76
// 设置连接是否需要在完成后关闭的标记。77
// 如果连接对象传进来时是关闭的，则用完后也应该关闭。即保持连接传入前的状态。78
bool connNeedClose = (sqlConn.State == ConnectionState.Closed);79
80
SqlDataAdapter sqlAdapter = new SqlDataAdapter();81
82
// 添加表映射及列映射83
DataTableMapping tableMapping = sqlAdapter.TableMappings.Add(dbTableName, table.TableName);84
for(int i=0; i<dataColumnMappings.Length; i++)85
{86
tableMapping.ColumnMappings.Add(dataColumnMappings[ i].SourceColumn, dataColumnMappings[ i].DataTableColumn);87
}88
89
// 构造WHERE条件部分90
StringBuilder sqlWhere = new StringBuilder(256);91
sqlWhere = new StringBuilder(256);92
sqlWhere.Append(" WHERE ");93
for(int i=0; i<keyColumnMappings.Length; i++)94
{95
if(i == 0)96
{97
sqlWhere.Append(string.Format("[{0}] = @{0}", keyColumnMappings[ i].SourceColumn));98
}99
else100
{101
sqlWhere.Append(string.Format(" AND [{0}] = @{0}", keyColumnMappings[ i].SourceColumn));102
}103
}104
105
StringBuilder sqlText = new StringBuilder(1024);106
SqlTransaction sqlTran = null;107
DataTable tempTable = null;108
DataRow[] rows = new DataRow[1];109
110
// 开始提交数据111
try112
{113
if(connNeedClose) sqlConn.Open();114
115
if(needTransaction) sqlTran = sqlConn.BeginTransaction();116
SqlCommand sqlCmd = new SqlCommand("", sqlConn, sqlTran);117
118
// 处理Insert119
tempTable = table.GetChanges(DataRowState.Added);120
if(tempTable != null)121
{122
sqlAdapter.InsertCommand = sqlCmd;123
StringBuilder valueText = new StringBuilder(256);124
// 循环每一行，只处理不为Null的列125
foreach(DataRow dr in tempTable.Rows)126
{127
sqlCmd.Parameters.Clear();128
sqlText.Length = 0;129
sqlText.Append("INSERT INTO " + dbTableName + " (");130
for(int i=0; i<dataColumnMappings.Length; i++)131
{132
if(dr[dataColumnMappings[ i].DataTableColumn] != DBNull.Value)133
{134
sqlText.Append(string.Format(" [{0}],", dataColumnMappings[ i].SourceColumn));135
valueText.Append(string.Format(" @{0},", dataColumnMappings[ i].SourceColumn));136
sqlCmd.Parameters.Add(dataColumnMappings[ i].DbParameter);137
}138
}139
140
// 移除末尾的','141
sqlText.Remove(sqlText.Length-1, 1);142
valueText.Remove(valueText.Length-1, 1);143
144
// 完成Insert语句的构造并提交145
sqlText.Append(string.Format(" ) VALUES ({0})", valueText.ToString()));146
sqlCmd.CommandText = sqlText.ToString();147
rows[0] = dr;148
sqlAdapter.Update(rows);149
}150
}151
152
// 处理Update 153
// 仅对已经发生了改变的列进行Update154
tempTable = table.GetChanges(DataRowState.Modified);155
if(tempTable != null)156
{157
int changedColumns = 0; //用于记录已经改变的列数158
sqlAdapter.UpdateCommand = sqlCmd;159
foreach(DataRow dr in tempTable.Rows)160
{161
sqlCmd.Parameters.Clear();162
sqlText.Length = 0;163
sqlText.Append(string.Format("UPDATE {0} SET", dbTableName));164
changedColumns = 0;165
for(int i=0; i<dataColumnMappings.Length; i++)166
{167
// 当前版本与原始版本不等168
if(!dr[dataColumnMappings[ i].DataTableColumn, DataRowVersion.Current].Equals(169
dr[dataColumnMappings[ i].DataTableColumn, DataRowVersion.Original]))170
{171
sqlText.Append(string.Format(" [{0}]=@{0},", dataColumnMappings[ i].SourceColumn));172
sqlCmd.Parameters.Add(dataColumnMappings[ i].DbParameter);173
changedColumns ++;174
}175
176
// 仅当有列改变了才进行处理，以防止行状态改变了，但值未变的情况。177
if(changedColumns > 0)178
{179
sqlText.Remove(sqlText.Length-1, 1); //移除末尾的‘,'180
sqlText.Append(" " + sqlWhere.ToString());181
182
// 添加条件参数183
for(int j=0; j<keyColumnMappings.Length; j++)184
{185
sqlCmd.Parameters.Add(keyColumnMappings[j].DbParameter);186
}187
188
// 提交数据189
sqlCmd.CommandText = sqlText.ToString();190
rows[0] = dr;191
sqlAdapter.Update(rows);192
}193
}194
}195
}196
197
// 处理Delete198
tempTable = table.GetChanges(DataRowState.Deleted);199
if(tempTable != null && tempTable.Rows.Count > 0)200
{201
sqlText.Length = 0;202
sqlText.Append("DELETE FROM " + dbTableName + sqlWhere.ToString());203
204
sqlCmd.CommandText = sqlText.ToString();205
sqlCmd.Parameters.Clear();206
// 添加条件参数207
for(int j=0; j<keyColumnMappings.Length; j++)208
{209
sqlCmd.Parameters.Add(keyColumnMappings[j].DbParameter);210
}211
212
sqlAdapter.DeleteCommand = sqlCmd;213
sqlAdapter.Update(tempTable);214
}215
216
if(sqlTran != null) sqlTran.Commit();217
}218
catch(Exception ex)219
{220
if(sqlTran != null)221
{222
sqlTran.Rollback();223
}224
throw ex;225
}226
finally227
{228
if(connNeedClose) sqlConn.Close();229
}230
}231
232
/// 233
/// 定义参数与列的映射关系的类。234
/// 235
public class ParameterColumnMapping236
{237
private string m_SourceColumn;238
private string m_DataTableColumn;239
private SqlParameter m_DbParameter;240
241
/// 242
/// 构造函数。243
/// 244
/// 数据库源列名245
/// DataTable中的列名246
/// 对应的参数对象247
public ParameterColumnMapping(string sourceColumn, string dataTableColumn, SqlParameter dbParameter)248
{249
m_SourceColumn = sourceColumn;250
m_DataTableColumn = dataTableColumn;251
m_DbParameter = dbParameter;252
}253
254
public ParameterColumnMapping() : this(null, null, null)255
{256
}257
258
/// 259
/// 获取或设置数据库源列名。260
/// 261
public string SourceColumn {262
get {263
return m_SourceColumn;264
}265
set {266
m_SourceColumn = value;267
}268
}269
270
/// 271
/// 获取或设置DataTable对象中的列名。272
/// 273
public string DataTableColumn {274
get {275
return m_DataTableColumn;276
}277
set {278
m_DataTableColumn = value;279
}280
}281
282
/// 283
/// 获取或设置列对应的Sql参数对象。284
/// 285
public SqlParameter DbParameter {286
get {287
return m_DbParameter;288
}289
set {290
m_DbParameter = value;291
}292
}293
}294
}295
}296
下面是使用示例代码：
1SqlDbAccess.ParameterColumnMapping[] dataColumnMappings = new Newegg.Data.SqlDbAccess.ParameterColumnMapping[2];
2 dataColumnMappings[0] = new Newegg.Data.SqlDbAccess.ParameterColumnMapping("APP_ID", myData.AppSystems.AppIDColumn.ColumnName,
3 new SqlParameter("@APP_ID", SqlDbType.NVarChar, 20, myData.AppSystems.AppIDColumn.ColumnName));
4 dataColumnMappings[1] = new Newegg.Data.SqlDbAccess.ParameterColumnMapping("DESCRIPTION", myData.AppSystems.DescriptionColumn.ColumnName,
5 new SqlParameter("@DESCRIPTION", SqlDbType.NVarChar, 500, myData.AppSystems.DescriptionColumn.ColumnName));
6
7 SqlDbAccess.ParameterColumnMapping[] keyColumnMappings = new Newegg.Data.SqlDbAccess.ParameterColumnMapping[1];
8 keyColumnMappings[0] = new Newegg.Data.SqlDbAccess.ParameterColumnMapping("APP_ID", myData.AppSystems.AppIDColumn.ColumnName,
9 new SqlParameter("@APP_ID", SqlDbType.NVarChar, 20, ParameterDirection.Input, true, 0, 0, myData.AppSystems.AppIDColumn.ColumnName, DataRowVersion.Original, null));
10
11 SqlConnection sqlConn = new SqlConnection(Configuration.D2WHP01_ConnString);
12 SqlDbAccess.UpdateData(myData.AppSystems, sqlConn, "AzProvider.dbo.APP", dataColumnMappings, keyColumnMappings, true);
SqlDbAccess.ParameterColumnMapping[] dataColumnMappings = new Newegg.Data.SqlDbAccess.ParameterColumnMapping[2];2
dataColumnMappings[0] = new Newegg.Data.SqlDbAccess.ParameterColumnMapping("APP_ID", myData.AppSystems.AppIDColumn.ColumnName,3
new SqlParameter("@APP_ID", SqlDbType.NVarChar, 20, myData.AppSystems.AppIDColumn.ColumnName));4
dataColumnMappings[1] = new Newegg.Data.SqlDbAccess.ParameterColumnMapping("DESCRIPTION", myData.AppSystems.DescriptionColumn.ColumnName,5
new SqlParameter("@DESCRIPTION", SqlDbType.NVarChar, 500, myData.AppSystems.DescriptionColumn.ColumnName));6
7
SqlDbAccess.ParameterColumnMapping[] keyColumnMappings = new Newegg.Data.SqlDbAccess.ParameterColumnMapping[1];8
keyColumnMappings[0] = new Newegg.Data.SqlDbAccess.ParameterColumnMapping("APP_ID", myData.AppSystems.AppIDColumn.ColumnName,9
new SqlParameter("@APP_ID", SqlDbType.NVarChar, 20, ParameterDirection.Input, true, 0, 0, myData.AppSystems.AppIDColumn.ColumnName, DataRowVersion.Original, null));10
11
SqlConnection sqlConn = new SqlConnection(Configuration.D2WHP01_ConnString);12
SqlDbAccess.UpdateData(myData.AppSystems, sqlConn, "AzProvider.dbo.APP", dataColumnMappings, keyColumnMappings, true);
