SQL数据库操作类

SQL数据库操作类

我把数据库操作类整理了一下,它包含了常用的数据库操作,由三种方式:简单的SQL拼接字符串的形式,SQL语句使用参数的形式和存储过程的形式,每种形式均有五个方法,并且都有事务.,可以直接调用.代码如下:

  1//======================================================================
  2//
  3//        Copyright (C) 2007-2008 三月软件工作室    
  4//        All rights reserved
  5//
  6//        filename :SQLDataBase
  7//        description :
  8//
  9//        created by 侯垒 at  04/14/2008 18:33:32
 10//        http://houleixx.cnblogs.com
 11//
 12//======================================================================
 13
 14using System;
 15using System.Collections;
 16using System.Collections.Specialized;
 17using System.Data;
 18using System.Data.SqlClient;
 19using System.Configuration;
 20using System.Data.Common;
 21
 22namespace SQLDataBase
 23{
 24    /**//// <summary>
 25    /// 数据访问基础类(基于SQLServer)
 26    /// </summary>
 27    class SQLDataBase
 28    {
 29        protected static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 30        public SQLDataBase()
 31        {
 32
 33        }
 34
 35        执行简单SQL语句#region  执行简单SQL语句
 36
 37        /**//// <summary>
 38        /// 执行SQL语句，返回影响的记录数
 39        /// </summary>
 40        /// <param name="SQLString">SQL语句</param>
 41        /// <returns>影响的记录数</returns>
 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        /**//// <summary>
 64        /// 执行多条SQL语句，实现数据库事务。
 65        /// </summary>
 66        /// <param name="SQLStringList">多条SQL语句</param>        
 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        /**//// <summary>
 97        /// 执行一条计算查询结果语句，返回查询结果（object）。
 98        /// </summary>
 99        /// <param name="SQLString">计算查询结果语句</param>
100        /// <returns>查询结果（object）</returns>
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        /**//// <summary>
129        /// 执行查询语句，返回SqlDataReader
130        /// </summary>
131        /// <param name="strSQL">查询语句</param>
132        /// <returns>SqlDataReader</returns>
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        /**//// <summary>
150        /// 执行查询语句，返回DataSet
151        /// </summary>
152        /// <param name="SQLString">查询语句</param>
153        /// <returns>DataSet</returns>
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        执行带参数的SQL语句#region 执行带参数的SQL语句
178
179        /**//// <summary>
180        /// 执行SQL语句，返回影响的记录数
181        /// </summary>
182        /// <param name="SQLString">SQL语句</param>
183        /// <returns>影响的记录数</returns>
184        public int ExecuteSql(string SQLString, DbParameter[] cmdParms)
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, cmdParms);
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        /**//// <summary>
207        /// 执行多条SQL语句，实现数据库事务。
208        /// </summary>
209        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
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                        //循环
221                        foreach (DictionaryEntry myDE in SQLStringList)
222                        {
223                            string cmdText = myDE.Key.ToString();
224                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
225                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
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        /**//// <summary>
242        /// 执行一条计算查询结果语句，返回查询结果（object）,返回首行首列的值;
243        /// </summary>
244        /// <param name="SQLString">计算查询结果语句</param>
245        /// <returns>查询结果（object）</returns>
246        public object GetSingle(string SQLString, DbParameter[] cmdParms)
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, cmdParms);
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        /**//// <summary>
275        /// 执行查询语句，返回SqlDataReader
276        /// </summary>
277        /// <param name="strSQL">查询语句</param>
278        /// <returns>SqlDataReader</returns>
279        public DbDataReader ExecuteReader(string SQLString, DbParameter[] cmdParms)
280        {
281            SqlConnection connection = new SqlConnection(connectionString);
282            SqlCommand cmd = new SqlCommand();
283            try
284            {
285                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
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        /**//// <summary>
298        /// 执行查询语句，返回DataSet
299        /// </summary>
300        /// <param name="SQLString">查询语句</param>
301        /// <returns>DataSet</returns>
302        public DataSet GetDataSet(string SQLString, DbParameter[] cmdParms)
303        {
304            using (SqlConnection connection = new SqlConnection(connectionString))
305            {
306                SqlCommand cmd = new SqlCommand();
307                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
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 cmdText, DbParameter[] cmdParms)
327        {
328            if (conn.State != ConnectionState.Open)
329                conn.Open();
330            cmd.Connection = conn;
331            cmd.CommandText = cmdText;
332            if (trans != null)
333                cmd.Transaction = trans;
334            cmd.CommandType = CommandType.Text;//cmdType;
335            if (cmdParms != null)
336            {
337                foreach (SqlParameter parm in cmdParms)
338                    cmd.Parameters.Add(parm);
339            }
340        }
341
342        #endregion
343
344        存储过程操作#region 存储过程操作
345        /**//// <summary>
346        /// 执行存储过程;
347        /// </summary>
348        /// <param name="storeProcName">存储过程名</param>
349        /// <param name="parameters">所需要的参数</param>
350        /// <returns>返回受影响的行数</returns>
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        /**//// <summary>
363        /// 执行存储过程,返回首行首列的值
364        /// </summary>
365        /// <param name="storeProcName">存储过程名</param>
366        /// <param name="parameters">存储过程参数</param>
367        /// <returns>返回首行首列的值</returns>
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        /**//// <summary>
393        /// 执行存储过程
394        /// </summary>
395        /// <param name="storedProcName">存储过程名</param>
396        /// <param name="parameters">存储过程参数</param>
397        /// <returns>SqlDataReader</returns>
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        /**//// <summary>
409        /// 执行存储过程
410        /// </summary>
411        /// <param name="storedProcName">存储过程名</param>
412        /// <param name="parameters">存储过程参数</param>
413        /// <returns>DataSet</returns>
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        /**//// <summary>
430        /// 执行多个存储过程，实现数据库事务。
431        /// </summary>
432        /// <param name="SQLStringList">存储过程的哈希表（key是该语句的DbParameter[],value为存储过程语句）</param>
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                        //循环
444                        foreach (DictionaryEntry myDE in SQLStringList)
445                        {
446                            cmd.Connection = connection;
447                            string storeName = myDE.Value.ToString();
448                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Key;
449
450                            cmd.Transaction = trans;
451                            cmd.CommandText = storeName;
452                            cmd.CommandType = CommandType.StoredProcedure;
453                            if (cmdParms != null)
454                            {
455                                foreach (SqlParameter parameter in cmdParms)
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        /**//// <summary>
474        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
475        /// </summary>
476        /// <param name="connection">数据库连接</param>
477        /// <param name="storedProcName">存储过程名</param>
478        /// <param name="parameters">存储过程参数</param>
479        /// <returns>SqlCommand</returns>
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}