I have sorted out the database operation classes for SQL database operation classes, which include common database operations in three ways: simple SQL concatenation of strings, SQL statements use the form of parameters and stored procedures. each form has five methods and has transactions ., can be called directly. the code is as follows:
1 // ============================================== ======================================
2 //
3 // Copyright (C) 2007-2008 March software studio
4 // All rights reserved
5 //
6 // filename: SQLDataBase
7 // description:
8 //
9 // created by Hou base at 18:33:32
10 // http://houleixx.cnblogs.com
11 //
12 // =================================================== ======================================
13
14 using System;
15 using System. Collections;
16 using System. Collections. Specialized;
17 using System. Data;
18 using System. Data. SqlClient;
19 using System. Configuration;
20 using System. Data. Common;
21
22 namespace SQLDataBase
23 {
24 /**////
25 // data access base class (based on SQLServer)
26 ///
27 class SQLDataBase
28 {
29 protected static string connectionString = ConfigurationManager. ConnectionStrings ["ConnectionString"]. ConnectionString;
30 public SQLDataBase ()
31 {
32
33}
34
35. execute simple SQL statement # region execute simple SQL statement
36
37 /**////
38 // execute the SQL statement and return the number of affected records
39 ///
40 /// SQL statement
41 /// Number of affected records
42 public int ExecuteSql (string SQLString)
43 {
44 using (SqlConnection connection = new SqlConnection (connectionString ))
45 {
46 using (SqlCommand cmd = new SqlCommand (SQLString, connection ))
47 {
48 try
49 {
50 connection. Open ();
51 int rows = cmd. ExecuteNonQuery ();
52 return rows;
53}
54 catch (System. Data. SqlClient. SqlException E)
55 {
56 connection. Close ();
57 throw new Exception (E. Message );
58}
59}
60}
61}
62
63 /**////
64 // execute multiple SQL statements to implement database transactions.
65 ///
66 /// Multiple SQL statements
67 public void ExecuteSqlTran (ArrayList SQLStringList)
68 {
69 using (SqlConnection conn = new SqlConnection (connectionString ))
70 {
71 conn. Open ();
72 SqlCommand cmd = new SqlCommand ();
73 cmd. Connection = conn;
74 SqlTransaction tx = conn. BeginTransaction ();
75 cmd. Transaction = tx;
76 try
77 {
78 for (int n = 0; n <SQLStringList. Count; n ++)
79 {
80 string strsql = SQLStringList [n]. ToString ();
81 if (strsql. Trim (). Length> 1)
82 {
83 cmd. CommandText = strsql;
84 cmd. ExecuteNonQuery ();
85}
86}
87 tx. Commit ();
88}
89 catch (System. Data. SqlClient. SqlException E)
90 {
91 tx. Rollback ();
92 throw new Exception (E. Message );
93}
94}
95}
96 /**////
97 // execute a statement to calculate the query result and return the query result (object ).
98 ///
99 /// Calculate the query result statement
100 /// Query result (object)
101 public object GetSingle (string SQLString)
102 {
103 using (SqlConnection connection = new SqlConnection (connectionString ))
104 {
105 using (SqlCommand cmd = new SqlCommand (SQLString, connection ))
106 {
107 try
108 {
109 connection. Open ();
110 object obj = cmd. ExecuteScalar ();
111 if (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
112 {
113 return null;
114}
115 else
116 {
117 return obj;
118}
119}
120 catch (System. Data. SqlClient. SqlException e)
121 {
122 connection. Close ();
123 throw new Exception (e. Message );
124}
125}
126}
127}
128 /**////
129 // execute the query statement and return SqlDataReader
130 ///
131 /// Query statement
132 /// SqlDataReader
133 public DbDataReader ExecuteReader (string strSQL)
134 {
135 SqlConnection connection = new SqlConnection (connectionString );
136 SqlCommand cmd = new SqlCommand (strSQL, connection );
137 try
138 {
139 connection. Open ();
140 SqlDataReader myReader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
141 return myReader;
142}
143 catch (System. Data. SqlClient. SqlException e)
144 {
145 throw new Exception (e. Message );
146}
147
148}
149 /**////
150 // execute the query statement and return DataSet
151 ///
152 /// Query statement
153 /// DataSet
154 public DataSet GetDataSet (string SQLString)
155 {
156 using (SqlConnection connection = new SqlConnection (connectionString ))
157 {
158 DataSet ds = new DataSet ();
159 try
160 {
161 connection. Open ();
162 SqlDataAdapter adapter = new SqlDataAdapter (SQLString, connection );
163 adapter. Fill (ds, "ds ");
164 connection. Close ();
165 return ds;
166}
167 catch (System. Data. SqlClient. SqlException ex)
168 {
169 throw new Exception (ex. Message );
170}
171}
172}
173
174
175 # endregion
176
177 run the SQL statement with parameters # region execute the SQL statement with parameters
178
179 /**////
180 // execute the SQL statement and return the number of affected records
181 ///
182 /// SQL statement
183 /// Number of affected records
184 public int ExecuteSql (string SQLString, DbParameter [] parallel parms)
185 {
186 using (SqlConnection connection = new SqlConnection (connectionString ))
187 {
188 using (SqlCommand cmd = new SqlCommand ())
189 {
190 try
191 {
192 PrepareCommand (cmd, connection, null, SQLString, callback parms );
193 int rows = cmd. ExecuteNonQuery ();
194 cmd. Parameters. Clear ();
195 return rows;
196}
197 catch (System. Data. SqlClient. SqlException E)
198 {
199 throw new Exception (E. Message );
200}
201}
202}
203}
204
205
206 /**////
207 // execute multiple SQL statements to implement database transactions.
208 ///
209 /// Hash table of an SQL statement (the key is an SQL statement, and the value is the SqlParameter [] of the statement)
210 public void ExecuteSqlTran (Hashtable SQLStringList)
211 {
212 using (SqlConnection conn = new SqlConnection (connectionString ))
213 {
214 conn. open ();
215 using (SqlTransaction trans = conn. BeginTransaction ())
216 {
217 SqlCommand cmd = new SqlCommand ();
218 try
219 {
220 // Loop
221 foreach (DictionaryEntry myDE in SQLStringList)
222 {
223 string plain text = myDE. Key. ToString ();
224 SqlParameter [] limit parms = (SqlParameter []) myDE. Value;
225 PrepareCommand (cmd, conn, trans, plain text, plain parms );
226 int val = cmd. ExecuteNonQuery ();
227 cmd. Parameters. Clear ();
228}
229 trans. Commit ();
230}
231 catch
232 {
233 trans. Rollback ();
234 throw;
235}
236}
237}
238}
239
240
241 /**////
242 // execute a computing query result statement, return the query result (object), and return the value of the first column in the first line;
243 ///
244 /// Calculate the query result statement
245 /// Query result (object)
246 public object GetSingle (string SQLString, DbParameter [] Partition parms)
247 {
248 using (SqlConnection connection = new SqlConnection (connectionString ))
249 {
250 using (SqlCommand cmd = new SqlCommand ())
251 {
252 try
253 {
254 PrepareCommand (cmd, connection, null, SQLString, callback parms );
255 object obj = cmd. ExecuteScalar ();
256 cmd. Parameters. Clear ();
257 if (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
258 {
259 return null;
260}
261 else
262 {
263 return obj;
264}
265}
266 catch (System. Data. SqlClient. SqlException e)
267 {
268 throw new Exception (e. Message );
269}
270}
271}
272}
273
274 /**////
275 // execute the query statement and return SqlDataReader
276 ///
277 /// Query statement
278 /// SqlDataReader
279 public DbDataReader ExecuteReader (string SQLString, DbParameter [] Partition parms)
280 {
281 SqlConnection connection = new SqlConnection (connectionString );
282 SqlCommand cmd = new SqlCommand ();
283 try
284 {
285 PrepareCommand (cmd, connection, null, SQLString, callback parms );
286 SqlDataReader myReader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
287 cmd. Parameters. Clear ();
288 return myReader;
289}
290 catch (System. Data. SqlClient. SqlException e)
291 {
292 throw new Exception (e. Message );
293}
294
295}
296
297 /**////
298 // execute the query statement and return DataSet
299 ///
300 /// Query statement
301 /// DataSet
302 public DataSet GetDataSet (string SQLString, DbParameter [] Partition parms)
303 {
304 using (SqlConnection connection = new SqlConnection (connectionString ))
305 {
306 SqlCommand cmd = new SqlCommand ();
307 PrepareCommand (cmd, connection, null, SQLString, callback parms );
308 using (SqlDataAdapter da = new SqlDataAdapter (cmd ))
309 {
310 DataSet ds = new DataSet ();
311 try
312 {
313 da. Fill (ds, "ds ");
314 cmd. Parameters. Clear ();
315 return ds;
316}
317 catch (System. Data. SqlClient. SqlException ex)
318 {
319 throw new Exception (ex. Message );
320}
321}
322}
323}
324
325
326 private void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string plain text, DbParameter [] Partial parms)
327 {
328 if (conn. State! = ConnectionState. Open)
329 conn. open ();
330 cmd. Connection = conn;
331 cmd. CommandText = plain text;
332 if (trans! = Null)
333 cmd. Transaction = trans;
334 cmd. CommandType = CommandType. Text; // specify type;
335 if (partition parms! = Null)
336 {
337 foreach (SqlParameter parm in milliseconds parms)
338 cmd. Parameters. Add (parm );
339}
340}
341
342 # endregion
343
344 stored procedure operations # region stored procedure operations
345 /**////
346 // execute the stored procedure;
347 ///
348 /// Stored Procedure name
349 /// Required parameters
350 /// Returns the number of affected rows.
351 public int RunProcedureExecuteSql (string storeProcName, DbParameter [] parameters)
352 {
353 using (SqlConnection connection = new SqlConnection (connectionString ))
354 {
355 SqlCommand cmd = BuildQueryCommand (connection, storeProcName, parameters );
356 int rows = cmd. ExecuteNonQuery ();
357 cmd. Parameters. Clear ();
358 connection. Close ();
359 return rows;
360}
361}
362 /**////
363 // execute the stored procedure and return the value of the first column of the first row
364 ///
365 /// Stored Procedure name
366 /// Stored Procedure parameters
367 /// Returns the value of the first column of the first row.
368 public Object RunProcedureGetSingle (string storeProcName, DbParameter [] parameters)
369 {
370 using (SqlConnection connection = new SqlConnection (connectionString ))
371 {
372 try
373 {
374 SqlCommand cmd = BuildQueryCommand (connection, storeProcName, parameters );
375 object obj = cmd. ExecuteScalar ();
376 cmd. Parameters. Clear ();
377 if (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
378 {
379 return null;
380}
381 else
382 {
383 return obj;
384}
385}
386 catch (System. Data. SqlClient. SqlException e)
387 {
388 throw new Exception (e. Message );
389}
390}
391}
392 /**////
393 // execute the stored procedure
394 ///
395 /// Stored Procedure name
396 /// Stored Procedure parameters
397 /// SqlDataReader
398 public DbDataReader RunProcedureGetDataReader (string storedProcName, DbParameter [] parameters)
399 {
400 SqlConnection connection = new SqlConnection (connectionString );
401 SqlDataReader returnReader;
402 SqlCommand cmd = BuildQueryCommand (connection, storedProcName, parameters );
403 cmd. CommandType = CommandType. StoredProcedure;
404 returnReader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
405 cmd. Parameters. Clear ();
406 return returnReader;
407}
408 /**////
409 // execute the stored procedure
410 ///
411 /// Stored Procedure name
412 /// Stored Procedure parameters
413 /// DataSet
414 public DataSet RunProcedureGetDataSet (string storedProcName, DbParameter [] parameters)
415 {
416 using (SqlConnection connection = new SqlConnection (connectionString ))
417 {
418 DataSet dataSet = new DataSet ();
419 connection. Open ();
420 SqlDataAdapter sqlDA = new SqlDataAdapter ();
421 sqlDA. SelectCommand = BuildQueryCommand (connection, storedProcName, parameters );
422 sqlDA. Fill (dataSet );
423 connection. Close ();
424 sqlDA. SelectCommand. Parameters. Clear ();
425 sqlDA. Dispose ();
426 return dataSet;
427}
428}
429 /**////
430 // execute multiple stored procedures to implement database transactions.
431 ///
432 /// Hash table of the stored procedure (the key is the DbParameter [] of the statement, and the value is the stored procedure statement)
433 public bool RunProcedureTran (Hashtable SQLStringList)
434 {
435 using (SqlConnection connection = new SqlConnection (connectionString ))
436 {
437 connection. Open ();
438 using (SqlTransaction trans = connection. BeginTransaction ())
439 {
440 SqlCommand cmd = new SqlCommand ();
441 try
442 {
443 // Loop
444 foreach (DictionaryEntry myDE in SQLStringList)
445 {
446 cmd. Connection = connection;
447 string storeName = myDE. Value. ToString ();
448 SqlParameter [] Partition parms = (SqlParameter []) myDE. Key;
449
450 cmd. Transaction = trans;
451 cmd. CommandText = storeName;
452 cmd. CommandType = CommandType. StoredProcedure;
453 if (partition parms! = Null)
454 {
455 foreach (SqlParameter parameter in milliseconds parms)
456 cmd. Parameters. Add (parameter );
457}
458 int val = cmd. ExecuteNonQuery ();
459 cmd. Parameters. Clear ();
460}
461 trans. Commit ();
462 return true;
463}
464 catch
465 {
466 trans. Rollback ();
467 return false;
468 throw;
469}
470}
471}
472}
473 /**////
474 // Construct a SqlCommand object (used to return a result set instead of an integer)
475 ///
476 /// Database Connection
477 /// Stored Procedure name
478 /// Stored Procedure parameters
479 /// SqlCommand
480 private SqlCommand BuildQueryCommand (SqlConnection connection, string storedProcName, DbParameter [] parameters)
481 {
482 if (connection. State! = ConnectionState. Open)
483 connection. Open ();
484 SqlCommand command = new SqlCommand (storedProcName, connection );
485 command. CommandType = CommandType. StoredProcedure;
486 if (parameters! = Null)
487 {
488 foreach (SqlParameter parameter in parameters)
489 {
490 command. Parameters. Add (parameter );
491}
492}
493 return command;
494}
495 # endregion
496
497}
498}
499
