我把数据库操作类整理了一下,它包含了常用的数据库操作,由三种方式:简单的SQL拼接字符串的形式,SQL语句使用参数的形式和存储过程的形式,每种形式均有五个方法,并且都有事务.,可以直接调用.代码如下:
（本文来源于图老师网站，更多请访问https://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
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
