当前文件： AspxNuke/Common/Library/Data/SqlHelper.cs[20K，2009-6-12 11:32:24]，打开代码结构图

		            
  1using System;
  2using System.Collections;
  3using System.Data;
  4using System.Data.Common;
  5using System.Data.SqlClient;
  6using System.Globalization;
  7
  8namespace AspxNuke.Library.Data
  9...{
 10    /**//// <summary>
 11    /// SQL数据库数据库常用操作功能类
 12    /// </summary>
 13    public class SqlHelper : DBHepler
 14    ...{
 15        ExecuteNonQuery#region ExecuteNonQuery
 16
 17        /**//// <summary>
 18        /// 执行一条SQL语句,返回受影响行数
 19        /// </summary>
 20        /// <param name="connectionString">数据库连接字符串</param>
 21        /// <param name="cmdType">CommandText 属性</param>
 22        /// <param name="cmdText">T-SQL命令</param>
 23        /// <param name="cmdParms">Command 的参数</param>
 24        /// <returns>受影响行数</returns>
 25        public override int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
 26                                            params DbParameter[] cmdParms)
 27        ...{
 28            return ExecuteNonQuery(connectionString, cmdType, cmdText, (SqlParameter[]) cmdParms);
 29        }
 30
 31        /**//// <summary>
 32        /// 执行一条SQL语句,返回受影响行数
 33        /// </summary>
 34        /// <param name="connectionString">数据库连接字符串</param>
 35        /// <param name="cmdType">CommandText 属性</param>
 36        /// <param name="cmdText">T-SQL命令</param>
 37        /// <param name="cmdParms">Command 的参数</param>
 38        /// <returns>受影响行数</returns>
 39        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
 40                                          params SqlParameter[] cmdParms)
 41        ...{
 42            using (SqlConnection conn = new SqlConnection(connectionString))
 43            ...{
 44                using (SqlCommand cmd = new SqlCommand())
 45                ...{
 46                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
 47                    int val = cmd.ExecuteNonQuery();
 48                    cmd.Parameters.Clear();
 49                    return val;
 50                }
 51            }
 52        }
 53
 54        /**//// <summary>
 55        /// 执行一条SQL语句,返回受影响行数
 56        /// </summary>
 57        /// <param name="conn">数据库连接对象</param>
 58        /// <param name="cmdType">CommandText 属性</param>
 59        /// <param name="cmdText">T-SQL命令</param>
 60        /// <param name="cmdParms">Command 的参数</param>
 61        /// <returns>受影响行数</returns>
 62        public override int ExecuteNonQuery(DbConnection conn, CommandType cmdType, string cmdText,
 63                                            params DbParameter[] cmdParms)
 64        ...{
 65            return ExecuteNonQuery((SqlConnection) conn, cmdType, cmdText, (SqlParameter[]) cmdParms);
 66        }
 67
 68        /**//// <summary>
 69        /// 执行一条SQL语句,返回受影响行数
 70        /// </summary>
 71        /// <param name="conn">数据库连接对象</param>
 72        /// <param name="cmdType">CommandText 属性</param>
 73        /// <param name="cmdText">T-SQL命令</param>
 74        /// <param name="cmdParms">Command 的参数</param>
 75        /// <returns>受影响行数</returns>
 76        public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText,
 77                                          params SqlParameter[] cmdParms)
 78        ...{
 79            using (SqlCommand cmd = new SqlCommand())
 80            ...{
 81                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
 82                int val = cmd.ExecuteNonQuery();
 83                cmd.Parameters.Clear();
 84                return val;
 85            }
 86        }
 87
 88        /**//// <summary>
 89        /// 执行一条SQL语句,返回受影响行数
 90        /// </summary>
 91        /// <param name="trans">数据库事务对象</param>
 92        /// <param name="cmdType">CommandText 属性</param>
 93        /// <param name="cmdText">T-SQL命令</param>
 94        /// <param name="cmdParms">Command 的参数</param>
 95        /// <returns>受影响行数</returns>
 96        public override int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,
 97                                            params DbParameter[] cmdParms)
 98        ...{
 99            return ExecuteNonQuery((SqlTransaction) trans, cmdType, cmdText, (SqlParameter[]) cmdParms);
100        }
101
102        /**//// <summary>
103        /// 执行一条SQL语句,返回受影响行数
104        /// </summary>
105        /// <param name="trans">数据库事务对象</param>
106        /// <param name="cmdType">CommandText 属性</param>
107        /// <param name="cmdText">T-SQL命令</param>
108        /// <param name="cmdParms">Command 的参数</param>
109        /// <returns>受影响行数</returns>
110        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText,
111                                          params SqlParameter[] cmdParms)
112        ...{
113            if (trans == null)
114            ...{
115                throw new ArgumentNullException("trans");
116            }
117
118            using (SqlCommand cmd = new SqlCommand())
119            ...{
120                PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
121                int val = cmd.ExecuteNonQuery();
122                cmd.Parameters.Clear();
123                return val;
124            }
125        }
126
127        #endregion
128
129        ExecuteScalar#region ExecuteScalar
130
131        /**//// <summary>
132        /// 执行一条SQL语句,返回第一列第一行值
133        /// </summary>
134        /// <param name="connectionString">数据库连接字符串</param>
135        /// <param name="cmdType">CommandText 属性</param>
136        /// <param name="cmdText">T-SQL命令</param>
137        /// <param name="cmdParms">Command 的参数</param>
138        /// <returns>第一列第一行值</returns>
139        public override object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
140                                             params DbParameter[] cmdParms)
141        ...{
142            return ExecuteScalar(connectionString, cmdType, cmdText, (SqlParameter[]) cmdParms);
143        }
144
145        /**//// <summary>
146        /// 执行一条SQL语句,返回第一列第一行值
147        /// </summary>
148        /// <param name="connectionString">数据库连接字符串</param>
149        /// <param name="cmdType">CommandText 属性</param>
150        /// <param name="cmdText">T-SQL命令</param>
151        /// <param name="cmdParms">Command 的参数</param>
152        /// <returns>第一列第一行值</returns>
153        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
154                                           params SqlParameter[] cmdParms)
155        ...{
156            using (SqlConnection conn = new SqlConnection(connectionString))
157            ...{
158                using (SqlCommand cmd = new SqlCommand())
159                ...{
160                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
161                    object val = cmd.ExecuteScalar();
162                    cmd.Parameters.Clear();
163                    return val;
164                }
165            }
166        }
167
168        /**//// <summary>
169        /// 执行一条SQL语句,返回第一列第一行值
170        /// </summary>
171        /// <param name="conn">数据库连接对象</param>
172        /// <param name="cmdType">CommandText 属性</param>
173        /// <param name="cmdText">T-SQL命令</param>
174        /// <param name="cmdParms">Command 的参数</param>
175        /// <returns>第一列第一行值</returns>
176        public override object ExecuteScalar(DbConnection conn, CommandType cmdType, string cmdText,
177                                             params DbParameter[] cmdParms)
178        ...{
179            return ExecuteScalar((SqlConnection) conn, cmdType, cmdText, (SqlParameter[]) cmdParms);
180        }
181
182        /**//// <summary>
183        /// 执行一条SQL语句,返回第一列第一行值
184        /// </summary>
185        /// <param name="conn">数据库连接对象</param>
186        /// <param name="cmdType">CommandText 属性</param>
187        /// <param name="cmdText">T-SQL命令</param>
188        /// <param name="cmdParms">Command 的参数</param>
189        /// <returns>第一列第一行值</returns>
190        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText,
191                                           params SqlParameter[] cmdParms)
192        ...{
193            using (SqlCommand cmd = new SqlCommand())
194            ...{
195                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
196                object val = cmd.ExecuteScalar();
197                cmd.Parameters.Clear();
198                return val;
199            }
200        }
201
202        #endregion
203
204        ExecuteReader#region ExecuteReader
205
206        /**//// <summary>
207        /// 执行一条SQL语句,返回SqlDataReader集
208        /// </summary>
209        /// <param name="connectionString">数据库连接字符串</param>
210        /// <param name="cmdType">CommandText 属性</param>
211        /// <param name="cmdText">T-SQL命令</param>
212        /// <param name="cmdParms">Command 的参数</param>
213        /// <returns>SqlDataReader集</returns>
214        public override IDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
215                                                  params DbParameter[] cmdParms)
216        ...{
217            return ExecuteReader(connectionString, cmdType, cmdText, (SqlParameter[]) cmdParms);
218        }
219
220        /**//// <summary>
221        /// 执行一条SQL语句,返回SqlDataReader集
222        /// </summary>
223        /// <param name="connectionString">数据库连接字符串</param>
224        /// <param name="cmdType">CommandText 属性</param>
225        /// <param name="cmdText">T-SQL命令</param>
226        /// <param name="cmdParms">Command 的参数</param>
227        /// <returns>SqlDataReader集</returns>
228        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
229                                                  params SqlParameter[] cmdParms)
230        ...{
231            SqlCommand cmd = new SqlCommand();
232            SqlConnection conn = new SqlConnection(connectionString);
233
234            try
235            ...{
236                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
237                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
238                cmd.Parameters.Clear();
239                return rdr;
240            }
241            catch
242            ...{
243                conn.Close();
244                throw;
245            }
246        }
247
248        #endregion
249
250        ExecuteDataSet#region ExecuteDataSet
251
252        /**//// <summary>
253        /// 执行一条SQL语句,返回DataSet集
254        /// </summary>
255        /// <param name="connectionString">数据库连接字符串</param>
256        /// <param name="cmdType">CommandText 属性</param>
257        /// <param name="cmdText">T-SQL命令</param>
258        /// <param name="tableName">数据表名</param>
259        /// <param name="cmdParms">Command 的参数</param>
260        /// <returns>DataSet集</returns>
261        public override DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText,
262                                               string tableName, params DbParameter[] cmdParms)
263        ...{
264            return ExecuteDataSet(connectionString, cmdType, cmdText, tableName, (SqlParameter[]) cmdParms);
265        }
266
267        /**//// <summary>
268        /// 执行一条SQL语句,返回DataSet集
269        /// </summary>
270        /// <param name="connectionString">数据库连接字符串</param>
271        /// <param name="cmdType">CommandText 属性</param>
272        /// <param name="cmdText">T-SQL命令</param>
273        /// <param name="tableName">数据表名</param>
274        /// <param name="cmdParms">Command 的参数</param>
275        /// <returns>DataSet集</returns>
276        public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText,
277                                             string tableName, params SqlParameter[] cmdParms)
278        ...{
279            using (SqlConnection conn = new SqlConnection(connectionString))
280            ...{
281                using (SqlCommand cmd = new SqlCommand())
282                ...{
283                    DataSet ds = new DataSet();
284                    ds.Locale = CultureInfo.InvariantCulture;
285                    try
286                    ...{
287                        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
288                        SqlDataAdapter sqlDA = new SqlDataAdapter();
289                        sqlDA.SelectCommand = cmd;
290                        sqlDA.Fill(ds, tableName);
291                    }
292                    catch
293                    ...{
294                        conn.Close();
295                        throw;
296                    }
297                    return ds;
298                }
299            }
300        }
301
302        /**////// <summary>
303        ///// 执行一条SQL语句,返回DataSet集
304        ///// </summary>
305        ///// <param name="conn">数据库连接对象</param>
306        ///// <param name="cmd">DbConnection 对象</param>
307        ///// <param name="ds">DataSet 对象</param>
308        ///// <param name="tableName">数据表</param>
309        ///// <returns>DataSet集</returns>
310        //public static DataSet ExecuteDataSet(DbConnection conn, DbCommand cmd, DataSet ds, string tableName)
311        //{
312        //    return ExecuteDataSet((SqlConnection)conn, (SqlCommand)cmd, ds, tableName);
313        //}
314        /**////// <summary>
315        ///// 执行一条SQL语句,返回DataSet集
316        ///// </summary>
317        ///// <param name="conn">数据库连接对象</param>
318        ///// <param name="cmd">SqlCommand 对象</param>
319        ///// <param name="ds">DataSet 对象</param>
320        ///// <param name="tableName">数据表</param>
321        ///// <returns>DataSet集</returns>
322        //public static DataSet ExecuteDataSet(SqlConnection conn, SqlCommand cmd, DataSet ds, string tableName)
323        //{
324        //    SqlDataAdapter sqlDA = new SqlDataAdapter();
325        //    try
326        //    {
327        //        PrepareCommand(cmd, conn, null, cmd.CommandType, cmd.CommandText, null);
328        //        sqlDA.SelectCommand = cmd;
329        //        sqlDA.Fill(ds, tableName);
330        //    }
331        //    catch
332        //    {
333        //        conn.Close();
334        //        throw;
335        //    }
336        //    return ds;
337        //}
338
339        #endregion
340
341        Transaction#region Transaction
342
343        /**////// <summary>
344        ///// 开始事务
345        ///// </summary>
346        ///// <param name="conn">DbConnection 对象。</param>
347        ///// <returns>DbTransaction事务对象</returns>
348        //public override DbTransaction BeginTransaction(DbConnection conn)
349        //{
350        //    return BeginTransaction((SqlConnection)conn);
351        //}
352        /**////// <summary>
353        ///// 开始事务
354        ///// </summary>
355        ///// <param name="conn">DbConnection 对象。</param>
356        ///// <returns>DbTransaction事务对象</returns>
357        //public static DbTransaction BeginTransaction(SqlConnection conn)
358        //{
359        //    SqlTransaction tran = conn.BeginTransaction();
360        //    return tran;
361        //}
362
363        /**////// <summary>
364        ///// 从挂起状态回滚事务。
365        ///// </summary>
366        ///// <param name="tran">DbTransaction事务对象</param>
367        //public override void RollbackTransaction(DbTransaction tran)
368        //{
369        //    RollbackTransaction((SqlTransaction)tran);
370        //}
371        /**////// <summary>
372        ///// 从挂起状态回滚事务。
373        ///// </summary>
374        ///// <param name="tran">DbTransaction事务对象</param>
375        //public static void RollbackTransaction(SqlTransaction tran)
376        //{
377        //    tran.Rollback();
378        //}
379
380
381        /**////// <summary>
382        ///// 提交数据库事务。
383        ///// </summary>
384        ///// <param name="tran">DbTransaction事务对象</param>
385        //public override void CommitTransaction(DbTransaction tran)
386        //{
387        //    CommitTransaction((SqlTransaction)tran);
388        //}
389        /**////// <summary>
390        ///// 提交数据库事务。
391        ///// </summary>
392        ///// <param name="tran">DbTransaction事务对象</param>
393        //public static void CommitTransaction(SqlTransaction tran)
394        //{
395        //    tran.Commit();
396        //}
397
398        #endregion
399
400        Connection#region Connection
401
402        /**//// <summary>
403        /// 建立数据库连接
404        /// </summary>
405        /// <returns>DbConnection 对象</returns>
406        public override DbConnection CreateConnection()
407        ...{
408            return CreateSqlConnection(ConnectionString);
409        }
410
411        /**//// <summary>
412        /// 建立数据库连接
413        /// </summary>
414        /// <param name="connectionString">数据库连接字符串</param>
415        /// <returns>SqlConnection 对象</returns>
416        public static SqlConnection CreateSqlConnection(string connectionString)
417        ...{
418            SqlConnection newConnection = new SqlConnection(connectionString);
419            return newConnection;
420        }
421
422        /**//// <summary>
423        /// 打开数据库连接
424        /// </summary>
425        /// <returns>DbConnection 对象</returns>
426        public override DbConnection OpenConnection()
427        ...{
428            return OpenSqlConnection(ConnectionString);
429        }
430
431        /**//// <summary>
432        /// 打开数据库连接
433        /// </summary>
434        /// <returns>SqlConnection 对象</returns>
435        public static SqlConnection OpenSqlConnection(string connectionString)
436        ...{
437            SqlConnection conn = null;
438            try
439            ...{
440                conn = CreateSqlConnection(connectionString);
441                conn.Open();
442            }
443            catch
444            ...{
445                if (conn != null)
446                    conn.Close();
447
448                throw;
449            }
450
451            return conn;
452        }
453
454        #endregion
455
456        Parameters#region Parameters
457
458        /**//// <summary>
459        /// add parameter array to the cache
460        /// </summary>
461        /// <param name="cacheKey">Key to the parameter cache</param>
462        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
463        public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
464        ...{
465            parmCache[cacheKey] = cmdParms;
466        }
467
468        /**//// <summary>
469        /// Retrieve cached parameters
470        /// </summary>
471        /// <param name="cacheKey">key used to lookup parameters</param>
472        /// <returns>Cached SqlParamters array</returns>
473        public static SqlParameter[] GetCachedParameters(string cacheKey)
474        ...{
475            SqlParameter[] cachedParms = (SqlParameter[]) parmCache[cacheKey];
476
477            if (cachedParms == null)
478                return null;
479
480            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
481
482            for (int i = 0, j = cachedParms.Length; i < j; i++)
483                clonedParms[i] = (SqlParameter) ((ICloneable) cachedParms[i]).Clone();
484
485            return clonedParms;
486        }
487
488        #endregion
489
490        PrepareCommand#region PrepareCommand
491
492        /**//// <summary>
493        /// Prepare a command for execution
494        /// </summary>
495        /// <param name="cmd">SqlCommand object</param>
496        /// <param name="conn">SqlConnection object</param>
497        /// <param name="trans">SqlTransaction object</param>
498        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
499        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
500        /// <param name="cmdParms">SqlParameters to use in the command</param>
501        public static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType,
502                                          string cmdText, SqlParameter[] cmdParms)
503        ...{
504            if (cmd == null)
505            ...{
506                throw new ArgumentNullException("cmd");
507            }
508
509            if (conn == null)
510            ...{
511                throw new ArgumentNullException("conn");
512            }
513
514            if (conn.State != ConnectionState.Open)
515                conn.Open();
516
517            cmd.Connection = conn;
518            cmd.CommandText = cmdText;
519
520            if (trans != null)
521                cmd.Transaction = trans;
522
523            cmd.CommandType = cmdType;
524
525            if (cmdParms != null)
526            ...{
527                foreach (SqlParameter parm in cmdParms)
528                    cmd.Parameters.Add(parm);
529            }
530        }
531
532        #endregion
533
534        private#region private
535
536        // Hashtable to store cached parameters
537        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
538
539        #endregion
540    }
541}