c# 操作sqlserver
来源:互联网 发布:mac chown usr local 编辑:程序博客网 时间:2024/04/28 20:47
using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Configuration;
7
8 namespace LiTianPing.SQLServerDAL//可以修改成实际项目的命名空间名称
9 {
10 /**////<summary>
11 /// Copyright (C) 2004-2008 LiTianPing
12 /// 数据访问基础类(基于SQLServer)
13 /// 用户可以修改满足自己项目的需要。
14 ///</summary>
15 publicabstract class DbHelperSQL
16 {
17 //数据库连接字符串(web.config来配置)
18 //<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />
19 protected static string connectionString= ConfigurationSettings.AppSettings["ConnectionString"];
20 public DbHelperSQL()
21 {
22 }
23
24 公用方法#region 公用方法
25
26 public staticint GetMaxID(string FieldName,string TableName)
27 {
28 string strsql = "select max("+ FieldName+ ")+1 from" + TableName;
29 object obj = GetSingle(strsql);
30 if (obj ==null)
31 {
32 return 1;
33 }
34 else
35 {
36 return int.Parse(obj.ToString());
37 }
38 }
39 public staticbool Exists(string strSql,params SqlParameter[] cmdParms)
40 {
41 object obj = GetSingle(strSql, cmdParms);
42 int cmdresult;
43 if ((Object.Equals(obj,null)) || (Object.Equals(obj, System.DBNull.Value)))
44 {
45 cmdresult= 0;
46 }
47 else
48 {
49 cmdresult= int.Parse(obj.ToString());
50 }
51 if (cmdresult == 0)
52 {
53 return false;
54 }
55 else
56 {
57 return true;
58 }
59 }
60 #endregion
61
62 执行简单SQL语句#region 执行简单SQL语句
63
64 /**////<summary>
65 /// 执行SQL语句,返回影响的记录数
66 ///</summary>
67 ///<param name="SQLString">SQL语句</param>
68 ///<returns>影响的记录数</returns>
69 public static int ExecuteSql(string SQLString)
70 {
71 using (SqlConnection connection= new SqlConnection(connectionString))
72 {
73 using (SqlCommand cmd= new SqlCommand(SQLString,connection))
74 {
75 try
76 {
77 connection.Open();
78 int rows=cmd.ExecuteNonQuery();
79 return rows;
80 }
81 catch(System.Data.SqlClient.SqlException E)
82 {
83 connection.Close();
84 throw new Exception(E.Message);
85 }
86 }
87 }
88 }
89
90 /**////<summary>
91 /// 执行多条SQL语句,实现数据库事务。
92 ///</summary>
93 ///<param name="SQLStringList">多条SQL语句</param>
94 public static void ExecuteSqlTran(ArrayList SQLStringList)
95 {
96 using (SqlConnection conn= new SqlConnection(connectionString))
97 {
98 conn.Open();
99 SqlCommand cmd= new SqlCommand();
100 cmd.Connection=conn;
101 SqlTransaction tx=conn.BeginTransaction();
102 cmd.Transaction=tx;
103 try
104 {
105 for(int n=0;n<SQLStringList.Count;n++)
106 {
107 string strsql=SQLStringList[n].ToString();
108 if (strsql.Trim().Length>1)
109 {
110 cmd.CommandText=strsql;
111 cmd.ExecuteNonQuery();
112 }
113 }
114 tx.Commit();
115 }
116 catch(System.Data.SqlClient.SqlException E)
117 {
118 tx.Rollback();
119 throw new Exception(E.Message);
120 }
121 }
122 }
123 /**////<summary>
124 /// 执行带一个存储过程参数的的SQL语句。
125 ///</summary>
126 ///<param name="SQLString">SQL语句</param>
127 ///<param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
128 ///<returns>影响的记录数</returns>
129 public static int ExecuteSql(string SQLString,string content)
130 {
131 using (SqlConnection connection= new SqlConnection(connectionString))
132 {
133 SqlCommand cmd= new SqlCommand(SQLString,connection);
134 System.Data.SqlClient.SqlParameter myParameter= new System.Data.SqlClient.SqlParameter ("@content", SqlDbType.NText);
135 myParameter.Value= content ;
136 cmd.Parameters.Add(myParameter);
137 try
138 {
139 connection.Open();
140 int rows=cmd.ExecuteNonQuery();
141 return rows;
142 }
143 catch(System.Data.SqlClient.SqlException E)
144 {
145 throw new Exception(E.Message);
146 }
147 finally
148 {
149 cmd.Dispose();
150 connection.Close();
151 }
152 }
153 }
154 /**////<summary>
155 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
156 ///</summary>
157 ///<param name="strSQL">SQL语句</param>
158 ///<param name="fs">图像字节,数据库的字段类型为image的情况</param>
159 ///<returns>影响的记录数</returns>
160 public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
161 {
162 using (SqlConnection connection= new SqlConnection(connectionString))
163 {
164 SqlCommand cmd= new SqlCommand(strSQL,connection);
165 System.Data.SqlClient.SqlParameter myParameter= new System.Data.SqlClient.SqlParameter ("@fs", SqlDbType.Image);
166 myParameter.Value= fs ;
167 cmd.Parameters.Add(myParameter);
168 try
169 {
170 connection.Open();
171 int rows=cmd.ExecuteNonQuery();
172 return rows;
173 }
174 catch(System.Data.SqlClient.SqlException E)
175 {
176 throw new Exception(E.Message);
177 }
178 finally
179 {
180 cmd.Dispose();
181 connection.Close();
182 }
183 }
184 }
185
186 /**////<summary>
187 /// 执行一条计算查询结果语句,返回查询结果(object)。
188 ///</summary>
189 ///<param name="SQLString">计算查询结果语句</param>
190 ///<returns>查询结果(object)</returns>
191 public static object GetSingle(string SQLString)
192 {
193 using (SqlConnection connection= new SqlConnection(connectionString))
194 {
195 using(SqlCommand cmd= new SqlCommand(SQLString,connection))
196 {
197 try
198 {
199 connection.Open();
200 object obj = cmd.ExecuteScalar();
201 if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
202 {
203 return null;
204 }
205 else
206 {
207 return obj;
208 }
209 }
210 catch(System.Data.SqlClient.SqlException e)
211 {
212 connection.Close();
213 throw new Exception(e.Message);
214 }
215 }
216 }
217 }
218 /**////<summary>
219 /// 执行查询语句,返回SqlDataReader
220 ///</summary>
221 ///<param name="strSQL">查询语句</param>
222 ///<returns>SqlDataReader</returns>
223 public static SqlDataReader ExecuteReader(string strSQL)
224 {
225 SqlConnection connection= new SqlConnection(connectionString);
226 SqlCommand cmd= new SqlCommand(strSQL,connection);
227 try
228 {
229 connection.Open();
230 SqlDataReader myReader= cmd.ExecuteReader();
231 return myReader;
232 }
233 catch(System.Data.SqlClient.SqlException e)
234 {
235 throw new Exception(e.Message);
236 }
237
238 }
239 /**////<summary>
240 /// 执行查询语句,返回DataSet
241 ///</summary>
242 ///<param name="SQLString">查询语句</param>
243 ///<returns>DataSet</returns>
244 public static DataSet Query(string SQLString)
245 {
246 using (SqlConnection connection= new SqlConnection(connectionString))
247 {
248 DataSet ds= new DataSet();
249 try
250 {
251 connection.Open();
252 SqlDataAdapter command= new SqlDataAdapter(SQLString,connection);
253 command.Fill(ds,"ds");
254 }
255 catch(System.Data.SqlClient.SqlException ex)
256 {
257 throw new Exception(ex.Message);
258 }
259 return ds;
260 }
261 }
262
263
264 #endregion
265
266 执行带参数的SQL语句#region 执行带参数的SQL语句
267
268 /**////<summary>
269 /// 执行SQL语句,返回影响的记录数
270 ///</summary>
271 ///<param name="SQLString">SQL语句</param>
272 ///<returns>影响的记录数</returns>
273 public static int ExecuteSql(string SQLString,params SqlParameter[] cmdParms)
274 {
275 using (SqlConnection connection= new SqlConnection(connectionString))
276 {
277 using (SqlCommand cmd= new SqlCommand())
278 {
279 try
280 {
281 PrepareCommand(cmd, connection,null,SQLString, cmdParms);
282 int rows=cmd.ExecuteNonQuery();
283 cmd.Parameters.Clear();
284 return rows;
285 }
286 catch(System.Data.SqlClient.SqlException E)
287 {
288 throw new Exception(E.Message);
289 }
290 }
291 }
292 }
293
294
295 /**////<summary>
296 /// 执行多条SQL语句,实现数据库事务。
297 ///</summary>
298 ///<param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
299 public static void ExecuteSqlTran(Hashtable SQLStringList)
300 {
301 using (SqlConnection conn= new SqlConnection(connectionString))
302 {
303 conn.Open();
304 using (SqlTransaction trans= conn.BeginTransaction())
305 {
306 SqlCommand cmd= new SqlCommand();
307 try
308 {
309 //循环
310 foreach (DictionaryEntry myDEin SQLStringList)
311 {
312 string cmdText=myDE.Key.ToString();
313 SqlParameter[] cmdParms=(SqlParameter[])myDE.Value;
314 PrepareCommand(cmd,conn,trans,cmdText, cmdParms);
315 int val = cmd.ExecuteNonQuery();
316 cmd.Parameters.Clear();
317
318 trans.Commit();
319 }
320 }
321 catch
322 {
323 trans.Rollback();
324 throw;
325 }
326 }
327 }
328 }
329
330
331 /**////<summary>
332 /// 执行一条计算查询结果语句,返回查询结果(object)。
333 ///</summary>
334 ///<param name="SQLString">计算查询结果语句</param>
335 ///<returns>查询结果(object)</returns>
336 public static object GetSingle(string SQLString,params SqlParameter[] cmdParms)
337 {
338 using (SqlConnection connection= new SqlConnection(connectionString))
339 {
340 using (SqlCommand cmd= new SqlCommand())
341 {
342 try
343 {
344 PrepareCommand(cmd, connection,null,SQLString, cmdParms);
345 object obj = cmd.ExecuteScalar();
346 cmd.Parameters.Clear();
347 if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
348 {
349 return null;
350 }
351 else
352 {
353 return obj;
354 }
355 }
356 catch(System.Data.SqlClient.SqlException e)
357 {
358 throw new Exception(e.Message);
359 }
360 }
361 }
362 }
363
364 /**////<summary>
365 /// 执行查询语句,返回SqlDataReader
366 ///</summary>
367 ///<param name="strSQL">查询语句</param>
368 ///<returns>SqlDataReader</returns>
369 public static SqlDataReader ExecuteReader(string SQLString,params SqlParameter[] cmdParms)
370 {
371 SqlConnection connection= new SqlConnection(connectionString);
372 SqlCommand cmd= new SqlCommand();
373 try
374 {
375 PrepareCommand(cmd, connection,null,SQLString, cmdParms);
376 SqlDataReader myReader= cmd.ExecuteReader();
377 cmd.Parameters.Clear();
378 return myReader;
379 }
380 catch(System.Data.SqlClient.SqlException e)
381 {
382 throw new Exception(e.Message);
383 }
384
385 }
386
387 /**////<summary>
388 /// 执行查询语句,返回DataSet
389 ///</summary>
390 ///<param name="SQLString">查询语句</param>
391 ///<returns>DataSet</returns>
392 public static DataSet Query(string SQLString,params SqlParameter[] cmdParms)
393 {
394 using (SqlConnection connection= new SqlConnection(connectionString))
395 {
396 SqlCommand cmd= new SqlCommand();
397 PrepareCommand(cmd, connection,null,SQLString, cmdParms);
398 using( SqlDataAdapter da= new SqlDataAdapter(cmd) )
399 {
400 DataSet ds= new DataSet();
401 try
402 {
403 da.Fill(ds,"ds");
404 cmd.Parameters.Clear();
405 }
406 catch(System.Data.SqlClient.SqlException ex)
407 {
408 throw new Exception(ex.Message);
409 }
410 return ds;
411 }
412 }
413 }
414
415
416 private staticvoid PrepareCommand(SqlCommand cmd,SqlConnection conn,SqlTransaction trans,string cmdText, SqlParameter[] cmdParms)
417 {
418 if (conn.State != ConnectionState.Open)
419 conn.Open();
420 cmd.Connection= conn;
421 cmd.CommandText= cmdText;
422 if (trans !=null)
423 cmd.Transaction= trans;
424 cmd.CommandType= CommandType.Text;//cmdType;
425 if (cmdParms != null)
426 {
427 foreach (SqlParameter parmin cmdParms)
428 cmd.Parameters.Add(parm);
429 }
430 }
431
432 #endregion
433
434 存储过程操作#region 存储过程操作
435
436 /**////<summary>
437 /// 执行存储过程
438 ///</summary>
439 ///<param name="storedProcName">存储过程名</param>
440 ///<param name="parameters">存储过程参数</param>
441 ///<returns>SqlDataReader</returns>
442 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
443 {
444 SqlConnection connection= new SqlConnection(connectionString);
445 SqlDataReader returnReader;
446 connection.Open();
447 SqlCommand command= BuildQueryCommand( connection,storedProcName, parameters );
448 command.CommandType= CommandType.StoredProcedure;
449 returnReader= command.ExecuteReader();
450 return returnReader;
451 }
452
453
454 /**////<summary>
455 /// 执行存储过程
456 ///</summary>
457 ///<param name="storedProcName">存储过程名</param>
458 ///<param name="parameters">存储过程参数</param>
459 ///<param name="tableName">DataSet结果中的表名</param>
460 ///<returns>DataSet</returns>
461 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters,string tableName )
462 {
463 using (SqlConnection connection= new SqlConnection(connectionString))
464 {
465 DataSet dataSet= new DataSet();
466 connection.Open();
467 SqlDataAdapter sqlDA= new SqlDataAdapter();
468 sqlDA.SelectCommand= BuildQueryCommand(connection, storedProcName, parameters );
469 sqlDA.Fill( dataSet, tableName );
470 connection.Close();
471 return dataSet;
472 }
473 }
474
475
476 /**////<summary>
477 /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
478 ///</summary>
479 ///<param name="connection">数据库连接</param>
480 ///<param name="storedProcName">存储过程名</param>
481 ///<param name="parameters">存储过程参数</param>
482 ///<returns>SqlCommand</returns>
483 private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
484 {
485 SqlCommand command= new SqlCommand( storedProcName, connection );
486 command.CommandType= CommandType.StoredProcedure;
487 foreach (SqlParameter parameterin parameters)
488 {
489 command.Parameters.Add( parameter );
490 }
491 return command;
492 }
493
494 /**////<summary>
495 /// 执行存储过程,返回影响的行数
496 ///</summary>
497 ///<param name="storedProcName">存储过程名</param>
498 ///<param name="parameters">存储过程参数</param>
499 ///<param name="rowsAffected">影响的行数</param>
500 ///<returns></returns>
501 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, outint rowsAffected )
502 {
503 using (SqlConnection connection= new SqlConnection(connectionString))
504 {
505 int result;
506 connection.Open();
507 SqlCommand command= BuildIntCommand(connection,storedProcName, parameters );
508 rowsAffected= command.ExecuteNonQuery();
509 result= (int)command.Parameters["ReturnValue"].Value;
510 //Connection.Close();
511 return result;
512 }
513 }
514
515 /**////<summary>
516 /// 创建 SqlCommand 对象实例(用来返回一个整数值)
517 ///</summary>
518 ///<param name="storedProcName">存储过程名</param>
519 ///<param name="parameters">存储过程参数</param>
520 ///<returns>SqlCommand 对象实例</returns>
521 private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
522 {
523 SqlCommand command= BuildQueryCommand(connection,storedProcName, parameters );
524 command.Parameters.Add(new SqlParameter ("ReturnValue",
525 SqlDbType.Int,4,ParameterDirection.ReturnValue,
526 false,0,0,string.Empty,DataRowVersion.Default,null ));
527 return command;
528 }
529 #endregion
530
531 }
532 }
- C#操作SQLServer数据库
- c# 操作sqlserver
- C#操作SqlServer数据库
- c# 操作sqlserver
- C# SqlServer操作类
- c# sqlserver操作类
- C#操作SQLServer数据库
- C#操作SQLserver数据库
- c#操作sqlserver数据库的事务操作
- 通过C#操作SQLServer服务管理器
- C# 数据库的基本操作(sqlserver)
- C#操作图片读取和存储SQLserver
- c# using用法-sql驱动操作sqlserver
- C# 连接操作SqlServer的方法分享
- C# 连接、操作SqlServer数据库--日常笔记
- 【c#第四次作业】c#操作SQLServer数据库
- C# oracle操作类 基于sqlserver操作类修改
- C# sqlserver
- C#中MessageBox用法大全
- python版本OpenCV 2.3 的中文路径
- Windows Server2008中如何关闭internet explorer增强的安全配置?
- iOS UIControl 几个事件的说明
- 快速掌握VMware Workstation(虚拟机)的使用方法
- c# 操作sqlserver
- ZeroMQ timeout a REQ/REP connection
- Linux操作系统中内存buffer和cache的区别
- webGL 投影
- mesh rigidbody drawing
- navigationitem 的titleView
- VC错误处理技术学习:断言(ASSERT)
- 写代码的他妈都是诗人好吧?
- Vtiger CRM 5.3.0 中文版 工作流增强版 视图增强版发布