<转>C#数据库封装类

 1using System;
  2using System.Collections.Generic;
  3using System.Text;
  4using System.Data;
  5using System.Configuration;
  6using System.Data.Common;
  7using System.Data.SqlClient;
  8using System.Data.OleDb;
  9using System.Data.Odbc;
 10using System.Data.OracleClient;
 11using System.IO;
 12
 13namespace BinaryIntellect.DataAccess
 14{
 15    public class DatabaseHelper:IDisposable
 16    {
 17        private string strConnectionString;
 18        private DbConnection objConnection;
 19        private DbCommand objCommand;
 20        private DbProviderFactory objFactory = null;
 21        private bool boolHandleErrors;
 22        private string strLastError;
 23        private bool boolLogError;
 24        private string strLogFile;
 25
 26        public DatabaseHelper(string connectionstring,Providers provider)
 27        {
 28            strConnectionString = connectionstring;
 29            switch (provider)
 30            {
 31                case Providers.SqlServer:
 32                    objFactory = SqlClientFactory.Instance;
 33                    break;
 34                case Providers.OleDb:
 35                    objFactory = OleDbFactory.Instance;
 36                    break;
 37                case Providers.Oracle:
 38                    objFactory = OracleClientFactory.Instance;
 39                    break;
 40                case Providers.ODBC:
 41                    objFactory = OdbcFactory.Instance;
 42                    break;
 43                case Providers.ConfigDefined:
 44                    string providername=ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
 45                    switch (providername)
 46                    {
 47                        case "System.Data.SqlClient":
 48                            objFactory = SqlClientFactory.Instance;
 49                            break;
 50                        case "System.Data.OleDb":
 51                            objFactory = OleDbFactory.Instance;
 52                            break;
 53                        case "System.Data.OracleClient":
 54                            objFactory = OracleClientFactory.Instance;
 55                            break;
 56                        case "System.Data.Odbc":
 57                            objFactory = OdbcFactory.Instance;
 58                            break;
 59                    }
 60                    break;
 61
 62            }
 63            objConnection = objFactory.CreateConnection();
 64            objCommand = objFactory.CreateCommand();
 65
 66            objConnection.ConnectionString = strConnectionString;
 67            objCommand.Connection = objConnection;
 68        }
 69
 70        public DatabaseHelper(Providers provider):this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,provider)
 71        {
 72        }
 73
 74        public DatabaseHelper(string connectionstring): this(connectionstring, Providers.SqlServer)
 75        {
 76        }
 77
 78        public DatabaseHelper():this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,Providers.ConfigDefined)
 79        {
 80        }
 81
 82        public bool HandleErrors
 83        {
 84            get
 85            {
 86                return boolHandleErrors;
 87            }
 88            set
 89            {
 90                boolHandleErrors = value;
 91            }
 92        }
 93
 94        public string LastError
 95        {
 96            get
 97            {
 98                return strLastError;
 99            }
100        }
101
102        public bool LogErrors
103        {
104            get
105            {
106                return boolLogError;
107            }
108            set
109            {
110                boolLogError=value;
111            }
112        }
113
114        public string LogFile
115        {
116            get
117            {
118                return strLogFile;
119            }
120            set
121            {
122                strLogFile = value;
123            }
124        }
125
126        public int AddParameter(string name,object value)
127        {
128            DbParameter p = objFactory.CreateParameter();
129            p.ParameterName = name;
130            p.Value=value;
131            return objCommand.Parameters.Add(p);
132        }
133
134        public int AddParameter(DbParameter parameter)
135        {
136            return objCommand.Parameters.Add(parameter);
137        }
138
139        public DbCommand Command
140        {
141            get
142            {
143                return objCommand;
144            }
145        }
146
147        public void BeginTransaction()
148        {
149            if (objConnection.State == System.Data.ConnectionState.Closed)
150            {
151                objConnection.Open();
152            }
153            objCommand.Transaction = objConnection.BeginTransaction();
154        }
155
156        public void CommitTransaction()
157        {
158            objCommand.Transaction.Commit();
159            objConnection.Close();
160        }
161
162        public void RollbackTransaction()
163        {
164            objCommand.Transaction.Rollback();
165            objConnection.Close();
166        }
167
168        public int ExecuteNonQuery(string query)
169        {
170            return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
171        }
172
173        public int ExecuteNonQuery(string query,CommandType commandtype)
174        {
175            return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
176        }
177
178        public int ExecuteNonQuery(string query,ConnectionState connectionstate)
179        {
180            return ExecuteNonQuery(query,CommandType.Text,connectionstate);
181        }
182
183        public int ExecuteNonQuery(string query,CommandType commandtype, ConnectionState connectionstate)
184        {
185            objCommand.CommandText = query;
186            objCommand.CommandType = commandtype;
187            int i=-1;
188            try
189            {
190                if (objConnection.State == System.Data.ConnectionState.Closed)
191                {
192                    objConnection.Open();
193                }
194                i = objCommand.ExecuteNonQuery();
195            }
196            catch (Exception ex)
197            {
198                HandleExceptions(ex);
199            }
200            finally
201            {
202                objCommand.Parameters.Clear();
203                if (connectionstate == ConnectionState.CloseOnExit)
204                {
205                    objConnection.Close();
206                }
207            }
208
209            return i;
210        }
211
212        public object ExecuteScalar(string query)
213        {
214            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
215        }
216
217        public object ExecuteScalar(string query,CommandType commandtype)
218        {
219            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
220        }
221
222        public object ExecuteScalar(string query, ConnectionState connectionstate)
223        {
224            return ExecuteScalar(query, CommandType.Text, connectionstate);
225        }
226
227        public object ExecuteScalar(string query,CommandType commandtype, ConnectionState connectionstate)
228        {
229            objCommand.CommandText = query;
230            objCommand.CommandType = commandtype;
231            object o = null;
232            try
233            {
234                if (objConnection.State == System.Data.ConnectionState.Closed)
235                {
236                    objConnection.Open();
237                }
238                o = objCommand.ExecuteScalar();
239            }
240            catch (Exception ex)
241            {
242                HandleExceptions(ex);
243            }
244            finally
245            {
246                objCommand.Parameters.Clear();
247                if (connectionstate == ConnectionState.CloseOnExit)
248                {
249                    objConnection.Close();
250                }
251            }
252
253            return o;
254        }
255
256        public DbDataReader ExecuteReader(string query)
257        {
258            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
259        }
260
261        public DbDataReader ExecuteReader(string query,CommandType commandtype)
262        {
263            return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
264        }
265
266        public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
267        {
268            return ExecuteReader(query, CommandType.Text, connectionstate);
269        }
270
271        public DbDataReader ExecuteReader(string query,CommandType commandtype, ConnectionState connectionstate)
272        {
273            objCommand.CommandText = query;
274            objCommand.CommandType = commandtype;
275            DbDataReader reader=null;
276            try
277            {
278                if (objConnection.State == System.Data.ConnectionState.Closed)
279                {
280                    objConnection.Open();
281                }
282                if (connectionstate == ConnectionState.CloseOnExit)
283                {
284                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
285                }
286                else
287                {
288                    reader = objCommand.ExecuteReader();
289                }
290
291            }
292            catch (Exception ex)
293            {
294                HandleExceptions(ex);
295            }
296            finally
297            {
298                objCommand.Parameters.Clear();
299            }
300
301            return reader;
302        }
303
304        public DataSet ExecuteDataSet(string query)
305        {
306            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
307        }
308
309        public DataSet ExecuteDataSet(string query,CommandType commandtype)
310        {
311            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
312        }
313
314        public DataSet ExecuteDataSet(string query,ConnectionState connectionstate)
315        {
316            return ExecuteDataSet(query, CommandType.Text, connectionstate);
317        }
318
319        public DataSet ExecuteDataSet(string query,CommandType commandtype, ConnectionState connectionstate)
320        {
321            DbDataAdapter adapter = objFactory.CreateDataAdapter();
322            objCommand.CommandText = query;
323            objCommand.CommandType = commandtype;
324            adapter.SelectCommand = objCommand;
325            DataSet ds = new DataSet();
326            try
327            {
328                adapter.Fill(ds);
329            }
330            catch (Exception ex)
331            {
332                HandleExceptions(ex);
333            }
334            finally
335            {
336                objCommand.Parameters.Clear();
337                if (connectionstate == ConnectionState.CloseOnExit)
338                {
339                    if (objConnection.State == System.Data.ConnectionState.Open)
340                    {
341                        objConnection.Close();
342                    }
343                }
344            }
345            return ds;
346        }
347
348        private void HandleExceptions(Exception ex)
349        {
350            if (LogErrors)
351            {
352                WriteToLog(ex.Message);
353            }
354            if (HandleErrors)
355            {
356                strLastError = ex.Message;
357            }
358            else
359            {
360                throw ex;
361            }
362        }
363
364        private void WriteToLog(string msg)
365        {
366            StreamWriter writer= File.AppendText(LogFile);
367            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
368            writer.Close();
369        }
370        
371        public void Dispose()
372        {
373            objConnection.Close();
374            objConnection.Dispose();
375            objCommand.Dispose();
376        }
377
378    }
379
380    public enum Providers
381    {
382        SqlServer,OleDb,Oracle,ODBC,ConfigDefined
383    }
384
385    public enum ConnectionState
386    {
387        KeepOpen,CloseOnExit
388    }
389}