C#数据库访问类

代码

  1 using  System;
  2 using  System.Collections.Generic;
  3 using  System.Text;
  4 using  System.Data;
  5 using  System.Configuration;
  6 using  System.Data.Common;
  7 using  System.Data.SqlClient;
  8 using  System.Data.OleDb;
  9 using  System.Data.Odbc;
 10 using  System.Data.OracleClient;
 11 using  System.IO;
  12
 13 namespace  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 }
390
391

 

转载于:https://www.cnblogs.com/kevin-top/archive/2010/07/04/1770731.html