1using System;
  2using System.Collections.Generic;
  3
  4using System.Data;
  5using System.Data.OleDb;
  6using System.Configuration;
  7
  8namespace MyShop.AccessDAL
  9...{
 10    /**//// <summary>
 11    /// 数据访问层，提供处理Access数据库的各种方法
 12    /// </summary>
 13    public sealed  class Database 
 14    ...{
 15        public static string connectionString ;
 16        static Database()
 17        ...{
 18            connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ToString();
 19        }
 20
 21        ExecuteNonQuery#region ExecuteNonQuery
 22
 23        /**//// <summary>
 24        /// execute the sql and return the count of line 
 25        /// </summary>
 26        /// <param name="sql"></param>
 27        /// <returns></returns>
 28        public static  int ExecuteNonQuery(string commandText)
 29        ...{
 30            return ExecuteNonQuery(commandText, (OleDbParameter[])null);
 31        }
 32
 33        public static int ExecuteNonQuery(string commandText, OleDbParameter[] parameters)
 34        ...{
 35            CommandType text = CommandType.Text;
 36            return ExecuteNonQuery(text, commandText, parameters);
 37        }
 38
 39 
 40
 41 
 42
 43 
 44
 45        /**//// <summary>
 46        /// Execute a OleDbCommand (that returns no resultset) against the database specified in the connection string 
 47        /// using the provided prams
 48        /// </summary>
 49        /// <Remarks>
 50        /// e.g.:  
 51        ///  int result = ExecuteNonQuery( CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
 52        /// </Remarks>
 53        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 54        /// <param name="commandText">The stored procedure name or T-SQL command</param>
 55        /// <param name="commandprams">An array of SqlParamters used to execute the command</param>
 56        /// <returns>An int representing the number of rows affected by the command</returns>
 57        public static int ExecuteNonQuery( CommandType commandType, string commandText, params OleDbParameter[] commandprams)
 58        ...{
 59            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 60
 61            // Create & open a OleDbConnection, and dispose of it after we are done
 62            using (OleDbConnection connection = new OleDbConnection(connectionString))
 63            ...{
 64                connection.Open();
 65
 66                // Call the overload that takes a connection in place of the connection string
 67                return ExecuteNonQuery(connection, commandType, commandText, commandprams);
 68            }
 69        }
 70
 71
 72        /**//// <summary>
 73        /// Execute a OleDbCommand (that returns no resultset) against the specified OleDbConnection 
 74        /// using the provided prams.
 75        /// </summary>
 76        /// <Remarks>
 77        /// e.g.:  
 78        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
 79        /// </Remarks>
 80        /// <param name="connection">A valid OleDbConnection</param>
 81        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 82        /// <param name="commandText">The stored procedure name or T-SQL command</param>
 83        /// <param name="commandprams">An array of SqlParamters used to execute the command</param>
 84        /// <returns>An int representing the number of rows affected by the command</returns>
 85        public static int ExecuteNonQuery(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandprams)
 86        ...{
 87            if (connection == null) throw new ArgumentNullException("connection");
 88
 89            // Create a command and prepare it for execution
 90            OleDbCommand cmd = new OleDbCommand();
 91            bool mustCloseConnection = false;
 92            PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandprams, out mustCloseConnection);
 93
 94            // Finally, execute the command
 95            int retval = cmd.ExecuteNonQuery();
 96
 97            // Detach the OleDbprams from the command object, so they can be used again
 98            cmd.Parameters.Clear();
 99            if (mustCloseConnection)
100                connection.Close();
101            return retval;
102        }
103
104        public static int ExecuteNonQuery(CommandType commandType, out int intIdentity, string commandText, params OleDbParameter[] commandParameters)
105        ...{
106            return ExecuteNonQuery(connectionString, commandType, out intIdentity, commandText, commandParameters);
107        }
108
109        public static int ExecuteNonQuery(string connectionString, CommandType commandType, out int intIdentity, string commandText, params OleDbParameter[] commandParameters)
110        ...{
111            if ((connectionString == null) || (connectionString.Length == 0))
112            ...{
113                throw new ArgumentNullException("connectionString");
114            }
115            using (OleDbConnection connection = new OleDbConnection(connectionString))
116            ...{
117                connection.Open();
118                return ExecuteNonQuery(connection, commandType, out intIdentity, commandText, commandParameters);
119            }
120        }
121
122 
123        public static int ExecuteNonQuery(OleDbConnection connection, CommandType commandType, out int intIdentity, string commandText, params OleDbParameter[] commandParameters)
124        ...{
125            if (connection == null)
126            ...{
127                throw new ArgumentNullException("connection");
128            }
129            OleDbCommand command = new OleDbCommand();
130            bool mustCloseConnection = false;
131            PrepareCommand(command, connection, null, commandType, commandText, commandParameters, out mustCloseConnection);
132            int num = command.ExecuteNonQuery();
133            command.Parameters.Clear();
134            command.CommandText = "SELECT @@identity";
135            intIdentity = Convert.ToInt32(command.ExecuteScalar());
136            if (mustCloseConnection)
137            ...{
138                connection.Close();
139            }
140            return num;
141        }
142
143
144
145
146 
147 
148 
149
150
151        #endregion
152
153        ExecuteScalar#region ExecuteScalar
154
155        public static string ExecuteScalar(string commandText)
156        ...{
157            CommandType commandType =  CommandType.Text ;
158            object ec = ExecuteScalar(commandType, commandText);
159            if (ec == null)
160            ...{
161                return "";
162            }
163            return ec.ToString();
164        }
165
166        public static object ExecuteScalar( CommandType commandType, string commandText)
167        ...{
168            // Pass through the call providing null for the set of OleDbprams
169            return ExecuteScalar( commandType, commandText, (OleDbParameter[])null);
170        }
171
172
173        /**//// <summary>
174        /// Execute a OleDbCommand (that returns a 1x1 resultset) against the database specified in the connection string 
175        /// using the provided prams.
176        /// </summary>
177        /// <Remarks>
178        /// e.g.:  
179        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new OleDbParameter("@prodid", 24));
180        /// </Remarks>
181        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
182        /// <param name="commandText">The stored procedure name or T-SQL command</param>
183        /// <param name="commandprams">An array of SqlParamters used to execute the command</param>
184        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
185        public static object ExecuteScalar( CommandType commandType, string commandText, params OleDbParameter[] commandprams)
186        ...{
187            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
188            // Create & open a OleDbConnection, and dispose of it after we are done
189            using (OleDbConnection connection = new OleDbConnection(connectionString))
190            ...{
191                connection.Open();
192
193                // Call the overload that takes a connection in place of the connection string
194                return ExecuteScalar(connection, commandType, commandText, commandprams);
195            }
196        }
197
198
199        /**//// <summary>
200        /// Execute a OleDbCommand (that returns a 1x1 resultset) against the specified OleDbConnection 
201        /// using the provided prams.
202        /// </summary>
203        /// <Remarks>
204        /// e.g.:  
205        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new OleDbParameter("@prodid", 24));
206        /// </Remarks>
207        /// <param name="connection">A valid OleDbConnection</param>
208        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
209        /// <param name="commandText">The stored procedure name or T-SQL command</param>
210        /// <param name="commandprams">An array of SqlParamters used to execute the command</param>
211        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
212        public static  object ExecuteScalar(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandprams)
213        ...{
214
215            if (connection == null) throw new ArgumentNullException("connection");
216
217            // Create a command and prepare it for execution
218            OleDbCommand cmd = new OleDbCommand();
219
220            bool mustCloseConnection = false;
221            PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandprams, out mustCloseConnection);
222
223            // Execute the command & return the results
224            object retval = cmd.ExecuteScalar();
225
226            // Detach the OleDbprams from the command object, so they can be used again
227            cmd.Parameters.Clear();
228
229            if (mustCloseConnection)
230                connection.Close();
231
232            return retval;
233        }
234
235        #endregion
236
237        ExecuteDataSet#region ExecuteDataSet
238
239        public static DataSet ExecuteDataSet(string commandText)
240        ...{
241            return ExecuteDataSet(commandText, (OleDbParameter[])null); ;
242        }
243
244        /**//// <summary>
245        /// 
246        /// </summary>
247        /// <param name="sql"></param>
248        /// <param name="prams"></param>
249        /// <returns></returns>
250        public static DataSet ExecuteDataSet(string commandText, OleDbParameter[] prams)
251        ...{
252            if (string.IsNullOrEmpty(commandText) )
253                return null;
254            CommandType commandType = CommandType.Text;
255            return ExecuteDataSet(commandType, commandText, prams);
256        }
257
258        /**//// <summary>
259        /// Execute a OleDbCommand (that returns a resultset) against the database specified in the connection string 
260        /// using the provided prams.
261        /// </summary>
262        /// <Remarks>
263        /// e.g.:  
264        ///  DataSet ds = ExecuteDataSet( CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
265        /// </Remarks>
266        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
267        /// <param name="commandText">The stored procedure name or T-SQL command</param>
268        /// <param name="commandprams">An array of SqlParamters used to execute the command</param>
269        /// <returns>A dataset containing the resultset generated by the command</returns>
270        public static DataSet ExecuteDataSet( CommandType commandType, string commandText, params OleDbParameter[] commandprams)
271        ...{
272            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
273
274            // Create & open a OleDbConnection, and dispose of it after we are done
275            using (OleDbConnection connection = new OleDbConnection(connectionString))
276            ...{
277                connection.Open();
278
279                // Call the overload that takes a connection in place of the connection string
280                return ExecuteDataSet(connection, commandType, commandText, commandprams);
281            }
282        }
283
284
285        /**//// <summary>
286        /// Execute a OleDbCommand (that returns a resultset) against the specified OleDbConnection 
287        /// using the provided prams.
288        /// </summary>
289        /// <Remarks>
290        /// e.g.:  
291        ///  DataSet ds = ExecuteDataSet(conn, CommandType.StoredProcedure, "GetOrders", new OleDbParameter("@prodid", 24));
292        /// </Remarks>
293        /// <param name="connection">A valid OleDbConnection</param>
294        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
295        /// <param name="commandText">The stored procedure name or T-SQL command</param>
296        /// <param name="commandprams">An array of SqlParamters used to execute the command</param>
297        /// <returns>A dataset containing the resultset generated by the command</returns>
298        public static DataSet ExecuteDataSet(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandprams)
299