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}
390
391
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}
390
391
