/* 2008 4 25 更新 */
我的数据访问函数库的源码。整个类有1400行，原先就是分开来写的，现在更新后还是分开来发一下吧。
第二部分：SQL语句部分。
传入SQL语句，执行相关的操作。
我的数据访问函数库的源码。整个类有1400行，原先就是分开来写的，现在更新后还是分开来发一下吧。
第二部分：SQL语句部分。
传入SQL语句，执行相关的操作。
1
//
查询语句部分
2
运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）
#region 运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）
3
4
/**//// <summary>
5 /// 运行SQL查询语句 返回DataSet。可以传入多条查询语句，返回的DataSet里会有多个DataTable
6 /// </summary>
7 /// <param name="SQL">查询语句。比如select * from tableName</param>
8
/// <returns>返回DataSet</returns>
9 函数实现 — — RunSqlDataSet#region 函数实现 — — RunSqlDataSet
10 public DataSet RunSqlDataSet(string SQL)
11 
{
12 SetCommand(SQL,1); //设置command
13 SqlDataAdapter da = new SqlDataAdapter(cm);
14 try
15 {
16 DataSet DS = new DataSet();
17 da.Fill(DS);
18 return DS;
19 }
20 catch(Exception ex)
21 {
22 SetErrorMsg("RunSqlDataSet",SQL,ex.Message ); //处理错误
23 return null;
24 }
25 finally
26 {
27 //自动关闭了，不用手动关闭。
28 da.Dispose();
29 }
30 }
31 #endregion
32
33
34 /**//// <summary>
35 /// 运行SQL查询语句 返回DataTable。
36 /// </summary>
37 /// <param name="SQL">查询语句。比如select * from tableName</param>
38 /// <returns>返回DataTable</returns>
39 函数实现 — — RunSqlDataTable#region 函数实现 — — RunSqlDataTable
40 public DataTable RunSqlDataTable(string SQL)
41 {
42 SetCommand(SQL,1); //设置command
43 SqlDataAdapter da = new SqlDataAdapter(cm);
44 try
45 {
46 DataTable DT = new DataTable();
47 da.Fill(DT);
48 return DT;
49 }
50 catch(Exception ex)
51 {
52 SetErrorMsg("RunSqlDataTable",SQL,ex.Message ); //处理错误
53 return null;
54 }
55 finally
56 {
57 //自动关闭了，不用手动关闭。
58 da.Dispose();
59 }
60 }
61 #endregion
62
63
64 /**//// <summary>
65 /// 运行SQl语句返回第一条记录。返回DataRow
66 /// </summary>
67 /// <param name="SQL">查询语句。比如select * from tableName</param>
68 /// <returns></returns>
69 函数实现 — — RunSqlDataRow#region 函数实现 — — RunSqlDataRow
70 public DataRow RunSqlDataRow(string SQL)
71 {
72 SetCommand(SQL,1); //设置command
73 SqlDataAdapter da = new SqlDataAdapter(cm);
74 try
75 {
76 DataTable DT = new DataTable();
77 da.Fill(DT);
78 if (DT.Rows.Count > 0)
79 return DT.Rows[0];
80 else
81 return null;
82 }
83 catch(Exception ex)
84 {
85 SetErrorMsg("RunSqlDataRow",SQL,ex.Message ); //处理错误
86 return null;
87 }
88 finally
89 {
90 //自动关闭了，不用手动关闭。
91 da.Dispose();
92 }
93
94 }
95 #endregion
96
97
98 /**//// <summary>
99 /// 运行SQl语句返回第一条记录的数组。返回字符串数组
100 /// </summary>
101 /// <param name="SQL">查询语句。比如select top 1 * from tableName</param>
102 /// <returns></returns>
103 函数实现 — — RunSqlStrings#region 函数实现 — — RunSqlStrings
104 public string[] RunSqlStrings(string SQL)
105 {
106 //返回ID 传入查询语句，返回第一条记录的第一的字段的值
107 SetCommand(SQL,1); //设置command
108 SqlDataReader r = null;
109 try
110 {
111 if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
112 cm.Connection.Open();
113
114 r = cm.ExecuteReader();
115 string[] strValue = null;
116 if (r.Read() )
117 {
118 int ArrLength = r.FieldCount;
119
120 strValue = new string[ArrLength];
121 for(int i=0;i<ArrLength;i++)
122 strValue[i] = r.GetValue(i).ToString();
123
124 }
125
126 return strValue;
127 }
128 catch(Exception ex)
129 {
130 SetErrorMsg("RunSqlStrs",SQL,ex.Message ); //处理错误
131 return null;
132 }
133 finally
134 {
135 if (r != null)
136 r.Close();
137
138 if (!isUseTrans)
139 cm.Connection.Close();
140
141 }
142 }
143 #endregion
144
145 /**//// <summary>
146 /// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
147 /// </summary>
148 /// <param name="SQL">查询语句。比如select myName from tableName</param>
149 /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
150 /// <returns></returns>
151 函数实现 — — RunSqlStringsByRow#region 函数实现 — — RunSqlStringsByRow
152 public string[] RunSqlStringsByRow(string SQL,string SQLForCount)
153 {
154 //先获取记录数
155 int RowCount = 0;
156 if (Functions.IsInt(SQLForCount))
157 {
158 RowCount = Int32.Parse(SQLForCount);
159 }
160 else
161 {
162 string strRowCount = RunSqlGetID(SQLForCount);
163 if (strRowCount == null)
164 return null;
165
166 RowCount = Int32.Parse(strRowCount);
167 }
168 if (RowCount <1)
169 return null;
170
171 //传入查询语句，返回每条记录的第一的字段的值
172 SetCommand(SQL,1); //设置command
173 SqlDataReader r = null;
174 try
175 {
176 if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
177 cm.Connection.Open();
178
179 string[] strValue = new string[RowCount];
180 r = cm.ExecuteReader();
181 int i = 0;
182 while (r.Read())
183 {
184 strValue[i] = r[0].ToString();
185 i++;
186 }
187 return strValue;
188
189
190 }
191 catch(Exception ex)
192 {
193 SetErrorMsg("RunSqlStringsByRow",SQL,ex.Message ); //处理错误
194 return null;
195 }
196 finally
197 {
198 if (r != null)
199 r.Close();
200
201 if (!isUseTrans)
202 cm.Connection.Close();
203
204 }
205 }
206 #endregion
207
208 /**//// <summary>
209 /// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
210 /// </summary>
211 /// <param name="SQL">查询语句。比如select myName from tableName</param>
212 /// <returns></returns>
213 函数实现 — — RunSqlStringsByRow#region 函数实现 — — RunSqlStringsByRow
214 public string[] RunSqlStringsByRow(string SQL)
215 {
216
217 //传入查询语句，返回每条记录的第一的字段的值
218 SetCommand(SQL,1); //设置command
219 SqlDataReader r = null;
220 try
221 {
222 if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
223 cm.Connection.Open();
224
225 r = cm.ExecuteReader();
226 //int i = 0;
227 System.Collections.IList li = new System.Collections.ArrayList();
228 while (r.Read())
229 li.Add(r[0].ToString());
230
231 string[] strValue = new string[li.Count];
232
233 li.CopyTo(strValue,0);
234
235 return strValue;
236
237 }
238 catch(Exception ex)
239 {
240 SetErrorMsg("RunSqlStringsByRow",SQL,ex.Message ); //处理错误
241 return null;
242 }
243 finally
244 {
245 if (r != null)
246 r.Close();
247 if (!isUseTrans)
248 cm.Connection.Close();
249
250 }
251 }
252 #endregion
253
254 /**//// <summary>
255 /// 运行SQl语句返回第一条记录的第一列的值。
256 /// </summary>
257 /// <param name="SQL">查询语句。比如select top 1 ID from tableName where userName='aa'。会返回ID的内容</param>
258 /// <returns></returns>
259 函数实现 — — RunSqlGetID#region 函数实现 — — RunSqlGetID
260 public string RunSqlGetID(string SQL)
261 {
262 SetCommand(SQL,1); //设置command
263 SqlDataReader r = null;
264 try
265 {
266 if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
267 cm.Connection.Open();
268
269 r = cm.ExecuteReader(CommandBehavior.SingleRow);
270 string re = "";
271 if (r.Read())
272 re = r.GetValue(0).ToString();
273 else
274 re = null;
275
276 return re;
277
278 }
279 catch(Exception ex)
280 {
281 SetErrorMsg("RunSqlGetID",SQL,ex.Message ); //处理错误
282 return null;
283 }
284 finally
285 {
286 if (r != null)
287 r.Close();
288
289 if (!isUseTrans)
290 cm.Connection.Close();
291
292 }
293 }
294 #endregion
295
296
#endregion
297
298
运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）
#region 运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）
299 /**//// <summary>
300 /// 运行SQL查询语句，不返回记录集。用于添加、修改、删除等操作
301 /// </summary>
302 /// <param name="SQL">查询语句。比如insert into tableName 、update tableName</param>
303 /// <returns></returns>
304 函数实现 — — RunSql#region 函数实现 — — RunSql
305 public void RunSql(string SQL)
306 {
307 SetCommand(SQL,1); //设置command
308 try
309 {
310 if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
311 cm.Connection.Open();
312 executeRowCount = cm.ExecuteNonQuery();
313 }
314 catch(Exception ex)
315 {
316 SetErrorMsg("RunSql",SQL,ex.Message ); //处理错误
317 }
318 finally
319 {
320 if (!isUseTrans)
321 cm.Connection.Close();
322 }
323 }
324 #endregion
325
326
327 /**//// <summary>
328 /// 执行一条SQL语句，看是否能查到记录 有：返回true；没有返回false，用于判断是否重名
329 /// </summary>
330 /// <param name="SQL">查询语句。比如select ID from tableName where userName='aa'</param>
331 /// <returns></returns>
332 函数实现 — — RunSqlExists#region 函数实现 — — RunSqlExists
333 public bool RunSqlExists( string SQL)
334 {
335 SetCommand(SQL,1); //设置command
336 SqlDataReader r = null;
337 try
338 {
339 if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
340 cm.Connection.Open();
341
342 r = cm.ExecuteReader();
343 bool re = true;
344 if (r.HasRows)
345 re = true;
346 else
347 re = false;
348
349 return re;
350 }
351 catch(Exception ex)
352 {
353 errorMsg = "运行RunSqlExists函数时出现错误。<BR>错误信息：" + ex.Message;
354 SetErrorMsg("RunSqlDataSet",SQL,ex.Message ); //处理错误
355 return true;
356 }
357 finally
358 {
359 if (r != null)
360 r.Close();
361
362 if (!isUseTrans)
363 cm.Connection.Close();
364 }
365 }
366 #endregion
367
368 #endregion
369
370
查询语句的方式添加、修改数据
#region 查询语句的方式添加、修改数据
371
372 /**//// <summary>
373 /// 添加记录。传入表名，字段数组，值数组，返回新生成记录的ID
374 /// </summary>
375 /// <param name="TableName">要添加记录的表的名称</param>
376 /// <param name="ziduan">字段名数组</param>
377 /// <param name="msg">字段对应的值的数组</param>
378 /// <returns></returns>
379 public string InsertDataStr(string TableName , string[] ziduan , string[] msg )
380 {
381 //添加数据 返回新添加的ID
382 System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);
383 SQL.Append("insert into "); //insert into
384 SQL.Append(TableName);
385 SQL.Append(" ([");
386 int i;
387 for( i = 0 ;i< ziduan.Length ;i++) //字段
388 {
389 if (msg[i] != "_n_")
390 {
391 SQL.Append(ziduan[i]);
392 SQL.Append("],[");
393 }
394 }
395 SQL = SQL.Remove(SQL.Length -2,2);
396
397 SQL.Append(") values ('");
398
399 for( i = 0;i<ziduan.Length ;i++)
400 {
401 if (msg[i] != "_n_")
402 {
403 SQL.Append(msg[i]);
404 SQL.Append("','");
405 }
406 }
407 SQL = SQL.Remove(SQL.Length -2,2);
408
409 SQL.Append(") select scope_identity() as a1");
410
411 string re = RunSqlGetID(SQL.ToString());
412 SQL.Length = 1;
413 if (re == null)
414 return "-1";
415 else
416 return re;
417 }
418
419 /**//// <summary>
420 /// 修改记录。传入表名，字段数组，值数组 ，修改条件
421 /// </summary>
422 /// <param name="TableName">要修改记录的表的名称</param>
423 /// <param name="ziduan">字段名数组</param>
424 /// <param name="msg">字段对应的值的数组</param>
425 /// <param name="tiaojian">条件 ，加在where 后面的语句</param>
426 /// <returns></returns>
427 public bool UpdateData( string TableName ,string[] ziduan ,string[] msg ,string tiaojian)
428 {
429 System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);
430 SQL.Append("update "); //update
431 SQL.Append(TableName);
432 SQL.Append(" set ");
433 int i;
434 for (i = 0 ;i< ziduan.Length ;i++)
435 {
436 if (msg[i] != "_n_")
437 {
438 SQL.Append("[");
439 SQL.Append(ziduan[i]); //update
440 SQL.Append("]='");
441 SQL.Append(msg[i]);
442 SQL.Append("',");
443 }
444 }
445 SQL = SQL.Remove(SQL.Length-1,1); //去掉最后一个 ","
446 SQL.Append(" where ");
447 SQL.Append(tiaojian);
448
449 RunSql(SQL.ToString());
450 return true;
451
452 }
453
454 "20071220增加的通过 FormTableCntrlInfo 保存数据的方法"#region "20071220增加的通过 FormTableCntrlInfo 保存数据的方法"
455 /**//// <summary>
456 /// 添加记录。传入表名，FormTableCntrlInfo，返回新生成记录的ID
457 /// </summary>
458 /// <param name="TableName">要添加记录的表的名称</param>
459 /// <param name="cInfo">FormTableCntrlInfo</param>
460 /// <returns></returns>
461 public string InsertDataStr(string TableName , FormTableCntrlInfo[] cInfo )
462 {
463 //添加数据 返回新添加的ID
464 System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);
465 SQL.Append("insert into "); //insert into
466 SQL.Append(TableName);
467 SQL.Append(" ([");
468 int i;
469 for( i = 0 ;i < cInfo.Length ;i++) //字段
470 {
471 if (cInfo[i].FieldValue != "_n_")
472 {
473 SQL.Append(cInfo[i].FieldName);
474 SQL.Append("],[");
475 }
476 }
477 SQL = SQL.Remove(SQL.Length -2,2);
478
479 SQL.Append(") values ('");
480
481 for( i = 0;i < cInfo.Length ;i++)
482 {
483 if (cInfo[i].FieldValue != "_n_")
484 {
485 SQL.Append(cInfo[i].FieldValue);
486 SQL.Append("','");
487 }
488 }
489 SQL = SQL.Remove(SQL.Length -2,2);
490
491 SQL.Append(") select scope_identity() as a1");
492
493 string re = RunSqlGetID(SQL.ToString());
494 SQL.Length = 1;
495 if (re == null)
496 return "-1";
497 else
498 return re;
499 }
500
501 /**//// <summary>
502 /// 修改记录。传入表名，FormTableCntrlInfo，修改条件
503 /// </summary>
504 /// <param name="TableName">要修改记录的表的名称</param>
505 /// <param name="cInfo">FormTableCntrlInfo</param>
506 /// <param name="tiaojian">条件 ，加在where 后面的语句</param>
507 /// <returns></returns>
508 public bool UpdateData( string TableName ,FormTableCntrlInfo[] cInfo ,string tiaojian)
509 {
510 System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);
511 SQL.Append("update "); //update
512 SQL.Append(TableName);
513 SQL.Append(" set ");
514 int i;
515 for (i = 0 ;i< cInfo.Length ;i++)
516 {
517 if (cInfo[i].FieldValue != "_n_")
518 {
519 SQL.Append("[");
520 SQL.Append(cInfo[i].FieldName); //update
521 SQL.Append("]='");
522 SQL.Append(cInfo[i].FieldValue);
523 SQL.Append("',");
524 }
525 }
526 SQL = SQL.Remove(SQL.Length-1,1); //去掉最后一个 ","
527 SQL.Append(" where ");
528 SQL.Append(tiaojian);
529
530 RunSql(SQL.ToString());
531 return true;
532
533 }
534
535 #endregion
536
537 #endregion
538
//
查询语句部分
2
运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）
#region 运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）3
4
/**//// <summary>5
/// 运行SQL查询语句 返回DataSet。可以传入多条查询语句，返回的DataSet里会有多个DataTable6
/// </summary>7
/// <param name="SQL">查询语句。比如select * from tableName</param>8
/// <returns>返回DataSet</returns>9
函数实现 — — RunSqlDataSet#region 函数实现 — — RunSqlDataSet10
public DataSet RunSqlDataSet(string SQL)11
{ 12
SetCommand(SQL,1); //设置command13
SqlDataAdapter da = new SqlDataAdapter(cm);14
try15
{16
DataSet DS = new DataSet();17
da.Fill(DS);18
return DS;19
}20
catch(Exception ex)21
{22
SetErrorMsg("RunSqlDataSet",SQL,ex.Message ); //处理错误23
return null;24
}25
finally26
{27
//自动关闭了，不用手动关闭。28
da.Dispose();29
}30
}31
#endregion32
33
34
/**//// <summary>35
/// 运行SQL查询语句 返回DataTable。36
/// </summary>37
/// <param name="SQL">查询语句。比如select * from tableName</param>38
/// <returns>返回DataTable</returns>39
函数实现 — — RunSqlDataTable#region 函数实现 — — RunSqlDataTable40
public DataTable RunSqlDataTable(string SQL)41
{ 42
SetCommand(SQL,1); //设置command43
SqlDataAdapter da = new SqlDataAdapter(cm);44
try45
{46
DataTable DT = new DataTable();47
da.Fill(DT);48
return DT;49
}50
catch(Exception ex)51
{52
SetErrorMsg("RunSqlDataTable",SQL,ex.Message ); //处理错误53
return null;54
}55
finally56
{57
//自动关闭了，不用手动关闭。58
da.Dispose();59
}60
}61
#endregion62
63
64
/**//// <summary>65
/// 运行SQl语句返回第一条记录。返回DataRow66
/// </summary>67
/// <param name="SQL">查询语句。比如select * from tableName</param>68
/// <returns></returns>69
函数实现 — — RunSqlDataRow#region 函数实现 — — RunSqlDataRow70
public DataRow RunSqlDataRow(string SQL)71
{ 72
SetCommand(SQL,1); //设置command73
SqlDataAdapter da = new SqlDataAdapter(cm);74
try75
{76
DataTable DT = new DataTable();77
da.Fill(DT);78
if (DT.Rows.Count > 0)79
return DT.Rows[0];80
else 81
return null;82
}83
catch(Exception ex)84
{85
SetErrorMsg("RunSqlDataRow",SQL,ex.Message ); //处理错误86
return null;87
}88
finally89
{90
//自动关闭了，不用手动关闭。91
da.Dispose();92
}93
94
}95
#endregion96
97
98
/**//// <summary>99
/// 运行SQl语句返回第一条记录的数组。返回字符串数组100
/// </summary>101
/// <param name="SQL">查询语句。比如select top 1 * from tableName</param>102
/// <returns></returns>103
函数实现 — — RunSqlStrings#region 函数实现 — — RunSqlStrings104
public string[] RunSqlStrings(string SQL)105
{ 106
//返回ID 传入查询语句，返回第一条记录的第一的字段的值107
SetCommand(SQL,1); //设置command108
SqlDataReader r = null;109
try110
{111
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )112
cm.Connection.Open();113
114
r = cm.ExecuteReader();115
string[] strValue = null;116
if (r.Read() )117
{118
int ArrLength = r.FieldCount;119
120
strValue = new string[ArrLength];121
for(int i=0;i<ArrLength;i++)122
strValue[i] = r.GetValue(i).ToString();123
124
}125
126
return strValue;127
}128
catch(Exception ex)129
{130
SetErrorMsg("RunSqlStrs",SQL,ex.Message ); //处理错误131
return null;132
}133
finally134
{135
if (r != null)136
r.Close();137
138
if (!isUseTrans)139
cm.Connection.Close();140
141
}142
}143
#endregion144
145
/**//// <summary>146
/// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组147
/// </summary>148
/// <param name="SQL">查询语句。比如select myName from tableName</param>149
/// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>150
/// <returns></returns>151
函数实现 — — RunSqlStringsByRow#region 函数实现 — — RunSqlStringsByRow152
public string[] RunSqlStringsByRow(string SQL,string SQLForCount)153
{154
//先获取记录数155
int RowCount = 0;156
if (Functions.IsInt(SQLForCount))157
{158
RowCount = Int32.Parse(SQLForCount);159
}160
else161
{162
string strRowCount = RunSqlGetID(SQLForCount);163
if (strRowCount == null)164
return null;165
166
RowCount = Int32.Parse(strRowCount);167
}168
if (RowCount <1)169
return null;170
171
//传入查询语句，返回每条记录的第一的字段的值172
SetCommand(SQL,1); //设置command173
SqlDataReader r = null;174
try175
{176
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )177
cm.Connection.Open();178
179
string[] strValue = new string[RowCount];180
r = cm.ExecuteReader();181
int i = 0;182
while (r.Read())183
{184
strValue[i] = r[0].ToString();185
i++;186
}187
return strValue;188
189
190
}191
catch(Exception ex)192
{193
SetErrorMsg("RunSqlStringsByRow",SQL,ex.Message ); //处理错误194
return null;195
}196
finally197
{198
if (r != null)199
r.Close();200
201
if (!isUseTrans)202
cm.Connection.Close();203
204
}205
}206
#endregion207
208
/**//// <summary>209
/// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组210
/// </summary>211
/// <param name="SQL">查询语句。比如select myName from tableName</param>212
/// <returns></returns>213
函数实现 — — RunSqlStringsByRow#region 函数实现 — — RunSqlStringsByRow214
public string[] RunSqlStringsByRow(string SQL)215
{216
217
//传入查询语句，返回每条记录的第一的字段的值218
SetCommand(SQL,1); //设置command219
SqlDataReader r = null;220
try221
{222
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )223
cm.Connection.Open();224
225
r = cm.ExecuteReader();226
//int i = 0;227
System.Collections.IList li = new System.Collections.ArrayList();228
while (r.Read())229
li.Add(r[0].ToString());230
231
string[] strValue = new string[li.Count];232
233
li.CopyTo(strValue,0);234
235
return strValue;236
237
}238
catch(Exception ex)239
{240
SetErrorMsg("RunSqlStringsByRow",SQL,ex.Message ); //处理错误241
return null;242
}243
finally244
{245
if (r != null)246
r.Close();247
if (!isUseTrans)248
cm.Connection.Close();249
250
}251
}252
#endregion253
254
/**//// <summary>255
/// 运行SQl语句返回第一条记录的第一列的值。256
/// </summary>257
/// <param name="SQL">查询语句。比如select top 1 ID from tableName where userName='aa'。会返回ID的内容</param>258
/// <returns></returns>259
函数实现 — — RunSqlGetID#region 函数实现 — — RunSqlGetID260
public string RunSqlGetID(string SQL)261
{ 262
SetCommand(SQL,1); //设置command263
SqlDataReader r = null;264
try265
{266
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )267
cm.Connection.Open();268
269
r = cm.ExecuteReader(CommandBehavior.SingleRow);270
string re = "";271
if (r.Read())272
re = r.GetValue(0).ToString();273
else274
re = null;275
276
return re;277
278
}279
catch(Exception ex)280
{281
SetErrorMsg("RunSqlGetID",SQL,ex.Message ); //处理错误282
return null;283
}284
finally285
{286
if (r != null)287
r.Close();288
289
if (!isUseTrans)290
cm.Connection.Close();291
292
}293
}294
#endregion295
296
#endregion
297
298
运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）
#region 运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）299
/**//// <summary>300
/// 运行SQL查询语句，不返回记录集。用于添加、修改、删除等操作301
/// </summary>302
/// <param name="SQL">查询语句。比如insert into tableName 、update tableName</param>303
/// <returns></returns>304
函数实现 — — RunSql#region 函数实现 — — RunSql305
public void RunSql(string SQL)306
{ 307
SetCommand(SQL,1); //设置command308
try309
{310
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )311
cm.Connection.Open();312
executeRowCount = cm.ExecuteNonQuery();313
}314
catch(Exception ex)315
{316
SetErrorMsg("RunSql",SQL,ex.Message ); //处理错误317
}318
finally319
{320
if (!isUseTrans)321
cm.Connection.Close();322
}323
}324
#endregion325
326
327
/**//// <summary>328
/// 执行一条SQL语句，看是否能查到记录 有：返回true；没有返回false，用于判断是否重名329
/// </summary>330
/// <param name="SQL">查询语句。比如select ID from tableName where userName='aa'</param>331
/// <returns></returns>332
函数实现 — — RunSqlExists#region 函数实现 — — RunSqlExists333
public bool RunSqlExists( string SQL)334
{335
SetCommand(SQL,1); //设置command336
SqlDataReader r = null;337
try338
{339
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )340
cm.Connection.Open();341
342
r = cm.ExecuteReader();343
bool re = true;344
if (r.HasRows)345
re = true;346
else347
re = false;348
349
return re;350
}351
catch(Exception ex)352
{353
errorMsg = "运行RunSqlExists函数时出现错误。<BR>错误信息：" + ex.Message;354
SetErrorMsg("RunSqlDataSet",SQL,ex.Message ); //处理错误355
return true;356
}357
finally358
{359
if (r != null)360
r.Close();361
362
if (!isUseTrans)363
cm.Connection.Close();364
}365
}366
#endregion367
368
#endregion
369
370
查询语句的方式添加、修改数据
#region 查询语句的方式添加、修改数据371
372
/**//// <summary>373
/// 添加记录。传入表名，字段数组，值数组，返回新生成记录的ID374
/// </summary>375
/// <param name="TableName">要添加记录的表的名称</param>376
/// <param name="ziduan">字段名数组</param>377
/// <param name="msg">字段对应的值的数组</param>378
/// <returns></returns>379
public string InsertDataStr(string TableName , string[] ziduan , string[] msg )380
{381
//添加数据 返回新添加的ID382
System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);383
SQL.Append("insert into "); //insert into 384
SQL.Append(TableName);385
SQL.Append(" ([");386
int i;387
for( i = 0 ;i< ziduan.Length ;i++) //字段388
{389
if (msg[i] != "_n_")390
{391
SQL.Append(ziduan[i]);392
SQL.Append("],[");393
}394
}395
SQL = SQL.Remove(SQL.Length -2,2);396
397
SQL.Append(") values ('");398
399
for( i = 0;i<ziduan.Length ;i++)400
{401
if (msg[i] != "_n_")402
{403
SQL.Append(msg[i]);404
SQL.Append("','");405
}406
}407
SQL = SQL.Remove(SQL.Length -2,2);408
409
SQL.Append(") select scope_identity() as a1");410
411
string re = RunSqlGetID(SQL.ToString());412
SQL.Length = 1;413
if (re == null)414
return "-1";415
else416
return re;417
}418
419
/**//// <summary>420
/// 修改记录。传入表名，字段数组，值数组 ，修改条件421
/// </summary>422
/// <param name="TableName">要修改记录的表的名称</param>423
/// <param name="ziduan">字段名数组</param>424
/// <param name="msg">字段对应的值的数组</param>425
/// <param name="tiaojian">条件 ，加在where 后面的语句</param>426
/// <returns></returns>427
public bool UpdateData( string TableName ,string[] ziduan ,string[] msg ,string tiaojian) 428
{429
System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);430
SQL.Append("update "); //update431
SQL.Append(TableName);432
SQL.Append(" set ");433
int i;434
for (i = 0 ;i< ziduan.Length ;i++)435
{436
if (msg[i] != "_n_")437
{438
SQL.Append("[");439
SQL.Append(ziduan[i]); //update440
SQL.Append("]='");441
SQL.Append(msg[i]);442
SQL.Append("',");443
}444
}445
SQL = SQL.Remove(SQL.Length-1,1); //去掉最后一个 ","446
SQL.Append(" where ");447
SQL.Append(tiaojian);448
449
RunSql(SQL.ToString());450
return true;451
452
}453
454
"20071220增加的通过 FormTableCntrlInfo 保存数据的方法"#region "20071220增加的通过 FormTableCntrlInfo 保存数据的方法"455
/**//// <summary>456
/// 添加记录。传入表名，FormTableCntrlInfo，返回新生成记录的ID457
/// </summary>458
/// <param name="TableName">要添加记录的表的名称</param>459
/// <param name="cInfo">FormTableCntrlInfo</param>460
/// <returns></returns>461
public string InsertDataStr(string TableName , FormTableCntrlInfo[] cInfo )462
{463
//添加数据 返回新添加的ID464
System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);465
SQL.Append("insert into "); //insert into 466
SQL.Append(TableName);467
SQL.Append(" ([");468
int i;469
for( i = 0 ;i < cInfo.Length ;i++) //字段470
{471
if (cInfo[i].FieldValue != "_n_")472
{473
SQL.Append(cInfo[i].FieldName);474
SQL.Append("],[");475
}476
}477
SQL = SQL.Remove(SQL.Length -2,2);478
479
SQL.Append(") values ('");480
481
for( i = 0;i < cInfo.Length ;i++)482
{483
if (cInfo[i].FieldValue != "_n_")484
{485
SQL.Append(cInfo[i].FieldValue);486
SQL.Append("','");487
}488
}489
SQL = SQL.Remove(SQL.Length -2,2);490
491
SQL.Append(") select scope_identity() as a1");492
493
string re = RunSqlGetID(SQL.ToString());494
SQL.Length = 1;495
if (re == null)496
return "-1";497
else498
return re;499
}500
501
/**//// <summary>502
/// 修改记录。传入表名，FormTableCntrlInfo，修改条件503
/// </summary>504
/// <param name="TableName">要修改记录的表的名称</param>505
/// <param name="cInfo">FormTableCntrlInfo</param>506
/// <param name="tiaojian">条件 ，加在where 后面的语句</param>507
/// <returns></returns>508
public bool UpdateData( string TableName ,FormTableCntrlInfo[] cInfo ,string tiaojian) 509
{510
System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);511
SQL.Append("update "); //update512
SQL.Append(TableName);513
SQL.Append(" set ");514
int i;515
for (i = 0 ;i< cInfo.Length ;i++)516
{517
if (cInfo[i].FieldValue != "_n_")518
{519
SQL.Append("[");520
SQL.Append(cInfo[i].FieldName); //update521
SQL.Append("]='");522
SQL.Append(cInfo[i].FieldValue);523
SQL.Append("',");524
}525
}526
SQL = SQL.Remove(SQL.Length-1,1); //去掉最后一个 ","527
SQL.Append(" where ");528
SQL.Append(tiaojian);529
530
RunSql(SQL.ToString());531
return true;532
533
}534
535
#endregion536
537
#endregion
538
下载全部源文件。
