将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 * ***********************************************************************/
5using 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 * ***********************************************************************/
5using 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
下面是使用示例代码:
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);
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);