1    
  2    //该类来自PetShop3.0
  3    
  4    //===============================================================================
  5    // This file is based on the Microsoft Data Access Application Block for .NET
  6    // For more information please go to 
  7    // http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
  8    //===============================================================================
  9
 10    using System;
 11    using System.Configuration;
 12    using System.Data;
 13    using System.Data.SqlClient;
 14    using System.Collections;
 15
 16    namespace MessageBoard7.SQLServerDAL
 17    ...{
 18
 19        /**//// <summary>
 20        /// The SqlHelper class is intended to encapsulate high performance, 
 21        /// scalable best practices for common uses of SqlClient.
 22        /// </summary>
 23        public abstract class SQLHelper 
 24        ...{
 25        
 26            //Database connection strings
 27            public static readonly string CONN_STRING = ConfigurationSettings.AppSettings["SQLConnectionString"];
 28        
 29            // Hashtable to store cached parameters
 30            private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
 31
 32            /**//// <summary>
 33            /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
 34            /// using the provided parameters.
 35            /// </summary>
 36            /// <remarks>
 37            /// e.g.:  
 38            ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 39            /// </remarks>
 40            /// <param name="connectionString">a valid connection string for a SqlConnection</param>
 41            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 42            /// <param name="commandText">the stored procedure name or T-SQL command</param>
 43            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
 44            /// <returns>an int representing the number of rows affected by the command</returns>
 45            public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
 46            ...{
 47
 48                SqlCommand cmd = new SqlCommand();
 49
 50                using (SqlConnection conn = new SqlConnection(connString)) 
 51                ...{
 52                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
 53                    int val = cmd.ExecuteNonQuery();
 54                    cmd.Parameters.Clear();
 55                    return val;
 56                }
 57            }
 58
 59            /**//// <summary>
 60            /// Execute a SqlCommand (that returns no resultset) against an existing database connection 
 61            /// using the provided parameters.
 62            /// </summary>
 63            /// <remarks>
 64            /// e.g.:  
 65            ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 66            /// </remarks>
 67            /// <param name="conn">an existing database connection</param>
 68            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 69            /// <param name="commandText">the stored procedure name or T-SQL command</param>
 70            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
 71            /// <returns>an int representing the number of rows affected by the command</returns>
 72            public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
 73            ...{
 74
 75                SqlCommand cmd = new SqlCommand();
 76
 77                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
 78                int val = cmd.ExecuteNonQuery();
 79                cmd.Parameters.Clear();
 80                return val;
 81            }
 82
 83            /**//// <summary>
 84            /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction 
 85            /// using the provided parameters.
 86            /// </summary>
 87            /// <remarks>
 88            /// e.g.:  
 89            ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 90            /// </remarks>
 91            /// <param name="trans">an existing sql transaction</param>
 92            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 93            /// <param name="commandText">the stored procedure name or T-SQL command</param>
 94            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
 95            /// <returns>an int representing the number of rows affected by the command</returns>
 96            public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
 97            ...{
 98                SqlCommand cmd = new SqlCommand();
 99                PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
100                int val = cmd.ExecuteNonQuery();
101                cmd.Parameters.Clear();
102                return val;
103            }
104
105            /**//// <summary>
106            /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
107            /// using the provided parameters.
108            /// </summary>
109            /// <remarks>
110            /// e.g.:  
111            ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
112            /// </remarks>
113            /// <param name="connectionString">a valid connection string for a SqlConnection</param>
114            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
115            /// <param name="commandText">the stored procedure name or T-SQL command</param>
116            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
117            /// <returns>A SqlDataReader containing the results</returns>
118            public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
119            ...{
120                SqlCommand cmd = new SqlCommand();
121                SqlConnection conn = new SqlConnection(connString);
122
123                // we use a try/catch here because if the method throws an exception we want to 
124                // close the connection throw code, because no datareader will exist, hence the 
125                // commandBehaviour.CloseConnection will not work
126                try 
127                ...{
128                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
129                    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
130                    cmd.Parameters.Clear();
131                    return rdr;
132                }
133                catch 
134                ...{
135                    conn.Close();
136                    throw;
137                }
138            }
139        
140            /**//// <summary>
141            /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 
142            /// using the provided parameters.
143            /// </summary>
144            /// <remarks>
145            /// e.g.:  
146            ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
147            /// </remarks>
148            /// <param name="connectionString">a valid connection string for a SqlConnection</param>
149            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
150            /// <param name="commandText">the stored procedure name or T-SQL command</param>
151            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
152            /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
153            public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
154            ...{
155                SqlCommand cmd = new SqlCommand();
156
157                using (SqlConnection conn = new SqlConnection(connString)) 
158                ...{
159                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
160                    object val = cmd.ExecuteScalar();
161                    cmd.Parameters.Clear();
162                    return val;
163                }
164            }
165
166            /**//// <summary>
167            /// Execute a SqlCommand that returns the first column of the first record against an existing database connection 
168            /// using the provided parameters.
169            /// </summary>
170            /// <remarks>
171            /// e.g.:  
172            ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
173            /// </remarks>
174            /// <param name="conn">an existing database connection</param>
175            /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
176            /// <param name="commandText">the stored procedure name or T-SQL command</param>
177            /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
178            /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
179            public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
180            ...{
181            
182                SqlCommand cmd = new SqlCommand();
183
184                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
185                object val = cmd.ExecuteScalar();
186                cmd.Parameters.Clear();
187                return val;
188            }
189
190            /**//// <summary>
191            /// add parameter array to the cache
192            /// </summary>
193            /// <param name="cacheKey">Key to the parameter cache</param>
194            /// <param name="cmdParms">an array of SqlParamters to be cached</param>
195            public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms) 
196            ...{
197                parmCache[cacheKey] = cmdParms;
198            }
199
200            /**//// <summary>
201            /// Retrieve cached parameters
202            /// </summary>
203            /// <param name="cacheKey">key used to lookup parameters</param>
204            /// <returns>Cached SqlParamters array</returns>
205            public static SqlParameter[] GetCachedParameters(string cacheKey) 
206            ...{
207                SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
208            
209                if (cachedParms == null)
210                    return null;
211            
212                SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
213
214                for (int i = 0, j = cachedParms.Length; i < j; i++)
215                    clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
216
217                return clonedParms;
218            }
219
220            /**//// <summary>
221            /// Prepare a command for execution
222            /// </summary>
223            /// <param name="cmd">SqlCommand object</param>
224            /// <param name="conn">SqlConnection object</param>
225            /// <param name="trans">SqlTransaction object</param>
226            /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
227            /// <param name="cmdText">Command text, e.g. Select * from Products</param>
228            /// <param name="cmdParms">SqlParameters to use in the command</param>
229            private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 
230            ...{
231
232                if (conn.State != ConnectionState.Open)
233                    conn.Open();
234
235                cmd.Connection = conn;
236                cmd.CommandText = cmdText;
237
238                if (trans != null)
239                    cmd.Transaction = trans;
240
241                cmd.CommandType = cmdType;
242
243                if (cmdParms != null) 
244                ...{
245                    foreach (SqlParameter parm in cmdParms)
246                        cmd.Parameters.Add(parm);
247                }
248            }
249        }
250    }