将DataSet更新自动提交到数据库工具类实现

大家好！

在项目中遇到一些要把DataSet的更改提交到数据库的需求，因此，实现了一个工具类来完成这个工作。

设计思想：DataSet中的每一个DataTable中的每一行都有状态(RowState), 并且DataTable提供了一个GetChanges(DataRowState state)的方法来获取变化了的数据集合，返回一个新的DataTable对象。因此，我们根据数据提交的顺序，将分别处理 Insert, Update, Delete三种情况，并且根据与数据库表的映射，自动构造SQL语句，利用SqlDataAdapter完成数据提交工作。

由于.net基础数据类型与SQL Server数据类型存在多对一的关系，且使用数据适配器(DataAdapter)时，构造SQL参数对象必须要求指定每个参数对象的长度以及更多信息（映射数据有版本的情况下），因此，不好根据DataColumn的类型来推导对应的SqlDbType，故根据需要实现了一个新的类，来保存每个列的映射及参数。

下面是实现的原代码，有许多需要改进的地方，希望大家提出更好的意见和建议。在此谢过，先！

/* ***********************************************************************
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

namespace Newegg.Data
12

{
13

///
14

/// Summary description for SqlDbAccess.
15

///
16

public class SqlDbAccess
17

{
18

static SqlDbAccess()
19

{
20

}
21

///
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

return text.ToString();
51

}
52

}
53

///
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

dbTableName = PrepareDbTableName(dbTableName);
75

// 设置连接是否需要在完成后关闭的标记。
77

// 如果连接对象传进来时是关闭的，则用完后也应该关闭。即保持连接传入前的状态。
78

bool connNeedClose = (sqlConn.State == ConnectionState.Closed);
79

SqlDataAdapter sqlAdapter = new SqlDataAdapter();
81

// 添加表映射及列映射
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

// 构造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

下面是使用示例代码：

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

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

SqlConnection sqlConn = new SqlConnection(Configuration.D2WHP01_ConnString);
12

SqlDbAccess.UpdateData(myData.AppSystems, sqlConn, "AzProvider.dbo.APP", dataColumnMappings, keyColumnMappings, true);

posted on 2006-02-17 18:22 流浪狗阅读(1717) 评论(0) 收藏举报