程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL數據庫操作類

SQL數據庫操作類

編輯:關於SqlServer

我把數據庫操作類整理了一下,它包含了常用的數據庫操作,由三種方式:簡單的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//
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}
499

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved