当前位置:Gxlcms > mysql > SQL数据库操作类_MySQL

SQL数据库操作类_MySQL

时间:2021-07-01 10:21:17 帮助过:28人阅读

我把数据库操作类整理了一下,它包含了常用的数据库操作,由三种方式:简单的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  /**////


25  /// 数据访问基础类(基于SQLServer)
26  ///

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    /**////
38    /// 执行SQL语句,返回影响的记录数
39    ///

40    /// SQL语句
41    /// 影响的记录数
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    /// 执行多条SQL语句,实现数据库事务。
65    ///

66    /// 多条SQL语句    
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    /// 执行一条计算查询结果语句,返回查询结果(object)。
98    ///

99    /// 计算查询结果语句
100    /// 查询结果(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    /// 执行查询语句,返回SqlDataReader
130    ///

131    /// 查询语句
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    /// 执行查询语句,返回DataSet
151    ///

152    /// 查询语句
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    执行带参数的SQL语句#region 执行带参数的SQL语句
178
179    /**////
180    /// 执行SQL语句,返回影响的记录数
181    ///

182    /// SQL语句
183    /// 影响的记录数
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    /**////
207    /// 执行多条SQL语句,实现数据库事务。
208    ///

209    /// SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])
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    /**////
242    /// 执行一条计算查询结果语句,返回查询结果(object),返回首行首列的值;
243    ///

244    /// 计算查询结果语句
245    /// 查询结果(object)
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    /**////
275    /// 执行查询语句,返回SqlDataReader
276    ///

277    /// 查询语句
278    /// SqlDataReader
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    /**////
298    /// 执行查询语句,返回DataSet
299    ///

300    /// 查询语句
301    /// DataSet
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    /**////
346    /// 执行存储过程;
347    ///

348    /// 存储过程名
349    /// 所需要的参数
350    /// 返回受影响的行数
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    /// 执行存储过程,返回首行首列的值
364    ///

365    /// 存储过程名
366    /// 存储过程参数
367    /// 返回首行首列的值
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    /// 执行存储过程
394    ///

395    /// 存储过程名
396    /// 存储过程参数
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    /// 执行存储过程
410    ///

411    /// 存储过程名
412    /// 存储过程参数
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    /// 执行多个存储过程,实现数据库事务。
431    ///

432    /// 存储过程的哈希表(key是该语句的DbParameter[],value为存储过程语句)
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    /**////
474    /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
475    ///

476    /// 数据库连接
477    /// 存储过程名
478    /// 存储过程参数
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

人气教程排行