1    
  2    //===============================================================================
  3    // This file is based on the Microsoft Data Access Application Block for .NET
  4    // For more information please go to 
  5    // http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
  6    //===============================================================================
  7
  8using System;
  9using System.Configuration;
 10using System.Data;
 11using System.Data.OleDb;
 12using System.Collections;
 13
 14namespace MessageBoard7.AccessDAL
 15...{
 16
 17    /**//// <summary>
 18    /// The OleDbHelper class is intended to encapsulate high performance, 
 19    /// scalable best practices for common uses of OleDb.
 20    /// </summary>
 21    public abstract class AccessHelper 
 22    ...{
 23        
 24        //Database connection strings
 25        private static readonly string ACCESS_PROVIDER = ConfigurationSettings.AppSettings["AccessProvider"];
 26        private static readonly string ACCESS_DATA_SOURCE = ConfigurationSettings.AppSettings["AccessDataSource"];
 27        public static readonly string CONN_STRING = "Provider=" + ACCESS_PROVIDER + ";" + "Data Source=" + System.Web.HttpContext.Current.Server.MapPath(ACCESS_DATA_SOURCE);
 28        
 29        // Hashtable to store cached parameters
 30        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
 31
 32        /**//// <summary>
 33        /// Execute a OleDbCommand (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 OleDbParameter("@prodid", 24));
 39        /// </remarks>
 40        /// <param name="connectionString">a valid connection string for a OleDbConnection</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 OleDbParamters 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 OleDbParameter[] cmdParms) 
 46        ...{
 47
 48            OleDbCommand cmd = new OleDbCommand();
 49
 50            using (OleDbConnection conn = new OleDbConnection(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 OleDbCommand (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 OleDbParameter("@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 OleDbParamters 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(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) 
 73        ...{
 74
 75            OleDbCommand cmd = new OleDbCommand();
 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 OleDbCommand (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 OleDbParameter("@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 OleDbParamters 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(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) 
 97        ...{
 98            OleDbCommand cmd = new OleDbCommand();
 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 OleDbCommand 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        ///  OleDbDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
112        /// </remarks>
113        /// <param name="connectionString">a valid connection string for a OleDbConnection</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 OleDbParamters used to execute the command</param>
117        /// <returns>A OleDbDataReader containing the results</returns>
118        public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) 
119        ...{
120            OleDbCommand cmd = new OleDbCommand();
121            OleDbConnection conn = new OleDbConnection(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                OleDbDataReader 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 OleDbCommand 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 OleDbParameter("@prodid", 24));
147        /// </remarks>
148        /// <param name="connectionString">a valid connection string for a OleDbConnection</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 OleDbParamters 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 OleDbParameter[] cmdParms) 
154        ...{
155            OleDbCommand cmd = new OleDbCommand();
156
157            using (OleDbConnection conn = new OleDbConnection(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 OleDbCommand 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 OleDbParameter("@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 OleDbParamters 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(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) 
180        ...{
181            
182            OleDbCommand cmd = new OleDbCommand();
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 OleDbParamters to be cached</param>
195        public static void CacheParameters(string cacheKey, params OleDbParameter[] 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 OleDbParamters array</returns>
205        public static OleDbParameter[] GetCachedParameters(string cacheKey) 
206        ...{
207            OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
208            
209            if (cachedParms == null)
210                return null;
211            
212            OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
213
214            for (int i = 0, j = cachedParms.Length; i < j; i++)
215                clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
216
217            return clonedParms;
218        }
219
220        /**//// <summary>
221        /// Prepare a command for execution
222        /// </summary>
223        /// <param name="cmd">OleDbCommand object</param>
224        /// <param name="conn">OleDbConnection object</param>
225        /// <param name="trans">OleDbTransaction 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">OleDbParameters to use in the command</param>
229        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] 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 (OleDbParameter parm in cmdParms)
246                    cmd.Parameters.Add(parm);
247            }
248        }
249    }
250}