c# 操作sqlserver

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 public abstract 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 static int 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 static bool 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 myDE in 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 static void 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 parm in 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 parameter in 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, out int 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 }