不管什么系统都免不了对数据访问操作,现在公布出来一些对数据库SQL Server操作的
基本类库,具体代码如下:
基本类库,具体代码如下:
1
//===============================================================================
2// This file is based on the Microsoft Data Access Application Block for .NET
3// For more information please go to
4// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
5//===============================================================================
6
7using System;
8using System.Configuration;
9using System.Data;
10using System.Data.SqlClient;
11using System.Collections;
12
13
namespace EBUILDS.PM.SQLServerDAL {
14
15
/// <summary>
16 /// The SqlHelper class is intended to encapsulate high performance,
17 /// scalable best practices for common uses of SqlClient.
18
/// </summary>
19 public abstract class SQLHelper {
20
21 //Database connection strings
22 public static readonly string CONN_STRING_NON_DTC ="server=chyli;database=pmdb;uid=sa;pwd=ebuilds";// ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString1"]);
23// public static readonly string CONN_STRING_DTC_INV = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString2"]);
24// public static readonly string CONN_STRING_DTC_ORDERS = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString3"]);
25
26 // Hashtable to store cached parameters
27 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
28
29
30 /**************************************
31 ** 描述：对SQLHelper进行招展,满足特殊要求
32 ** 执行SQL语句，不需要参数
33 **************************************/
34// public static string ExecuteScalar(string connString,SqlCommand cmd)
35// {
36//
37//
38//
39// using (SqlConnection conn = new SqlConnection(connString))
40// {
41// if (conn.State != ConnectionState.Open)
42// {
43// conn.Open();
44// }
45// cmd.Connection = conn;
46// string val = Convert.ToString(cmd.ExecuteScalar());
47// cmd.Dispose();
48// return val;
49// }
50// }
51 public static string ExecuteScalar(string connString,string cmdText)
52 {
53
54 SqlCommand cmd = new SqlCommand(cmdText);
55
56 using (SqlConnection conn = new SqlConnection(connString))
57 {
58 if (conn.State != ConnectionState.Open)
59 {
60 conn.Open();
61 }
62 cmd.Connection = conn;
63 string val = Convert.ToString(cmd.ExecuteScalar());
64 cmd.Dispose();
65 return val;
66 }
67 }
68 /**************************************
69 ** 描述：对SQLHelper进行招展,满足特殊要求
70 ** 执行SQL语句，不需要参数
71 **************************************/
72
73 public static int ExecuteNonQuery(string connString,string cmdText)
74 {
75
76 SqlCommand cmd = new SqlCommand(cmdText);
77
78 using (SqlConnection conn = new SqlConnection(connString))
79 {
80 if (conn.State != ConnectionState.Open)
81 {
82 conn.Open();
83 }
84 cmd.Connection = conn;
85 int val = cmd.ExecuteNonQuery();
86 cmd.Dispose();
87 return val;
88 }
89 }
90
91 /**************************************
92 ** 描述：对SQLHelper进行招展,满足特殊要求
93 ** 执行SQL语句，不需要参数
94 **************************************/
95 public static int ExecuteNonQuery(string connString,string[] cmdText)
96 {
97 int val = 0;
98 SqlConnection myConnection = new SqlConnection(connString);
99 myConnection.Open();
100
101 SqlCommand myCommand = myConnection.CreateCommand();
102 SqlTransaction myTrans;
103
104 // Start a local transaction
105 myTrans = myConnection.BeginTransaction();
106 // Must assign both transaction object and connection
107 // to Command object for a pending local transaction
108 myCommand.Connection = myConnection;
109 myCommand.Transaction = myTrans;
110
111 try
112 {
113 foreach( string strSql in cmdText)
114 {
115 myCommand.CommandText = strSql;
116 val = myCommand.ExecuteNonQuery();
117 }
118 myTrans.Commit();
119
120 }
121 catch
122 {
123 myTrans.Rollback();
124
125 }
126 return val;
127 }
128
129 /// <summary>
130 /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
131 /// using the provided parameters.
132 /// </summary>
133 /// <remarks>
134 /// e.g.:
135 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
136 /// </remarks>
137 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
138 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
139 /// <param name="commandText">the stored procedure name or T-SQL command</param>
140 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
141 /// <returns>an int representing the number of rows affected by the command</returns>
142 public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
143
144 SqlCommand cmd = new SqlCommand();
145
146 using (SqlConnection conn = new SqlConnection(connString)) {
147 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
148 int val = cmd.ExecuteNonQuery();
149 cmd.Parameters.Clear();
150 return val;
151 }
152 }
153
154 /// <summary>
155 /// Execute a SqlCommand (that returns no resultset) against an existing database connection
156 /// using the provided parameters.
157 /// </summary>
158 /// <remarks>
159 /// e.g.:
160 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
161 /// </remarks>
162 /// <param name="conn">an existing database connection</param>
163 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
164 /// <param name="commandText">the stored procedure name or T-SQL command</param>
165 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
166 /// <returns>an int representing the number of rows affected by the command</returns>
167 public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
168
169 SqlCommand cmd = new SqlCommand();
170
171 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
172 int val = cmd.ExecuteNonQuery();
173 cmd.Parameters.Clear();
174 return val;
175 }
176
177 /// <summary>
178 /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
179 /// using the provided parameters.
180 /// </summary>
181 /// <remarks>
182 /// e.g.:
183 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
184 /// </remarks>
185 /// <param name="trans">an existing sql transaction</param>
186 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
187 /// <param name="commandText">the stored procedure name or T-SQL command</param>
188 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
189 /// <returns>an int representing the number of rows affected by the command</returns>
190 public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
191 SqlCommand cmd = new SqlCommand();
192 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
193 int val = cmd.ExecuteNonQuery();
194 cmd.Parameters.Clear();
195 return val;
196 }
197
198 /// <summary>
199 /// Execute a SqlCommand that returns a resultset against the database specified in the connection string
200 /// using the provided parameters.
201 /// </summary>
202 /// <remarks>
203 /// e.g.:
204 /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
205 /// </remarks>
206 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
207 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
208 /// <param name="commandText">the stored procedure name or T-SQL command</param>
209 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
210 /// <returns>A SqlDataReader containing the results</returns>
211 public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
212 SqlCommand cmd = new SqlCommand();
213 SqlConnection conn = new SqlConnection(connString);
214
215 // we use a try/catch here because if the method throws an exception we want to
216 // close the connection throw code, because no datareader will exist, hence the
217 // commandBehaviour.CloseConnection will not work
218 try {
219 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
220 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
221 cmd.Parameters.Clear();
222 return rdr;
223 }catch {
224 conn.Close();
225 throw;
226 }
227 }
228
229 /// <summary>
230 /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
231 /// using the provided parameters.
232 /// </summary>
233 /// <remarks>
234 /// e.g.:
235 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
236 /// </remarks>
237 /// <param name="connectionString">a valid connection string for a SqlConnection</param>
238 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
239 /// <param name="commandText">the stored procedure name or T-SQL command</param>
240 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
241 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
242 public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
243 SqlCommand cmd = new SqlCommand();
244
245 using (SqlConnection conn = new SqlConnection(connString)) {
246 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
247 object val = cmd.ExecuteScalar();
248 cmd.Parameters.Clear();
249 return val;
250 }
251 }
252 public static SqlDataReader ExecuteScalar(string connString, SqlCommand cmd)
253 {
254 SqlConnection conn = new SqlConnection(connString);
255
256 // we use a try/catch here because if the method throws an exception we want to
257 // close the connection throw code, because no datareader will exist, hence the
258 // commandBehaviour.CloseConnection will not work
259 try
260 {
261 cmd.Connection=conn;
262 conn.Open();
263 SqlDataReader rdr = cmd.ExecuteReader();
264 return rdr;
265 }
266 catch
267 {
268 conn.Close();
269 throw;
270 }
271 finally
272 {
273
274 }
275 }
276
277 /// <summary>
278 /// Execute a SqlCommand that returns the first column of the first record against an existing database connection
279 /// using the provided parameters.
280 /// </summary>
281 /// <remarks>
282 /// e.g.:
283 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
284 /// </remarks>
285 /// <param name="conn">an existing database connection</param>
286 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
287 /// <param name="commandText">the stored procedure name or T-SQL command</param>
288 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
289 /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
290 public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
291
292 SqlCommand cmd = new SqlCommand();
293
294 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
295 object val = cmd.ExecuteScalar();
296 cmd.Parameters.Clear();
297 return val;
298 }
299
300 /// <summary>
301 /// add parameter array to the cache
302 /// </summary>
303 /// <param name="cacheKey">Key to the parameter cache</param>
304 /// <param name="cmdParms">an array of SqlParamters to be cached</param>
305 public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms) {
306 parmCache[cacheKey] = cmdParms;
307 }
308
309 /// <summary>
310 /// Retrieve cached parameters
311 /// </summary>
312 /// <param name="cacheKey">key used to lookup parameters</param>
313 /// <returns>Cached SqlParamters array</returns>
314 public static SqlParameter[] GetCachedParameters(string cacheKey) {
315 SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
316
317 if (cachedParms == null)
318 return null;
319
320 SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
321
322 for (int i = 0, j = cachedParms.Length; i < j; i++)
323 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
324
325 return clonedParms;
326 }
327
328 /// <summary>
329 /// Prepare a command for execution
330 /// </summary>
331 /// <param name="cmd">SqlCommand object</param>
332 /// <param name="conn">SqlConnection object</param>
333 /// <param name="trans">SqlTransaction object</param>
334 /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
335 /// <param name="cmdText">Command text, e.g. Select * from Products</param>
336 /// <param name="cmdParms">SqlParameters to use in the command</param>
337 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) {
338
339 if (conn.State != ConnectionState.Open)
340 conn.Open();
341
342 cmd.Connection = conn;
343 cmd.CommandText = cmdText;
344
345 if (trans != null)
346 cmd.Transaction = trans;
347
348 cmd.CommandType = cmdType;
349
350 if (cmdParms != null) {
351 foreach (SqlParameter parm in cmdParms)
352 cmd.Parameters.Add(parm);
353 }
354 }
355 public static DataSet GetDs(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) //获取有输入参数的sqlcommand返回数据集
356 {
357 SqlCommand cmd = new SqlCommand();
358
359 using (SqlConnection conn = new SqlConnection(connString))
360 {
361 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
362 SqlDataAdapter adp=new SqlDataAdapter(cmd);
363 DataSet ds=new DataSet();
364 adp.Fill(ds);
365 cmd.Parameters.Clear();
366 return ds;
367 }
368 }
369 public static DataSet GetDs(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
370 {
371 SqlCommand cmd = new SqlCommand();
372
373 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
374 SqlDataAdapter adp=new SqlDataAdapter(cmd);
375 DataSet ds=new DataSet();
376 adp.Fill(ds);
377 cmd.Parameters.Clear();
378 return ds;
379 }
380 public static DataSet GetDs(SqlConnection conn, CommandType cmdType, string cmdText) //获取无输入参数的sqlcommand返回数据集
381 {
382 SqlCommand cmd = new SqlCommand();
383 SqlDataAdapter adp=new SqlDataAdapter();
384 adp.SelectCommand.CommandType=cmdType;
385 adp.SelectCommand.Connection=conn;
386 adp.SelectCommand.CommandText=cmdText;
387 DataSet ds=new DataSet();
388 adp.Fill(ds);
389 return ds;
390 }
391 public static DataSet GetDs(string connString, CommandType cmdType, string cmdText) //
392 {
393
394 SqlCommand cmd = new SqlCommand();
395 SqlDataAdapter adp=new SqlDataAdapter();
396 adp.SelectCommand.CommandType=cmdType;
397 adp.SelectCommand.Connection=new SqlConnection(connString);
398 adp.SelectCommand.CommandText=cmdText;
399 DataSet ds=new DataSet();
400 adp.Fill(ds);
401 return ds;
402 }
403 public static Boolean ExecuteNonQuery (string connString, SqlCommand cmd)// 执行无返回结果的sqlcommand语句
404 {
405 try
406 {
407 cmd.Connection=new SqlConnection(connString);
408 cmd.Connection.Open();
409 cmd.ExecuteNonQuery();
410 return true;
411 }
412 catch
413 {
414 return false;
415 }
416 finally
417 {
418 cmd.Connection.Close();
419 }
420 }
421 public static DataSet GetDs(SqlConnection conn, SqlCommand cmd) //通过事情sqlcommand语句获取数据集
422 {
423 SqlDataAdapter adp=new SqlDataAdapter();
424 adp.SelectCommand=cmd;
425 adp.SelectCommand.Connection=conn;
426 DataSet ds=new DataSet();
427 adp.Fill(ds);
428 return ds;
429 }
430 public static DataSet GetDs(string connString, SqlCommand cmd) //通过事情sqlcommand语句获取数据集
431 {
432
433 SqlDataAdapter adp=new SqlDataAdapter();
434 adp.SelectCommand=cmd;
435 adp.SelectCommand.Connection=new SqlConnection(connString);
436 DataSet ds=new DataSet();
437 adp.Fill(ds);
438 return ds;
439 }
440 public static DataSet GetDs(SqlConnection conn, string sqlstr) //通过sql语句获取数据集
441 {
442
443 SqlDataAdapter adp=new SqlDataAdapter(sqlstr,conn);
444 DataSet ds=new DataSet();
445 adp.Fill(ds);
446 return ds;
447 }
448 public static DataSet GetDs(string connString, string sqlstr) //通过sql语句获取数据集
449 {
450
451 SqlDataAdapter adp=new SqlDataAdapter(sqlstr,new SqlConnection(connString));
452 DataSet ds=new DataSet();
453 adp.Fill(ds);
454 return ds;
455 }
456 /// <summary>
457 /// 传入输入参数
458 /// </summary>
459 /// <param name="ParamName">存储过程名称</param>
460 /// <param name="DbType">参数类型</param></param>
461 /// <param name="Size">参数大小</param>
462 /// <param name="Value">参数值</param>
463 /// <returns>新的 parameter 对象</returns>
464 public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
465 {
466 return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
467 }
468
469 /// <summary>
470 /// 传入返回值参数
471 /// </summary>
472 /// <param name="ParamName">存储过程名称</param>
473 /// <param name="DbType">参数类型</param>
474 /// <param name="Size">参数大小</param>
475 /// <returns>新的 parameter 对象</returns>
476 public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
477 {
478 return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
479 }
480 /// <summary>
481 /// 传入返回值参数
482 /// </summary>
483 /// <param name="ParamName">存储过程名称</param>
484 /// <param name="DbType">参数类型</param>
485 /// <param name="Size">参数大小</param>
486 /// <returns>新的 parameter 对象</returns>
487 public static SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size)
488 {
489 return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
490 }
491
492 /// <summary>
493 /// 生成存储过程参数
494 /// </summary>
495 /// <param name="ParamName">存储过程名称</param>
496 /// <param name="DbType">参数类型</param>
497 /// <param name="Size">参数大小</param>
498 /// <param name="Direction">参数方向</param>
499 /// <param name="Value">参数值</param>
500 /// <returns>新的 parameter 对象</returns>
501 public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
502 {
503 SqlParameter param;
504
505 if(Size > 0)
506 param = new SqlParameter(ParamName, DbType, Size);
507 else
508 param = new SqlParameter(ParamName, DbType);
509
510 param.Direction = Direction;
511 if (!(Direction == ParameterDirection.Output && Value == null))
512 param.Value = Value;
513
514 return param;
515 }
516
517 }
518
}
//===============================================================================2
// This file is based on the Microsoft Data Access Application Block for .NET3
// For more information please go to 4
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp5
//===============================================================================6
7
using System;8
using System.Configuration;9
using System.Data;10
using System.Data.SqlClient;11
using System.Collections;12
13
namespace EBUILDS.PM.SQLServerDAL {
14
15
/// <summary>
16
/// The SqlHelper class is intended to encapsulate high performance, 17
/// scalable best practices for common uses of SqlClient.18
/// </summary>19
public abstract class SQLHelper {20
21
//Database connection strings22
public static readonly string CONN_STRING_NON_DTC ="server=chyli;database=pmdb;uid=sa;pwd=ebuilds";// ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString1"]);23
// public static readonly string CONN_STRING_DTC_INV = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString2"]); 24
// public static readonly string CONN_STRING_DTC_ORDERS = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString3"]);25
26
// Hashtable to store cached parameters27
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());28
29
30
/**************************************31
** 描述：对SQLHelper进行招展,满足特殊要求32
** 执行SQL语句，不需要参数33
**************************************/34
// public static string ExecuteScalar(string connString,SqlCommand cmd) 35
// {36
//37
// 38
//39
// using (SqlConnection conn = new SqlConnection(connString)) 40
// {41
// if (conn.State != ConnectionState.Open)42
// { 43
// conn.Open();44
// }45
// cmd.Connection = conn;46
// string val = Convert.ToString(cmd.ExecuteScalar());47
// cmd.Dispose();48
// return val;49
// }50
// }51
public static string ExecuteScalar(string connString,string cmdText) 52
{53
54
SqlCommand cmd = new SqlCommand(cmdText);55
56
using (SqlConnection conn = new SqlConnection(connString)) 57
{58
if (conn.State != ConnectionState.Open)59
{ 60
conn.Open();61
}62
cmd.Connection = conn;63
string val = Convert.ToString(cmd.ExecuteScalar());64
cmd.Dispose();65
return val;66
}67
}68
/**************************************69
** 描述：对SQLHelper进行招展,满足特殊要求70
** 执行SQL语句，不需要参数71
**************************************/72
73
public static int ExecuteNonQuery(string connString,string cmdText) 74
{75
76
SqlCommand cmd = new SqlCommand(cmdText);77
78
using (SqlConnection conn = new SqlConnection(connString)) 79
{80
if (conn.State != ConnectionState.Open)81
{ 82
conn.Open();83
}84
cmd.Connection = conn;85
int val = cmd.ExecuteNonQuery();86
cmd.Dispose();87
return val;88
}89
}90
91
/**************************************92
** 描述：对SQLHelper进行招展,满足特殊要求93
** 执行SQL语句，不需要参数94
**************************************/95
public static int ExecuteNonQuery(string connString,string[] cmdText) 96
{97
int val = 0;98
SqlConnection myConnection = new SqlConnection(connString);99
myConnection.Open();100
101
SqlCommand myCommand = myConnection.CreateCommand();102
SqlTransaction myTrans;103
104
// Start a local transaction105
myTrans = myConnection.BeginTransaction();106
// Must assign both transaction object and connection107
// to Command object for a pending local transaction108
myCommand.Connection = myConnection;109
myCommand.Transaction = myTrans;110
111
try112
{113
foreach( string strSql in cmdText)114
{115
myCommand.CommandText = strSql;116
val = myCommand.ExecuteNonQuery();117
} 118
myTrans.Commit();119
120
}121
catch122
{123
myTrans.Rollback();124
125
}126
return val;127
}128
129
/// <summary>130
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 131
/// using the provided parameters.132
/// </summary>133
/// <remarks>134
/// e.g.: 135
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));136
/// </remarks>137
/// <param name="connectionString">a valid connection string for a SqlConnection</param>138
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>139
/// <param name="commandText">the stored procedure name or T-SQL command</param>140
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>141
/// <returns>an int representing the number of rows affected by the command</returns>142
public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {143
144
SqlCommand cmd = new SqlCommand();145
146
using (SqlConnection conn = new SqlConnection(connString)) {147
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);148
int val = cmd.ExecuteNonQuery();149
cmd.Parameters.Clear();150
return val;151
}152
}153
154
/// <summary>155
/// Execute a SqlCommand (that returns no resultset) against an existing database connection 156
/// using the provided parameters.157
/// </summary>158
/// <remarks>159
/// e.g.: 160
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));161
/// </remarks>162
/// <param name="conn">an existing database connection</param>163
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>164
/// <param name="commandText">the stored procedure name or T-SQL command</param>165
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>166
/// <returns>an int representing the number of rows affected by the command</returns>167
public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {168
169
SqlCommand cmd = new SqlCommand();170
171
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);172
int val = cmd.ExecuteNonQuery();173
cmd.Parameters.Clear();174
return val;175
}176
177
/// <summary>178
/// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction 179
/// using the provided parameters.180
/// </summary>181
/// <remarks>182
/// e.g.: 183
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));184
/// </remarks>185
/// <param name="trans">an existing sql transaction</param>186
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>187
/// <param name="commandText">the stored procedure name or T-SQL command</param>188
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>189
/// <returns>an int representing the number of rows affected by the command</returns>190
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {191
SqlCommand cmd = new SqlCommand();192
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);193
int val = cmd.ExecuteNonQuery();194
cmd.Parameters.Clear();195
return val;196
}197
198
/// <summary>199
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string 200
/// using the provided parameters.201
/// </summary>202
/// <remarks>203
/// e.g.: 204
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));205
/// </remarks>206
/// <param name="connectionString">a valid connection string for a SqlConnection</param>207
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>208
/// <param name="commandText">the stored procedure name or T-SQL command</param>209
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>210
/// <returns>A SqlDataReader containing the results</returns>211
public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {212
SqlCommand cmd = new SqlCommand();213
SqlConnection conn = new SqlConnection(connString);214
215
// we use a try/catch here because if the method throws an exception we want to 216
// close the connection throw code, because no datareader will exist, hence the 217
// commandBehaviour.CloseConnection will not work218
try {219
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);220
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);221
cmd.Parameters.Clear();222
return rdr;223
}catch {224
conn.Close();225
throw;226
}227
}228
229
/// <summary>230
/// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 231
/// using the provided parameters.232
/// </summary>233
/// <remarks>234
/// e.g.: 235
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));236
/// </remarks>237
/// <param name="connectionString">a valid connection string for a SqlConnection</param>238
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>239
/// <param name="commandText">the stored procedure name or T-SQL command</param>240
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>241
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>242
public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {243
SqlCommand cmd = new SqlCommand();244
245
using (SqlConnection conn = new SqlConnection(connString)) {246
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);247
object val = cmd.ExecuteScalar();248
cmd.Parameters.Clear();249
return val;250
}251
}252
public static SqlDataReader ExecuteScalar(string connString, SqlCommand cmd) 253
{254
SqlConnection conn = new SqlConnection(connString);255
256
// we use a try/catch here because if the method throws an exception we want to 257
// close the connection throw code, because no datareader will exist, hence the 258
// commandBehaviour.CloseConnection will not work259
try 260
{261
cmd.Connection=conn;262
conn.Open();263
SqlDataReader rdr = cmd.ExecuteReader();264
return rdr;265
}266
catch 267
{268
conn.Close();269
throw;270
}271
finally272
{273
274
}275
}276
277
/// <summary>278
/// Execute a SqlCommand that returns the first column of the first record against an existing database connection 279
/// using the provided parameters.280
/// </summary>281
/// <remarks>282
/// e.g.: 283
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));284
/// </remarks>285
/// <param name="conn">an existing database connection</param>286
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>287
/// <param name="commandText">the stored procedure name or T-SQL command</param>288
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>289
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>290
public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {291
292
SqlCommand cmd = new SqlCommand();293
294
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);295
object val = cmd.ExecuteScalar();296
cmd.Parameters.Clear();297
return val;298
}299
300
/// <summary>301
/// add parameter array to the cache302
/// </summary>303
/// <param name="cacheKey">Key to the parameter cache</param>304
/// <param name="cmdParms">an array of SqlParamters to be cached</param>305
public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms) {306
parmCache[cacheKey] = cmdParms;307
}308
309
/// <summary>310
/// Retrieve cached parameters311
/// </summary>312
/// <param name="cacheKey">key used to lookup parameters</param>313
/// <returns>Cached SqlParamters array</returns>314
public static SqlParameter[] GetCachedParameters(string cacheKey) {315
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];316
317
if (cachedParms == null)318
return null;319
320
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];321
322
for (int i = 0, j = cachedParms.Length; i < j; i++)323
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();324
325
return clonedParms;326
}327
328
/// <summary>329
/// Prepare a command for execution330
/// </summary>331
/// <param name="cmd">SqlCommand object</param>332
/// <param name="conn">SqlConnection object</param>333
/// <param name="trans">SqlTransaction object</param>334
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>335
/// <param name="cmdText">Command text, e.g. Select * from Products</param>336
/// <param name="cmdParms">SqlParameters to use in the command</param>337
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) {338
339
if (conn.State != ConnectionState.Open)340
conn.Open();341
342
cmd.Connection = conn;343
cmd.CommandText = cmdText;344
345
if (trans != null)346
cmd.Transaction = trans;347
348
cmd.CommandType = cmdType;349
350
if (cmdParms != null) {351
foreach (SqlParameter parm in cmdParms)352
cmd.Parameters.Add(parm);353
}354
}355
public static DataSet GetDs(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) //获取有输入参数的sqlcommand返回数据集356
{357
SqlCommand cmd = new SqlCommand();358
359
using (SqlConnection conn = new SqlConnection(connString)) 360
{361
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);362
SqlDataAdapter adp=new SqlDataAdapter(cmd);363
DataSet ds=new DataSet();364
adp.Fill(ds);365
cmd.Parameters.Clear();366
return ds;367
}368
}369
public static DataSet GetDs(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 370
{371
SqlCommand cmd = new SqlCommand();372
373
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);374
SqlDataAdapter adp=new SqlDataAdapter(cmd);375
DataSet ds=new DataSet();376
adp.Fill(ds);377
cmd.Parameters.Clear();378
return ds;379
}380
public static DataSet GetDs(SqlConnection conn, CommandType cmdType, string cmdText) //获取无输入参数的sqlcommand返回数据集381
{382
SqlCommand cmd = new SqlCommand();383
SqlDataAdapter adp=new SqlDataAdapter();384
adp.SelectCommand.CommandType=cmdType;385
adp.SelectCommand.Connection=conn;386
adp.SelectCommand.CommandText=cmdText;387
DataSet ds=new DataSet();388
adp.Fill(ds);389
return ds;390
}391
public static DataSet GetDs(string connString, CommandType cmdType, string cmdText) //392
{393
394
SqlCommand cmd = new SqlCommand();395
SqlDataAdapter adp=new SqlDataAdapter();396
adp.SelectCommand.CommandType=cmdType;397
adp.SelectCommand.Connection=new SqlConnection(connString);398
adp.SelectCommand.CommandText=cmdText;399
DataSet ds=new DataSet();400
adp.Fill(ds);401
return ds;402
}403
public static Boolean ExecuteNonQuery (string connString, SqlCommand cmd)// 执行无返回结果的sqlcommand语句404
{405
try406
{407
cmd.Connection=new SqlConnection(connString);408
cmd.Connection.Open();409
cmd.ExecuteNonQuery();410
return true;411
}412
catch413
{414
return false;415
}416
finally417
{418
cmd.Connection.Close();419
}420
}421
public static DataSet GetDs(SqlConnection conn, SqlCommand cmd) //通过事情sqlcommand语句获取数据集422
{423
SqlDataAdapter adp=new SqlDataAdapter();424
adp.SelectCommand=cmd;425
adp.SelectCommand.Connection=conn;426
DataSet ds=new DataSet();427
adp.Fill(ds);428
return ds;429
}430
public static DataSet GetDs(string connString, SqlCommand cmd) //通过事情sqlcommand语句获取数据集431
{432
433
SqlDataAdapter adp=new SqlDataAdapter();434
adp.SelectCommand=cmd;435
adp.SelectCommand.Connection=new SqlConnection(connString);436
DataSet ds=new DataSet();437
adp.Fill(ds);438
return ds;439
}440
public static DataSet GetDs(SqlConnection conn, string sqlstr) //通过sql语句获取数据集441
{442
443
SqlDataAdapter adp=new SqlDataAdapter(sqlstr,conn);444
DataSet ds=new DataSet();445
adp.Fill(ds);446
return ds;447
}448
public static DataSet GetDs(string connString, string sqlstr) //通过sql语句获取数据集449
{450
451
SqlDataAdapter adp=new SqlDataAdapter(sqlstr,new SqlConnection(connString));452
DataSet ds=new DataSet();453
adp.Fill(ds);454
return ds;455
}456
/// <summary>457
/// 传入输入参数458
/// </summary>459
/// <param name="ParamName">存储过程名称</param>460
/// <param name="DbType">参数类型</param></param>461
/// <param name="Size">参数大小</param>462
/// <param name="Value">参数值</param>463
/// <returns>新的 parameter 对象</returns>464
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) 465
{466
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);467
} 468
469
/// <summary>470
/// 传入返回值参数471
/// </summary>472
/// <param name="ParamName">存储过程名称</param>473
/// <param name="DbType">参数类型</param>474
/// <param name="Size">参数大小</param>475
/// <returns>新的 parameter 对象</returns>476
public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) 477
{478
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);479
} 480
/// <summary>481
/// 传入返回值参数482
/// </summary>483
/// <param name="ParamName">存储过程名称</param>484
/// <param name="DbType">参数类型</param>485
/// <param name="Size">参数大小</param>486
/// <returns>新的 parameter 对象</returns>487
public static SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size) 488
{489
return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);490
} 491
492
/// <summary>493
/// 生成存储过程参数494
/// </summary>495
/// <param name="ParamName">存储过程名称</param>496
/// <param name="DbType">参数类型</param>497
/// <param name="Size">参数大小</param>498
/// <param name="Direction">参数方向</param>499
/// <param name="Value">参数值</param>500
/// <returns>新的 parameter 对象</returns>501
public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 502
{503
SqlParameter param;504
505
if(Size > 0)506
param = new SqlParameter(ParamName, DbType, Size);507
else508
param = new SqlParameter(ParamName, DbType);509
510
param.Direction = Direction;511
if (!(Direction == ParameterDirection.Output && Value == null))512
param.Value = Value;513
514
return param;515
}516
517
}518
}
