SQL数据库操作类

elan_luo

elan_luo

2016-02-19 18:17

今天图老师小编给大家展示的是SQL数据库操作类,精心挑选的内容希望大家多多支持、多多分享,喜欢就赶紧get哦!

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

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)

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

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)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    /// returnsSqlDataReader/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    /// returnsDataSet/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    /// returnsSqlDataReader/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    /// returnsDataSet/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
展开更多 50%)
分享

猜你喜欢

SQL数据库操作类

编程语言 网络编程
SQL数据库操作类

使用SQL操作MySQL数据库

编程语言 网络编程
使用SQL操作MySQL数据库

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

C#写的数据库操作类!

电脑网络
C#写的数据库操作类!

操作数据库结构Sql语句

SQLServer
操作数据库结构Sql语句

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

SQL Server数据库导入MySQL数据库体验

MySQL mysql数据库
SQL Server数据库导入MySQL数据库体验

Asp+Sql 对数据库的各种操作

ASP
Asp+Sql 对数据库的各种操作

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

在windows 2003 server中编写asp代码需要注意的地方

在windows 2003 server中编写asp代码需要注意的地方

fontcolor 方法

fontcolor 方法
下拉加载更多内容 ↓