对数据库Sql Server常用操作类库SQLHelper

不管什么系统都免不了对数据访问操作,现在公布出来一些对数据库SQL Server操作的
基本类库,具体代码如下:

  1//===============================================================================
  2// This file is based on the Microsoft Data Access Application Block for .NET
  3// For more information please go to 
  4// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
  5//===============================================================================
  6
  7using System;
  8using System.Configuration;
  9using System.Data;
 10using System.Data.SqlClient;
 11using System.Collections;
 12
 13namespace EBUILDS.PM.SQLServerDAL {
 14
 15    /**//// <summary>
 16    /// The SqlHelper class is intended to encapsulate high performance, 
 17    /// scalable best practices for common uses of SqlClient.
 18    /// </summary>
 19    public abstract class SQLHelper {
 20        
 21        //Database connection strings
 22        public static readonly string CONN_STRING_NON_DTC ="server=chyli;database=pmdb;uid=sa;pwd=ebuilds";// ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString1"]);
 23//        public static readonly string CONN_STRING_DTC_INV = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString2"]);        
 24//        public static readonly string CONN_STRING_DTC_ORDERS = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString3"]);
 25        
 26        // Hashtable to store cached parameters
 27        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
 28
 29
 30        /**//**************************************
 31            ** 描述：对SQLHelper进行招展,满足特殊要求
 32            **        执行SQL语句，不需要参数
 33            **************************************/
 34//        public static string ExecuteScalar(string connString,SqlCommand cmd) 
 35//        {
 36//
 37//        
 38//
 39//            using (SqlConnection conn = new SqlConnection(connString)) 
 40//            {
 41//                if (conn.State != ConnectionState.Open)
 42//                {    
 43//                    conn.Open();
 44//                }
 45//                cmd.Connection = conn;
 46//                string val = Convert.ToString(cmd.ExecuteScalar());
 47//                cmd.Dispose();
 48//                return val;
 49//            }
 50//        }
 51        public static string ExecuteScalar(string connString,string cmdText) 
 52        {
 53
 54            SqlCommand cmd = new SqlCommand(cmdText);
 55
 56            using (SqlConnection conn = new SqlConnection(connString)) 
 57            {
 58                if (conn.State != ConnectionState.Open)
 59                {    
 60                    conn.Open();
 61                }
 62                cmd.Connection = conn;
 63                string val = Convert.ToString(cmd.ExecuteScalar());
 64                cmd.Dispose();
 65                return val;
 66            }
 67        }
 68        /**//**************************************
 69        ** 描述：对SQLHelper进行招展,满足特殊要求
 70        **        执行SQL语句，不需要参数
 71        **************************************/
 72    
 73        public static int ExecuteNonQuery(string connString,string cmdText) 
 74        {
 75
 76            SqlCommand cmd = new SqlCommand(cmdText);
 77
 78            using (SqlConnection conn = new SqlConnection(connString)) 
 79            {
 80                if (conn.State != ConnectionState.Open)
 81                {    
 82                    conn.Open();
 83                }
 84                cmd.Connection = conn;
 85                int val = cmd.ExecuteNonQuery();
 86                cmd.Dispose();
 87                return val;
 88            }
 89        }
 90        
 91        /**//**************************************
 92        ** 描述：对SQLHelper进行招展,满足特殊要求
 93        **        执行SQL语句，不需要参数
 94        **************************************/
 95        public static int ExecuteNonQuery(string connString,string[] cmdText) 
 96        {
 97            int val = 0;
 98            SqlConnection myConnection = new SqlConnection(connString);
 99            myConnection.Open();
100
101            SqlCommand myCommand = myConnection.CreateCommand();
102            SqlTransaction myTrans;
103
104            // Start a local transaction
105            myTrans = myConnection.BeginTransaction();
106            // Must assign both transaction object and connection
107            // to Command object for a pending local transaction
108            myCommand.Connection = myConnection;
109            myCommand.Transaction = myTrans;
110
111            try
112            {
113                foreach( string strSql in cmdText)
114                {
115                    myCommand.CommandText = strSql;
116                    val = myCommand.ExecuteNonQuery();
117                }    
118                myTrans.Commit();
119                
120            }
121            catch
122            {
123                myTrans.Rollback();
124                
125            }
126            return val;
127        }
128
129        /**//// <summary>
130        /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
131        /// using the provided parameters.
132        /// </summary>
133        /// <remarks>
134        /// e.g.:  
135        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
136        /// </remarks>
137        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
138        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
139        /// <param name="commandText">the stored procedure name or T-SQL command</param>
140        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
141        /// <returns>an int representing the number of rows affected by the command</returns>
142        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
143
144            SqlCommand cmd = new SqlCommand();
145
146            using (SqlConnection conn = new SqlConnection(connString)) {
147                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
148                int val = cmd.ExecuteNonQuery();
149                cmd.Parameters.Clear();
150                return val;
151            }
152        }
153
154        /**//// <summary>
155        /// Execute a SqlCommand (that returns no resultset) against an existing database connection 
156        /// using the provided parameters.
157        /// </summary>
158        /// <remarks>
159        /// e.g.:  
160        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
161        /// </remarks>
162        /// <param name="conn">an existing database connection</param>
163        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
164        /// <param name="commandText">the stored procedure name or T-SQL command</param>
165        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
166        /// <returns>an int representing the number of rows affected by the command</returns>
167        public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
168
169            SqlCommand cmd = new SqlCommand();
170
171            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
172            int val = cmd.ExecuteNonQuery();
173            cmd.Parameters.Clear();
174            return val;
175        }
176
177        /**//// <summary>
178        /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction 
179        /// using the provided parameters.
180        /// </summary>
181        /// <remarks>
182        /// e.g.:  
183        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
184        /// </remarks>
185        /// <param name="trans">an existing sql transaction</param>
186        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
187        /// <param name="commandText">the stored procedure name or T-SQL command</param>
188        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
189        /// <returns>an int representing the number of rows affected by the command</returns>
190        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
191            SqlCommand cmd = new SqlCommand();
192            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
193            int val = cmd.ExecuteNonQuery();
194            cmd.Parameters.Clear();
195            return val;
196        }
197
198        /**//// <summary>
199        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
200        /// using the provided parameters.
201        /// </summary>
202        /// <remarks>
203        /// e.g.:  
204        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
205        /// </remarks>
206        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
207        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
208        /// <param name="commandText">the stored procedure name or T-SQL command</param>
209        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
210        /// <returns>A SqlDataReader containing the results</returns>
211        public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
212            SqlCommand cmd = new SqlCommand();
213            SqlConnection conn = new SqlConnection(connString);
214
215            // we use a try/catch here because if the method throws an exception we want to 
216            // close the connection throw code, because no datareader will exist, hence the 
217            // commandBehaviour.CloseConnection will not work
218            try {
219                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
220                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
221                cmd.Parameters.Clear();
222                return rdr;
223            }catch {
224                conn.Close();
225                throw;
226            }
227        }
228        
229        /**//// <summary>
230        /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 
231        /// using the provided parameters.
232        /// </summary>
233        /// <remarks>
234        /// e.g.:  
235        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
236        /// </remarks>
237        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
238        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
239        /// <param name="commandText">the stored procedure name or T-SQL command</param>
240        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
241        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
242        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
243            SqlCommand cmd = new SqlCommand();
244
245            using (SqlConnection conn = new SqlConnection(connString)) {
246                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
247                object val = cmd.ExecuteScalar();
248                cmd.Parameters.Clear();
249                return val;
250            }
251        }
252        public static SqlDataReader ExecuteScalar(string connString, SqlCommand cmd) 
253        {
254            SqlConnection conn = new SqlConnection(connString);
255
256            // we use a try/catch here because if the method throws an exception we want to 
257            // close the connection throw code, because no datareader will exist, hence the 
258            // commandBehaviour.CloseConnection will not work
259            try 
260            {
261                cmd.Connection=conn;
262                conn.Open();
263                    SqlDataReader rdr = cmd.ExecuteReader();
264                return rdr;
265            }
266            catch 
267            {
268                conn.Close();
269                throw;
270            }
271            finally
272            {
273                
274            }
275        }
276
277        /**//// <summary>
278        /// Execute a SqlCommand that returns the first column of the first record against an existing database connection 
279        /// using the provided parameters.
280        /// </summary>
281        /// <remarks>
282        /// e.g.:  
283        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
284        /// </remarks>
285        /// <param name="conn">an existing database connection</param>
286        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
287        /// <param name="commandText">the stored procedure name or T-SQL command</param>
288        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
289        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
290        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
291            
292            SqlCommand cmd = new SqlCommand();
293
294            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
295            object val = cmd.ExecuteScalar();
296            cmd.Parameters.Clear();
297            return val;
298        }
299
300        /**//// <summary>
301        /// add parameter array to the cache
302        /// </summary>
303        /// <param name="cacheKey">Key to the parameter cache</param>
304        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
305        public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms) {
306            parmCache[cacheKey] = cmdParms;
307        }
308
309        /**//// <summary>
310        /// Retrieve cached parameters
311        /// </summary>
312        /// <param name="cacheKey">key used to lookup parameters</param>
313        /// <returns>Cached SqlParamters array</returns>
314        public static SqlParameter[] GetCachedParameters(string cacheKey) {
315            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
316            
317            if (cachedParms == null)
318                return null;
319            
320            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
321
322            for (int i = 0, j = cachedParms.Length; i < j; i++)
323                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
324
325            return clonedParms;
326        }
327
328        /**//// <summary>
329        /// Prepare a command for execution
330        /// </summary>
331        /// <param name="cmd">SqlCommand object</param>
332        /// <param name="conn">SqlConnection object</param>
333        /// <param name="trans">SqlTransaction object</param>
334        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
335        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
336        /// <param name="cmdParms">SqlParameters to use in the command</param>
337        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) {
338
339            if (conn.State != ConnectionState.Open)
340                conn.Open();
341
342            cmd.Connection = conn;
343            cmd.CommandText = cmdText;
344
345            if (trans != null)
346                cmd.Transaction = trans;
347
348            cmd.CommandType = cmdType;
349
350            if (cmdParms != null) {
351                foreach (SqlParameter parm in cmdParms)
352                    cmd.Parameters.Add(parm);
353            }
354        }
355        public static DataSet GetDs(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) //获取有输入参数的sqlcommand返回数据集
356        {
357            SqlCommand cmd = new SqlCommand();
358
359            using (SqlConnection conn = new SqlConnection(connString)) 
360            {
361                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
362                SqlDataAdapter adp=new SqlDataAdapter(cmd);
363                DataSet ds=new DataSet();
364                adp.Fill(ds);
365                cmd.Parameters.Clear();
366                return ds;
367            }
368        }
369        public static DataSet GetDs(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
370        {
371            SqlCommand cmd = new SqlCommand();
372
373            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
374            SqlDataAdapter adp=new SqlDataAdapter(cmd);
375            DataSet ds=new DataSet();
376            adp.Fill(ds);
377            cmd.Parameters.Clear();
378            return ds;
379        }
380        public static DataSet GetDs(SqlConnection conn, CommandType cmdType, string cmdText) //获取无输入参数的sqlcommand返回数据集
381        {
382            SqlCommand cmd = new SqlCommand();
383            SqlDataAdapter adp=new SqlDataAdapter();
384            adp.SelectCommand.CommandType=cmdType;
385            adp.SelectCommand.Connection=conn;
386            adp.SelectCommand.CommandText=cmdText;
387            DataSet ds=new DataSet();
388            adp.Fill(ds);
389            return ds;
390        }
391        public static DataSet GetDs(string connString, CommandType cmdType, string cmdText) //
392        {
393            
394            SqlCommand cmd = new SqlCommand();
395            SqlDataAdapter adp=new SqlDataAdapter();
396            adp.SelectCommand.CommandType=cmdType;
397            adp.SelectCommand.Connection=new SqlConnection(connString);
398            adp.SelectCommand.CommandText=cmdText;
399            DataSet ds=new DataSet();
400            adp.Fill(ds);
401            return ds;
402        }
403        public static Boolean ExecuteNonQuery (string connString, SqlCommand cmd)// 执行无返回结果的sqlcommand语句
404        {
405            try
406            {
407                cmd.Connection=new SqlConnection(connString);
408                cmd.Connection.Open();
409                cmd.ExecuteNonQuery();
410                return true;
411            }
412            catch
413            {
414                return false;
415            }
416            finally
417            {
418                cmd.Connection.Close();
419            }
420        }
421        public static DataSet GetDs(SqlConnection conn, SqlCommand cmd) //通过事情sqlcommand语句获取数据集
422        {
423            SqlDataAdapter adp=new SqlDataAdapter();
424            adp.SelectCommand=cmd;
425            adp.SelectCommand.Connection=conn;
426            DataSet ds=new DataSet();
427            adp.Fill(ds);
428            return ds;
429        }
430        public static DataSet GetDs(string connString, SqlCommand cmd) //通过事情sqlcommand语句获取数据集
431        {
432            
433            SqlDataAdapter adp=new SqlDataAdapter();
434            adp.SelectCommand=cmd;
435            adp.SelectCommand.Connection=new SqlConnection(connString);
436            DataSet ds=new DataSet();
437            adp.Fill(ds);
438            return ds;
439        }
440        public static DataSet GetDs(SqlConnection conn,  string sqlstr) //通过sql语句获取数据集
441        {
442            
443            SqlDataAdapter adp=new SqlDataAdapter(sqlstr,conn);
444            DataSet ds=new DataSet();
445            adp.Fill(ds);
446            return ds;
447        }
448        public static DataSet GetDs(string connString,  string sqlstr) //通过sql语句获取数据集
449        {
450            
451            SqlDataAdapter adp=new SqlDataAdapter(sqlstr,new SqlConnection(connString));
452            DataSet ds=new DataSet();
453            adp.Fill(ds);
454            return ds;
455        }
456        /**//// <summary>
457        /// 传入输入参数
458        /// </summary>
459        /// <param name="ParamName">存储过程名称</param>
460        /// <param name="DbType">参数类型</param></param>
461        /// <param name="Size">参数大小</param>
462        /// <param name="Value">参数值</param>
463        /// <returns>新的 parameter 对象</returns>
464        public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) 
465        {
466            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
467        }        
468
469        /**//// <summary>
470        /// 传入返回值参数
471        /// </summary>
472        /// <param name="ParamName">存储过程名称</param>
473        /// <param name="DbType">参数类型</param>
474        /// <param name="Size">参数大小</param>
475        /// <returns>新的 parameter 对象</returns>
476        public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) 
477        {
478            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
479        }        
480        /**//// <summary>
481        /// 传入返回值参数
482        /// </summary>
483        /// <param name="ParamName">存储过程名称</param>
484        /// <param name="DbType">参数类型</param>
485        /// <param name="Size">参数大小</param>
486        /// <returns>新的 parameter 对象</returns>
487        public static SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size) 
488        {
489            return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
490        }    
491    
492        /**//// <summary>
493        /// 生成存储过程参数
494        /// </summary>
495        /// <param name="ParamName">存储过程名称</param>
496        /// <param name="DbType">参数类型</param>
497        /// <param name="Size">参数大小</param>
498        /// <param name="Direction">参数方向</param>
499        /// <param name="Value">参数值</param>
500        /// <returns>新的 parameter 对象</returns>
501        public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 
502        {
503            SqlParameter param;
504
505            if(Size > 0)
506                param = new SqlParameter(ParamName, DbType, Size);
507            else
508                param = new SqlParameter(ParamName, DbType);
509
510            param.Direction = Direction;
511            if (!(Direction == ParameterDirection.Output && Value == null))
512                param.Value = Value;
513
514            return param;
515        }
516
517    }
518}