为ASP.NET封装的SQL数据库访问类
1 using System;
2 using System.Configuration;
3 using System.Data;
4 using System.Data.SqlClient;
5 using System.Collections;
6
7 namespace MyCorporation.DepartMent.DataBase
8 {
9 /// <summary>
10 /// 通用数据库类
11 /// </summary>
12 public class DataBase
13 {
14
15 private string ConnStr = null;
16
17 public DataBase()
18 {
19 ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
20 }
21 public DataBase(string Str)
22 {
23 try
24 {
25 this.ConnStr = Str;
26
27 }
28 catch (Exception ex)
29 {
30 throw ex;
31 }
32 }
33
34 /// <summary>
35 /// 返回connection对象
36 /// </summary>
37 /// <returns></returns>
38 public SqlConnection ReturnConn()
39 {
40 SqlConnection Conn = new SqlConnection(ConnStr);
41 Conn.Open();
42 return Conn;
43 }
44 public void Dispose(SqlConnection Conn)
45 {
46 if (Conn != null)
47 {
48 Conn.Close();
49 Conn.Dispose();
50 }
51 GC.Collect();
52 }
53 /// <summary>
54 /// 运行SQL语句
55 /// </summary>
56 /// <param name="SQL"></param>
57 public void RunProc(string SQL)
58 {
59 SqlConnection Conn;
60 Conn = new SqlConnection(ConnStr);
61 Conn.Open();
62 SqlCommand Cmd;
63 Cmd = CreateCmd(SQL, Conn);
64 try
65 {
66 Cmd.ExecuteNonQuery();
67 }
68 catch
69 {
70 throw new Exception(SQL);
71 }
72 Dispose(Conn);
73 return;
74 }
75
76 /// <summary>
77 /// 运行SQL语句返回DataReader
78 /// </summary>
79 /// <param name="SQL"></param>
80 /// <returns>SqlDataReader对象.</returns>
81 public SqlDataReader RunProcGetReader(string SQL)
82 {
83 SqlConnection Conn;
84 Conn = new SqlConnection(ConnStr);
85 Conn.Open();
86 SqlCommand Cmd;
87 Cmd = CreateCmd(SQL, Conn);
88 SqlDataReader Dr;
89 try
90 {
91 Dr = Cmd.ExecuteReader(CommandBehavior.Default);
92 }
93 catch
94 {
95 throw new Exception(SQL);
96 }
97 //Dispose(Conn);
98 return Dr;
99 }
100
101 /// <summary>
102 /// 生成Command对象
103 /// </summary>
104 /// <param name="SQL"></param>
105 /// <param name="Conn"></param>
106 /// <returns></returns>
107 public SqlCommand CreateCmd(string SQL, SqlConnection Conn)
108 {
109 SqlCommand Cmd;
110 Cmd = new SqlCommand(SQL, Conn);
111 return Cmd;
112 }
113
114 /// <summary>
115 /// 生成Command对象
116 /// </summary>
117 /// <param name="SQL"></param>
118 /// <returns></returns>
119 public SqlCommand CreateCmd(string SQL)
120 {
121 SqlConnection Conn;
122 Conn = new SqlConnection(ConnStr);
123 Conn.Open();
124 SqlCommand Cmd;
125 Cmd = new SqlCommand(SQL, Conn);
126 return Cmd;
127 }
128
129 /// <summary>
130 /// 返回adapter对象
131 /// </summary>
132 /// <param name="SQL"></param>
133 /// <param name="Conn"></param>
134 /// <returns></returns>
135 public SqlDataAdapter CreateDa(string SQL)
136 {
137 SqlConnection Conn;
138 Conn = new SqlConnection(ConnStr);
139 Conn.Open();
140 SqlDataAdapter Da;
141 Da = new SqlDataAdapter(SQL, Conn);
142 return Da;
143 }
144
145 /// <summary>
146 /// 运行SQL语句,返回DataSet对象
147 /// </summary>
148 /// <param name="procName">SQL语句</param>
149 /// <param name="prams">DataSet对象</param>
150 public DataSet RunProc(string SQL, DataSet Ds)
151 {
152 SqlConnection Conn;
153 Conn = new SqlConnection(ConnStr);
154 Conn.Open();
155 SqlDataAdapter Da;
156 //Da = CreateDa(SQL, Conn);
157 Da = new SqlDataAdapter(SQL, Conn);
158 try
159 {
160 Da.Fill(Ds);
161 }
162 catch (Exception Err)
163 {
164 throw Err;
165 }
166 Dispose(Conn);
167 return Ds;
168 }
169
170 /// <summary>
171 /// 运行SQL语句,返回DataSet对象
172 /// </summary>
173 /// <param name="procName">SQL语句</param>
174 /// <param name="prams">DataSet对象</param>
175 /// <param name="dataReader">表名</param>
176 public DataSet RunProc(string SQL, DataSet Ds, string tablename)
177 {
178 SqlConnection Conn;
179 Conn = new SqlConnection(ConnStr);
180 Conn.Open();
181 SqlDataAdapter Da;
182 Da = CreateDa(SQL);
183 try
184 {
185 Da.Fill(Ds, tablename);
186 }
187 catch (Exception Ex)
188 {
189 throw Ex;
190 }
191 Dispose(Conn);
192 return Ds;
193 }
194
195 /// <summary>
196 /// 运行SQL语句,返回DataSet对象
197 /// </summary>
198 /// <param name="procName">SQL语句</param>
199 /// <param name="prams">DataSet对象</param>
200 /// <param name="dataReader">表名</param>
201 public DataSet RunProc(string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename)
202 {
203 SqlConnection Conn;
204 Conn = new SqlConnection(ConnStr);
205 Conn.Open();
206 SqlDataAdapter Da;
207 Da = CreateDa(SQL);
208 try
209 {
210 Da.Fill(Ds, StartIndex, PageSize, tablename);
211 }
212 catch (Exception Ex)
213 {
214 throw Ex;
215 }
216 Dispose(Conn);
217 return Ds;
218 }
219
220 /// <summary>
221 /// 检验是否存在数据
222 /// </summary>
223 /// <returns></returns>
224 public bool ExistDate(string SQL)
225 {
226 SqlConnection Conn;
227 Conn = new SqlConnection(ConnStr);
228 Conn.Open();
229 SqlDataReader Dr;
230 Dr = CreateCmd(SQL, Conn).ExecuteReader();
231 if (Dr.Read())
232 {
233 Dispose(Conn);
234 return true;
235 }
236 else
237 {
238 Dispose(Conn);
239 return false;
240 }
241 }
242
243 /// <summary>
244 /// 返回SQL语句执行结果的第一行第一列
245 /// </summary>
246 /// <returns>字符串</returns>
247 public string ReturnValue(string SQL)
248 {
249 SqlConnection Conn;
250 Conn = new SqlConnection(ConnStr);
251 Conn.Open();
252 string result;
253 SqlDataReader Dr;
254 try
255 {
256 Dr = CreateCmd(SQL, Conn).ExecuteReader();
257 if (Dr.Read())
258 {
259 result = Dr[0].ToString();
260 Dr.Close();
261 }
262 else
263 {
264 result = "";
265 Dr.Close();
266 }
267 }
268 catch
269 {
270 throw new Exception(SQL);
271 }
272 Dispose(Conn);
273 return result;
274 }
275
276 /// <summary>
277 /// 返回SQL语句第一列,第ColumnI列,
278 /// </summary>
279 /// <returns>字符串</returns>
280 public string ReturnValue(string SQL, int ColumnI)
281 {
282 SqlConnection Conn;
283 Conn = new SqlConnection(ConnStr);
284 Conn.Open();
285 string result;
286 SqlDataReader Dr;
287 try
288 {
289 Dr = CreateCmd(SQL, Conn).ExecuteReader();
290 }
291 catch
292 {
293 throw new Exception(SQL);
294 }
295 if (Dr.Read())
296 {
297 result = Dr[ColumnI].ToString();
298 }
299 else
300 {
301 result = "";
302 }
303 Dr.Close();
304 Dispose(Conn);
305 return result;
306 }
307
308 /// <summary>
309 /// 生成一个存储过程使用的sqlcommand.
310 /// </summary>
311 /// <param name="procName">存储过程名.</param>
312 /// <param name="prams">存储过程入参数组.</param>
313 /// <returns>sqlcommand对象.</returns>
314 public SqlCommand CreateCmd(string procName, SqlParameter[] prams)
315 {
316 SqlConnection Conn;
317 Conn = new SqlConnection(ConnStr);
318 Conn.Open();
319 SqlCommand Cmd = new SqlCommand(procName, Conn);
320 Cmd.CommandType = CommandType.StoredProcedure;
321 if (prams != null)
322 {
323 foreach (SqlParameter parameter in prams)
324 {
325 if (parameter != null)
326 {
327 Cmd.Parameters.Add(parameter);
328 }
329 }
330 }
331 return Cmd;
332 }
333
334 /// <summary>
335 /// 为存储过程生成一个SqlCommand对象
336 /// </summary>
337 /// <param name="procName">存储过程名</param>
338 /// <param name="prams">存储过程参数</param>
339 /// <returns>SqlCommand对象</returns>
340 private SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlDataReader Dr)
341 {
342 SqlConnection Conn;
343 Conn = new SqlConnection(ConnStr);
344 Conn.Open();
345 SqlCommand Cmd = new SqlCommand(procName, Conn);
346 Cmd.CommandType = CommandType.StoredProcedure;
347 if (prams != null)
348 {
349 foreach (SqlParameter parameter in prams)
350 Cmd.Parameters.Add(parameter);
351 }
352 Cmd.Parameters.Add(
353 new SqlParameter("ReturnValue", SqlDbType.Int, 4,
354 ParameterDirection.ReturnValue, false, 0, 0,
355 string.Empty, DataRowVersion.Default, null));
356
357 return Cmd;
358 }
359
360 /// <summary>
361 /// 运行存储过程,返回.
362 /// </summary>
363 /// <param name="procName">存储过程名</param>
364 /// <param name="prams">存储过程参数</param>
365 /// <param name="dataReader">SqlDataReader对象</param>
366 public void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
367 {
368
369 SqlCommand Cmd = CreateCmd(procName, prams, Dr);
370 Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
371 return;
372 }
373
374 /// <summary>
375 /// 运行存储过程,返回.
376 /// </summary>
377 /// <param name="procName">存储过程名</param>
378 /// <param name="prams">存储过程参数</param>
379 public string RunProc(string procName, SqlParameter[] prams)
380 {
381 SqlDataReader Dr;
382 SqlCommand Cmd = CreateCmd(procName, prams);
383 Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
384 if (Dr.Read())
385 {
386 return Dr.GetValue(0).ToString();
387 }
388 else
389 {
390 return "";
391 }
392 }
393
394 /// <summary>
395 /// 运行存储过程,返回dataset.
396 /// </summary>
397 /// <param name="procName">存储过程名.</param>
398 /// <param name="prams">存储过程入参数组.</param>
399 /// <returns>dataset对象.</returns>
400 public DataSet RunProc(string procName, SqlParameter[] prams, DataSet Ds)
401 {
402 SqlCommand Cmd = CreateCmd(procName, prams);
403 SqlDataAdapter Da = new SqlDataAdapter(Cmd);
404 try
405 {
406 Da.Fill(Ds);
407 }
408 catch (Exception Ex)
409 {
410 throw Ex;
411 }
412 return Ds;
413 }
414
415 }
416 }
2 using System.Configuration;
3 using System.Data;
4 using System.Data.SqlClient;
5 using System.Collections;
6
7 namespace MyCorporation.DepartMent.DataBase
8 {
9 /// <summary>
10 /// 通用数据库类
11 /// </summary>
12 public class DataBase
13 {
14
15 private string ConnStr = null;
16
17 public DataBase()
18 {
19 ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
20 }
21 public DataBase(string Str)
22 {
23 try
24 {
25 this.ConnStr = Str;
26
27 }
28 catch (Exception ex)
29 {
30 throw ex;
31 }
32 }
33
34 /// <summary>
35 /// 返回connection对象
36 /// </summary>
37 /// <returns></returns>
38 public SqlConnection ReturnConn()
39 {
40 SqlConnection Conn = new SqlConnection(ConnStr);
41 Conn.Open();
42 return Conn;
43 }
44 public void Dispose(SqlConnection Conn)
45 {
46 if (Conn != null)
47 {
48 Conn.Close();
49 Conn.Dispose();
50 }
51 GC.Collect();
52 }
53 /// <summary>
54 /// 运行SQL语句
55 /// </summary>
56 /// <param name="SQL"></param>
57 public void RunProc(string SQL)
58 {
59 SqlConnection Conn;
60 Conn = new SqlConnection(ConnStr);
61 Conn.Open();
62 SqlCommand Cmd;
63 Cmd = CreateCmd(SQL, Conn);
64 try
65 {
66 Cmd.ExecuteNonQuery();
67 }
68 catch
69 {
70 throw new Exception(SQL);
71 }
72 Dispose(Conn);
73 return;
74 }
75
76 /// <summary>
77 /// 运行SQL语句返回DataReader
78 /// </summary>
79 /// <param name="SQL"></param>
80 /// <returns>SqlDataReader对象.</returns>
81 public SqlDataReader RunProcGetReader(string SQL)
82 {
83 SqlConnection Conn;
84 Conn = new SqlConnection(ConnStr);
85 Conn.Open();
86 SqlCommand Cmd;
87 Cmd = CreateCmd(SQL, Conn);
88 SqlDataReader Dr;
89 try
90 {
91 Dr = Cmd.ExecuteReader(CommandBehavior.Default);
92 }
93 catch
94 {
95 throw new Exception(SQL);
96 }
97 //Dispose(Conn);
98 return Dr;
99 }
100
101 /// <summary>
102 /// 生成Command对象
103 /// </summary>
104 /// <param name="SQL"></param>
105 /// <param name="Conn"></param>
106 /// <returns></returns>
107 public SqlCommand CreateCmd(string SQL, SqlConnection Conn)
108 {
109 SqlCommand Cmd;
110 Cmd = new SqlCommand(SQL, Conn);
111 return Cmd;
112 }
113
114 /// <summary>
115 /// 生成Command对象
116 /// </summary>
117 /// <param name="SQL"></param>
118 /// <returns></returns>
119 public SqlCommand CreateCmd(string SQL)
120 {
121 SqlConnection Conn;
122 Conn = new SqlConnection(ConnStr);
123 Conn.Open();
124 SqlCommand Cmd;
125 Cmd = new SqlCommand(SQL, Conn);
126 return Cmd;
127 }
128
129 /// <summary>
130 /// 返回adapter对象
131 /// </summary>
132 /// <param name="SQL"></param>
133 /// <param name="Conn"></param>
134 /// <returns></returns>
135 public SqlDataAdapter CreateDa(string SQL)
136 {
137 SqlConnection Conn;
138 Conn = new SqlConnection(ConnStr);
139 Conn.Open();
140 SqlDataAdapter Da;
141 Da = new SqlDataAdapter(SQL, Conn);
142 return Da;
143 }
144
145 /// <summary>
146 /// 运行SQL语句,返回DataSet对象
147 /// </summary>
148 /// <param name="procName">SQL语句</param>
149 /// <param name="prams">DataSet对象</param>
150 public DataSet RunProc(string SQL, DataSet Ds)
151 {
152 SqlConnection Conn;
153 Conn = new SqlConnection(ConnStr);
154 Conn.Open();
155 SqlDataAdapter Da;
156 //Da = CreateDa(SQL, Conn);
157 Da = new SqlDataAdapter(SQL, Conn);
158 try
159 {
160 Da.Fill(Ds);
161 }
162 catch (Exception Err)
163 {
164 throw Err;
165 }
166 Dispose(Conn);
167 return Ds;
168 }
169
170 /// <summary>
171 /// 运行SQL语句,返回DataSet对象
172 /// </summary>
173 /// <param name="procName">SQL语句</param>
174 /// <param name="prams">DataSet对象</param>
175 /// <param name="dataReader">表名</param>
176 public DataSet RunProc(string SQL, DataSet Ds, string tablename)
177 {
178 SqlConnection Conn;
179 Conn = new SqlConnection(ConnStr);
180 Conn.Open();
181 SqlDataAdapter Da;
182 Da = CreateDa(SQL);
183 try
184 {
185 Da.Fill(Ds, tablename);
186 }
187 catch (Exception Ex)
188 {
189 throw Ex;
190 }
191 Dispose(Conn);
192 return Ds;
193 }
194
195 /// <summary>
196 /// 运行SQL语句,返回DataSet对象
197 /// </summary>
198 /// <param name="procName">SQL语句</param>
199 /// <param name="prams">DataSet对象</param>
200 /// <param name="dataReader">表名</param>
201 public DataSet RunProc(string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename)
202 {
203 SqlConnection Conn;
204 Conn = new SqlConnection(ConnStr);
205 Conn.Open();
206 SqlDataAdapter Da;
207 Da = CreateDa(SQL);
208 try
209 {
210 Da.Fill(Ds, StartIndex, PageSize, tablename);
211 }
212 catch (Exception Ex)
213 {
214 throw Ex;
215 }
216 Dispose(Conn);
217 return Ds;
218 }
219
220 /// <summary>
221 /// 检验是否存在数据
222 /// </summary>
223 /// <returns></returns>
224 public bool ExistDate(string SQL)
225 {
226 SqlConnection Conn;
227 Conn = new SqlConnection(ConnStr);
228 Conn.Open();
229 SqlDataReader Dr;
230 Dr = CreateCmd(SQL, Conn).ExecuteReader();
231 if (Dr.Read())
232 {
233 Dispose(Conn);
234 return true;
235 }
236 else
237 {
238 Dispose(Conn);
239 return false;
240 }
241 }
242
243 /// <summary>
244 /// 返回SQL语句执行结果的第一行第一列
245 /// </summary>
246 /// <returns>字符串</returns>
247 public string ReturnValue(string SQL)
248 {
249 SqlConnection Conn;
250 Conn = new SqlConnection(ConnStr);
251 Conn.Open();
252 string result;
253 SqlDataReader Dr;
254 try
255 {
256 Dr = CreateCmd(SQL, Conn).ExecuteReader();
257 if (Dr.Read())
258 {
259 result = Dr[0].ToString();
260 Dr.Close();
261 }
262 else
263 {
264 result = "";
265 Dr.Close();
266 }
267 }
268 catch
269 {
270 throw new Exception(SQL);
271 }
272 Dispose(Conn);
273 return result;
274 }
275
276 /// <summary>
277 /// 返回SQL语句第一列,第ColumnI列,
278 /// </summary>
279 /// <returns>字符串</returns>
280 public string ReturnValue(string SQL, int ColumnI)
281 {
282 SqlConnection Conn;
283 Conn = new SqlConnection(ConnStr);
284 Conn.Open();
285 string result;
286 SqlDataReader Dr;
287 try
288 {
289 Dr = CreateCmd(SQL, Conn).ExecuteReader();
290 }
291 catch
292 {
293 throw new Exception(SQL);
294 }
295 if (Dr.Read())
296 {
297 result = Dr[ColumnI].ToString();
298 }
299 else
300 {
301 result = "";
302 }
303 Dr.Close();
304 Dispose(Conn);
305 return result;
306 }
307
308 /// <summary>
309 /// 生成一个存储过程使用的sqlcommand.
310 /// </summary>
311 /// <param name="procName">存储过程名.</param>
312 /// <param name="prams">存储过程入参数组.</param>
313 /// <returns>sqlcommand对象.</returns>
314 public SqlCommand CreateCmd(string procName, SqlParameter[] prams)
315 {
316 SqlConnection Conn;
317 Conn = new SqlConnection(ConnStr);
318 Conn.Open();
319 SqlCommand Cmd = new SqlCommand(procName, Conn);
320 Cmd.CommandType = CommandType.StoredProcedure;
321 if (prams != null)
322 {
323 foreach (SqlParameter parameter in prams)
324 {
325 if (parameter != null)
326 {
327 Cmd.Parameters.Add(parameter);
328 }
329 }
330 }
331 return Cmd;
332 }
333
334 /// <summary>
335 /// 为存储过程生成一个SqlCommand对象
336 /// </summary>
337 /// <param name="procName">存储过程名</param>
338 /// <param name="prams">存储过程参数</param>
339 /// <returns>SqlCommand对象</returns>
340 private SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlDataReader Dr)
341 {
342 SqlConnection Conn;
343 Conn = new SqlConnection(ConnStr);
344 Conn.Open();
345 SqlCommand Cmd = new SqlCommand(procName, Conn);
346 Cmd.CommandType = CommandType.StoredProcedure;
347 if (prams != null)
348 {
349 foreach (SqlParameter parameter in prams)
350 Cmd.Parameters.Add(parameter);
351 }
352 Cmd.Parameters.Add(
353 new SqlParameter("ReturnValue", SqlDbType.Int, 4,
354 ParameterDirection.ReturnValue, false, 0, 0,
355 string.Empty, DataRowVersion.Default, null));
356
357 return Cmd;
358 }
359
360 /// <summary>
361 /// 运行存储过程,返回.
362 /// </summary>
363 /// <param name="procName">存储过程名</param>
364 /// <param name="prams">存储过程参数</param>
365 /// <param name="dataReader">SqlDataReader对象</param>
366 public void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
367 {
368
369 SqlCommand Cmd = CreateCmd(procName, prams, Dr);
370 Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
371 return;
372 }
373
374 /// <summary>
375 /// 运行存储过程,返回.
376 /// </summary>
377 /// <param name="procName">存储过程名</param>
378 /// <param name="prams">存储过程参数</param>
379 public string RunProc(string procName, SqlParameter[] prams)
380 {
381 SqlDataReader Dr;
382 SqlCommand Cmd = CreateCmd(procName, prams);
383 Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
384 if (Dr.Read())
385 {
386 return Dr.GetValue(0).ToString();
387 }
388 else
389 {
390 return "";
391 }
392 }
393
394 /// <summary>
395 /// 运行存储过程,返回dataset.
396 /// </summary>
397 /// <param name="procName">存储过程名.</param>
398 /// <param name="prams">存储过程入参数组.</param>
399 /// <returns>dataset对象.</returns>
400 public DataSet RunProc(string procName, SqlParameter[] prams, DataSet Ds)
401 {
402 SqlCommand Cmd = CreateCmd(procName, prams);
403 SqlDataAdapter Da = new SqlDataAdapter(Cmd);
404 try
405 {
406 Da.Fill(Ds);
407 }
408 catch (Exception Ex)
409 {
410 throw Ex;
411 }
412 return Ds;
413 }
414
415 }
416 }
