简单的数据访问层

1 using System;
  2 using System.Collections;
  3 using System.Collections.Specialized;
  4 using System.Data;
  5 using System.Data.OleDb;
  6 using System.Configuration;
  7 
  8   
  9     public  class DBHelper
 10     {
 11         //数据库连接字符串(web.config来配置)
 12         //<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />        
 13         protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
 14      
 15 
 16         #region  执行简单SQL语句
 17 
 18         /// <summary>
 19         /// 执行SQL语句，返回影响的记录数
 20         /// </summary>
 21         /// <param name="SQLString">SQL语句</param>
 22         /// <returns>影响的记录数</returns>
 23         public static int ExecuteSql(string SQLString)
 24         {
 25             using (OleDbConnection connection = new OleDbConnection(connectionString))
 26             {
 27                 using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
 28                 {
 29                     try
 30                     {
 31                         connection.Open();
 32                         int rows = cmd.ExecuteNonQuery();
 33                         return rows;
 34                     }
 35                     catch (System.Data.OleDb.OleDbException E)
 36                     {
 37                         connection.Close();
 38                         throw new Exception(E.Message);
 39                     }
 40                 }
 41             }
 42         }
 43 
 44         /// <summary>
 45         /// 执行多条SQL语句，实现数据库事务。
 46         /// </summary>
 47         /// <param name="SQLStringList">多条SQL语句</param>        
 48         public static void ExecuteSqlTran(ArrayList SQLStringList)
 49         {
 50             using (OleDbConnection conn = new OleDbConnection(connectionString))
 51             {
 52                 conn.Open();
 53                 OleDbCommand cmd = new OleDbCommand();
 54                 cmd.Connection = conn;
 55                 OleDbTransaction tx = conn.BeginTransaction();
 56                 cmd.Transaction = tx;
 57                 try
 58                 {
 59                     for (int n = 0; n < SQLStringList.Count; n++)
 60                     {
 61                         string strsql = SQLStringList[n].ToString();
 62                         if (strsql.Trim().Length > 1)
 63                         {
 64                             cmd.CommandText = strsql;
 65                             cmd.ExecuteNonQuery();
 66                         }
 67                     }
 68                     tx.Commit();
 69                 }
 70                 catch (System.Data.OleDb.OleDbException E)
 71                 {
 72                     tx.Rollback();
 73                     throw new Exception(E.Message);
 74                 }
 75             }
 76         }
 77         /// <summary>
 78         /// 执行带一个存储过程参数的的SQL语句。
 79         /// </summary>
 80         /// <param name="SQLString">SQL语句</param>
 81         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
 82         /// <returns>影响的记录数</returns>
 83         public static int ExecuteSql(string SQLString, string content)
 84         {
 85             using (OleDbConnection connection = new OleDbConnection(connectionString))
 86             {
 87                 OleDbCommand cmd = new OleDbCommand(SQLString, connection);
 88                 System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@content", 
 89 
 90 OleDbType.VarChar);
 91                 myParameter.Value = content;
 92                 cmd.Parameters.Add(myParameter);
 93                 try
 94                 {
 95                     connection.Open();
 96                     int rows = cmd.ExecuteNonQuery();
 97                     return rows;
 98                 }
 99                 catch (System.Data.OleDb.OleDbException E)
100                 {
101                     throw new Exception(E.Message);
102                 }
103                 finally
104                 {
105                     cmd.Dispose();
106                     connection.Close();
107                 }
108             }
109         }
110         /// <summary>
111         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
112         /// </summary>
113         /// <param name="strSQL">SQL语句</param>
114         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
115         /// <returns>影响的记录数</returns>
116         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
117         {
118             using (OleDbConnection connection = new OleDbConnection(connectionString))
119             {
120                 OleDbCommand cmd = new OleDbCommand(strSQL, connection);
121                 System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", OleDbType.Binary);
122                 myParameter.Value = fs;
123                 cmd.Parameters.Add(myParameter);
124                 try
125                 {
126                     connection.Open();
127                     int rows = cmd.ExecuteNonQuery();
128                     return rows;
129                 }
130                 catch (System.Data.OleDb.OleDbException E)
131                 {
132                     throw new Exception(E.Message);
133                 }
134                 finally
135                 {
136                     cmd.Dispose();
137                     connection.Close();
138                 }
139             }
140         }
141 
142         /// <summary>
143         /// 执行一条计算查询结果语句，返回查询结果（object）。
144         /// </summary>
145         /// <param name="SQLString">计算查询结果语句</param>
146         /// <returns>查询结果（object）</returns>
147         public static object GetSingle(string SQLString)
148         {
149             using (OleDbConnection connection = new OleDbConnection(connectionString))
150             {
151                 using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
152                 {
153                     try
154                     {
155                         connection.Open();
156                         object obj = cmd.ExecuteScalar();
157                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
158                         {
159                             return null;
160                         }
161                         else
162                         {
163                             return obj;
164                         }
165                     }
166                     catch (System.Data.OleDb.OleDbException e)
167                     {
168                         connection.Close();
169                         throw new Exception(e.Message);
170                     }
171                 }
172             }
173         }
174         /// <summary>
175         /// 执行查询语句，返回OleDbDataReader
176         /// </summary>
177         /// <param name="strSQL">查询语句</param>
178         /// <returns>OleDbDataReader</returns>
179         public static OleDbDataReader ExecuteReader(string strSQL)
180         {
181             OleDbConnection connection = new OleDbConnection(connectionString);
182             OleDbCommand cmd = new OleDbCommand(strSQL, connection);
183             try
184             {
185                 connection.Open();
186                 OleDbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
187                 return myReader;
188             }
189             catch (System.Data.OleDb.OleDbException e)
190             {
191                 throw new Exception(e.Message);
192             }
193 
194         }
195         /// <summary>
196         /// 执行查询语句，返回DataSet
197         /// </summary>
198         /// <param name="SQLString">查询语句</param>
199         /// <returns>DataSet</returns>
200         public static DataSet Query(string SQLString)
201         {
202             using (OleDbConnection connection = new OleDbConnection(connectionString))
203             {
204                 DataSet ds = new DataSet();
205                 try
206                 {
207                     connection.Open();
208                     OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
209                     command.Fill(ds, "ds");
210                 }
211                 catch (System.Data.OleDb.OleDbException ex)
212                 {
213                     throw new Exception(ex.Message);
214                 }
215                 return ds;
216             }
217         }
218 
219 
220         #endregion
221 
222         #region 执行带参数的SQL语句
223 
224         /// <summary>
225         /// 执行SQL语句，返回影响的记录数
226         /// </summary>
227         /// <param name="SQLString">SQL语句</param>
228         /// <returns>影响的记录数</returns>
229         public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
230         {
231             using (OleDbConnection connection = new OleDbConnection(connectionString))
232             {
233                 using (OleDbCommand cmd = new OleDbCommand())
234                 {
235                     try
236                     {
237                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
238                         int rows = cmd.ExecuteNonQuery();
239                         cmd.Parameters.Clear();
240                         return rows;
241                     }
242                     catch (System.Data.OleDb.OleDbException E)
243                     {
244                         throw new Exception(E.Message);
245                     }
246                 }
247             }
248         }
249 
250 
251         /// <summary>
252         /// 执行多条SQL语句，实现数据库事务。
253         /// </summary>
254         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）</param>
255         public static void ExecuteSqlTran(Hashtable SQLStringList)
256         {
257             using (OleDbConnection conn = new OleDbConnection(connectionString))
258             {
259                 conn.Open();
260                 using (OleDbTransaction trans = conn.BeginTransaction())
261                 {
262                     OleDbCommand cmd = new OleDbCommand();
263                     try
264                     {
265                         //循环
266                         foreach (DictionaryEntry myDE in SQLStringList)
267                         {
268                             string cmdText = myDE.Key.ToString();
269                             OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;
270                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
271                             int val = cmd.ExecuteNonQuery();
272                             cmd.Parameters.Clear();
273                             trans.Commit();
274                         }
275                     }
276                     catch
277                     {
278                         trans.Rollback();
279                         throw;
280                     }
281                 }
282             }
283         }
284 
285 
286         /// <summary>
287         /// 执行一条计算查询结果语句，返回查询结果（object）。
288         /// </summary>
289         /// <param name="SQLString">计算查询结果语句</param>
290         /// <returns>查询结果（object）</returns>
291         public static object GetSingle(string SQLString, params OleDbParameter[] cmdParms)
292         {
293             using (OleDbConnection connection = new OleDbConnection(connectionString))
294             {
295                 using (OleDbCommand cmd = new OleDbCommand())
296                 {
297                     try
298                     {
299                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
300                         object obj = cmd.ExecuteScalar();
301                         cmd.Parameters.Clear();
302                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
303                         {
304                             return null;
305                         }
306                         else
307                         {
308                             return obj;
309                         }
310                     }
311                     catch (System.Data.OleDb.OleDbException e)
312                     {
313                         throw new Exception(e.Message);
314                     }
315                 }
316             }
317         }
318 
319         /// <summary>
320         /// 执行查询语句，返回OleDbDataReader
321         /// </summary>
322         /// <param name="strSQL">查询语句</param>
323         /// <returns>OleDbDataReader</returns>
324         public static OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter[] cmdParms)
325         {
326             OleDbConnection connection = new OleDbConnection(connectionString);
327             OleDbCommand cmd = new OleDbCommand();
328             try
329             {
330                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
331                 OleDbDataReader myReader = cmd.ExecuteReader();
332                 cmd.Parameters.Clear();
333                 return myReader;
334             }
335             catch (System.Data.OleDb.OleDbException e)
336             {
337                 throw new Exception(e.Message);
338             }
339 
340         }
341 
342         /// <summary>
343         /// 执行查询语句，返回DataSet
344         /// </summary>
345         /// <param name="SQLString">查询语句</param>
346         /// <returns>DataSet</returns>
347         public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms)
348         {
349             using (OleDbConnection connection = new OleDbConnection(connectionString))
350             {
351                 OleDbCommand cmd = new OleDbCommand();
352                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
353                 using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
354                 {
355                     DataSet ds = new DataSet();
356                     try
357                     {
358                         da.Fill(ds, "ds");
359                         cmd.Parameters.Clear();
360                     }
361                     catch (System.Data.OleDb.OleDbException ex)
362                     {
363                         throw new Exception(ex.Message);
364                     }
365                     return ds;
366                 }
367             }
368         }
369 
370 
371         private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, 
372 
373 OleDbParameter[] cmdParms)
374         {
375             if (conn.State != ConnectionState.Open)
376                 conn.Open();
377             cmd.Connection = conn;
378             cmd.CommandText = cmdText;
379             if (trans != null)
380                 cmd.Transaction = trans;
381             cmd.CommandType = CommandType.Text;//cmdType;
382             if (cmdParms != null)
383             {
384                 foreach (OleDbParameter parm in cmdParms)
385                     cmd.Parameters.Add(parm);
386             }
387         }
388 
389         #endregion
390 
391         #region 存储过程操作
392 
393         /// <summary>
394         /// 执行存储过程
395         /// </summary>
396         /// <param name="storedProcName">存储过程名</param>
397         /// <param name="parameters">存储过程参数</param>
398         /// <returns>OleDbDataReader</returns>
399         public static OleDbDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
400         {
401             OleDbConnection connection = new OleDbConnection(connectionString);
402             OleDbDataReader returnReader;
403             connection.Open();
404             OleDbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
405             command.CommandType = CommandType.StoredProcedure;
406             returnReader = command.ExecuteReader();
407             return returnReader;
408         }
409 
410 
411         /// <summary>
412         /// 执行存储过程
413         /// </summary>
414         /// <param name="storedProcName">存储过程名</param>
415         /// <param name="parameters">存储过程参数</param>
416         /// <param name="tableName">DataSet结果中的表名</param>
417         /// <returns>DataSet</returns>
418         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
419         {
420             using (OleDbConnection connection = new OleDbConnection(connectionString))
421             {
422                 DataSet dataSet = new DataSet();
423                 connection.Open();
424                 OleDbDataAdapter sqlDA = new OleDbDataAdapter();
425                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
426                 sqlDA.Fill(dataSet, tableName);
427                 connection.Close();
428                 return dataSet;
429             }
430         }
431 
432 
433         /// <summary>
434         /// 构建 OleDbCommand 对象(用来返回一个结果集，而不是一个整数值)
435         /// </summary>
436         /// <param name="connection">数据库连接</param>
437         /// <param name="storedProcName">存储过程名</param>
438         /// <param name="parameters">存储过程参数</param>
439         /// <returns>OleDbCommand</returns>
440         private static OleDbCommand BuildQueryCommand(OleDbConnection connection, string storedProcName, IDataParameter[] 
441 
442 parameters)
443         {
444             OleDbCommand command = new OleDbCommand(storedProcName, connection);
445             command.CommandType = CommandType.StoredProcedure;
446             foreach (OleDbParameter parameter in parameters)
447             {
448                 command.Parameters.Add(parameter);
449             }
450             return command;
451         }
452 
453         /// <summary>
454         /// 执行存储过程，返回影响的行数        
455         /// </summary>
456         /// <param name="storedProcName">存储过程名</param>
457         /// <param name="parameters">存储过程参数</param>
458         /// <param name="rowsAffected">影响的行数</param>
459         /// <returns></returns>
460         public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
461         {
462             using (OleDbConnection connection = new OleDbConnection(connectionString))
463             {
464                 int result;
465                 connection.Open();
466                 OleDbCommand command = BuildIntCommand(connection, storedProcName, parameters);
467                 rowsAffected = command.ExecuteNonQuery();
468                 result = (int)command.Parameters["ReturnValue"].Value;
469                 //Connection.Close();
470                 return result;
471             }
472         }
473 
474         /// <summary>
475         /// 创建 OleDbCommand 对象实例(用来返回一个整数值)    
476         /// </summary>
477         /// <param name="storedProcName">存储过程名</param>
478         /// <param name="parameters">存储过程参数</param>
479         /// <returns>OleDbCommand 对象实例</returns>
480         private static OleDbCommand BuildIntCommand(OleDbConnection connection, string storedProcName, IDataParameter[] 
481 
482 parameters)
483         {
484             OleDbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
485             command.Parameters.Add(new OleDbParameter("ReturnValue",
486                 OleDbType.Integer, 4, ParameterDirection.ReturnValue,
487                 false, 0, 0, string.Empty, DataRowVersion.Default, null));
488             return command;
489         }
490         #endregion
491 
492     }