SqlHelper include Transaction

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data.Common;
  6 using System.Data;
  7 using System.Collections;
  8 using System.Reflection;
  9 using System.Collections.Specialized;
 10 using System.Configuration;
 11 using System.ComponentModel;
 12 using System.IO;
 13 using System.Text.RegularExpressions;
 14 using CP.Utility;
 15 
 16 namespace CP.DBService
 17 {
 18 public class DBHelper
 19 {
 20 protected string ConnectionString { get; set; }
 21 protected DataBaseType DBType { get; set; }
 22 private static DBHelper helper;
 23 private static bool hasRead = false;
 24 private static DBHelper gpmHelper;
 25 protected DBHelper() { }
 26 /// <summary>
 27 /// 创建单例模式
 28 /// </summary>
 29 /// <param name="connectionString"></param>
 30 /// <param name="dbType"></param>
 31 /// <returns></returns>
 32 public static DBHelper CreateInstance()
 33 {
 34 if (helper == null)
 35 {
 36 helper = new DBHelper();
 37 helper.ConnectionString = EncryptHelper.DecryptDES(ConfigurationManager.ConnectionStrings["BizConnectionString"].ConnectionString, "masicsyc");
 38 //helper.ConnectionString = ConfigurationManager.ConnectionStrings["BizConnectionString"].ConnectionString;
 39 helper.DBType = DataBaseType.Oracle;
 40 }
 41 return helper;
 42 }
 43 
 44 public static DBHelper GPMCreateInstance()
 45 {
 46 if (gpmHelper == null)
 47 {
 48 gpmHelper = new DBHelper();
 49 gpmHelper.ConnectionString = EncryptHelper.DecryptDES(ConfigurationManager.ConnectionStrings["GPMConnectionString"].ConnectionString, "masicgpm");
 50 //helper.ConnectionString = ConfigurationManager.ConnectionStrings["GPMConnectionString"].ConnectionString;
 51 gpmHelper.DBType = DataBaseType.Oracle;
 52 }
 53 return gpmHelper;
 54 }
 55 
 56 public static DBHelper CreateNonSingleTonInstance()
 57 {
 58 helper = new DBHelper();
 59 helper.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 60 helper.DBType = DataBaseType.Oracle;
 61 return helper;
 62 
 63 }
 64 
 65 DbTransaction transaction = null;
 66 public DbTransaction Transaction
 67 {
 68 get { return transaction; }
 69 set { transaction = value; }
 70 }
 71 public void Commit()
 72 {
 73 Transaction.Commit();
 74 Transaction = null;
 75 GC.Collect();
 76 }
 77 public void Rollback()
 78 {
 79 Transaction.Rollback();
 80 Transaction = null;
 81 GC.Collect();
 82 }
 83 public virtual void BeginTransaction()
 84 {
 85 DbConnection transConnection = CreateConnection();
 86 DbTransaction transaction = transConnection.BeginTransaction();
 87 Transaction = transaction;
 88 }
 89 public virtual void CloseConnection(DbConnection connection)
 90 {
 91 if (Transaction == null)
 92 connection.Close();
 93 }
 94 /// <summary>
 95 /// 创建连接
 96 /// </summary>
 97 /// <returns></returns>
 98 protected DbConnection CreateConnection()
 99 {
100 if (Transaction != null) return Transaction.Connection;
101 DbConnection connection = null;
102 switch (DBType)
103 {
104 case DataBaseType.Access:
105 connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
106 break;
107 case DataBaseType.MySql:
108 connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
109 break;
110 case DataBaseType.Oracle:
111 connection = new System.Data.OracleClient.OracleConnection(ConnectionString);
112 break;
113 case DataBaseType.SqlServer:
114 connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
115 break;
116 }
117 connection.Open();
118 return connection;
119 }
120 public DbCommand CreateCommand()
121 {
122 DbCommand command = CreateConnection().CreateCommand();
123 command.Transaction = Transaction;
124 return command;
125 }
126 /// <summary>
127 /// 创建命令
128 /// </summary>
129 /// <param name="connection"></param>
130 /// <param name="sqlSegment"></param>
131 /// <param name="parameters"></param>
132 /// <returns></returns>
133 protected DbCommand CreateCommand(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
134 {
135 DbCommand command = connection.CreateCommand();
136 command.CommandText = sqlSegment;
137 command.CommandType = commandType;
138 if (parameters != null)
139 command.Parameters.AddRange(parameters);
140 command.Transaction = Transaction;
141 return command;
142 }
143 /// <summary>
144 /// 创建适配器
145 /// </summary>
146 /// <param name="connection"></param>
147 /// <param name="sqlSegment"></param>
148 /// <param name="parameters"></param>
149 /// <returns></returns>
150 protected DbDataAdapter CreateAdapter(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
151 {
152 DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
153 command.CommandText = sqlSegment;
154 //command.CommandType = commandType;
155 //if (parameters != null)
156 // command.Parameters.AddRange(parameters);
157 
158 DbDataAdapter adapter = null;
159 switch (DBType)
160 {
161 case DataBaseType.Access:
162 adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
163 break;
164 case DataBaseType.MySql:
165 adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
166 break;
167 case DataBaseType.Oracle:
168 adapter = new System.Data.OracleClient.OracleDataAdapter((System.Data.OracleClient.OracleCommand)command);
169 break;
170 case DataBaseType.SqlServer:
171 adapter = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command);
172 break;
173 }
174 return adapter;
175 }
176 
177 public virtual T ExecuteScalar<T>(string sqlSegment)
178 {
179 return ExecuteScalar<T>(sqlSegment, null);
180 }
181 public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters)
182 {
183 return ExecuteScalar<T>(sqlSegment, null, CommandType.Text);
184 }
185 public virtual T ExecuteScalar<T>(string sqlSegment, CommandType commandType)
186 {
187 return ExecuteScalar<T>(sqlSegment, null, commandType);
188 }
189 public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
190 {
191 T result = default(T);
192 object value = ExecuteScalar(sqlSegment, parameters, commandType);
193 
194 if (value != null && value != DBNull.Value)
195 {
196 result = (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(value.ToString());
197 }
198 return result;
199 }
200 
201 public virtual object ExecuteScalar(string sqlSegment)
202 {
203 return ExecuteScalar(sqlSegment, null);
204 }
205 public virtual object ExecuteScalar(string sqlSegment, IDataParameter[] parameters)
206 {
207 return ExecuteScalar(sqlSegment, null, CommandType.Text);
208 }
209 public virtual object ExecuteScalar(string sqlSegment, CommandType commandType)
210 {
211 return ExecuteScalar(sqlSegment, null, commandType);
212 }
213 public virtual object ExecuteScalar(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
214 {
215 DbConnection connection = CreateConnection();
216 DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
217 
218 object result = command.ExecuteScalar();
219 command.Parameters.Clear();
220 CloseConnection(connection);
221 return result;
222 }
223 
224 /// <summary>
225 /// 执行SQL语句，返回影响的记录数，用于增/删/改数据
226 /// </summary>
227 /// <param name="sqlSegment"></param>
228 /// <param name="parameters"></param>
229 /// <returns></returns>
230 public virtual int ExecuteNonQuery(string sqlSegment)
231 {
232 return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
233 }
234 public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters)
235 {
236 return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
237 }
238 public virtual int ExecuteNonQuery(string sqlSegment, CommandType commandType)
239 {
240 return ExecuteNonQuery(sqlSegment, null, commandType);
241 }
242 public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
243 {
244 DbConnection connection = CreateConnection();
245 DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
246 int result = command.ExecuteNonQuery();
247 command.Parameters.Clear();
248 CloseConnection(connection);
249 return result;
250 }
251 
252 /// <summary>
253 /// 执行SQL语句，返回一张表，用于查询数据
254 /// </summary>
255 /// <param name="sqlSegment"></param>
256 /// <param name="parameters"></param>
257 /// <returns></returns>
258 public virtual DataTable ExecuteDataTable(string sqlSegment)
259 {
260 return ExecuteDataTable(sqlSegment, null);
261 }
262 public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters)
263 {
264 return ExecuteDataTable(sqlSegment, null, CommandType.Text);
265 }
266 public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
267 {
268 
269 DataTable dataTable = new DataTable();
270 DbConnection connection = CreateConnection();
271 DbDataAdapter adapter = CreateAdapter(connection, sqlSegment, parameters, commandType);
272 adapter.Fill(dataTable);
273 adapter.SelectCommand.Parameters.Clear();
274 CloseConnection(connection);
275 return dataTable;
276 
277 }
278 
279 /// <summary>
280 /// 执行SQL语句，返回一个Reader对象，用于查询数据
281 /// </summary>
282 /// <param name="sqlSegment"></param>
283 /// <param name="parameters"></param>
284 /// <returns></returns>
285 //public virtual DbDataReader ExecuteReader(string sqlSegment)
286 //{
287 // return ExecuteReader(sqlSegment, null);
288 //}
289 //public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters)
290 //{
291 // return ExecuteReader(sqlSegment, null,CommandType.Text);
292 //}
293 //public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters,CommandType commandType)
294 //{
295 // DbConnection connection = CreateConnection();
296 // DbCommand command = CreateCommand(connection, sqlSegment, parameters,commandType);
297 // DbDataReader reader = command.ExecuteReader();
298 // return reader;
299 //}
300 
301 public virtual IList<T> ExecuteObject<T>(string sqlSegment)
302 {
303 return ExecuteObject<T>(sqlSegment, null);
304 }
305 public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters)
306 {
307 return ExecuteObject<T>(sqlSegment, parameters, CommandType.Text);
308 }
309 public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
310 {
311 return ExecuteObject<T>(sqlSegment, parameters, commandType, null);
312 }
313 public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType, string entity_type)
314 {
315 DbConnection connection = CreateConnection();
316 DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
317 
318 IList<T> entities = new List<T>();
319 try
320 {
321 using (DbDataReader reader = command.ExecuteReader())
322 {
323 while (reader.Read())
324 {
325 T entity = Activator.CreateInstance<T>();
326 if (!string.IsNullOrEmpty(entity_type))
327 {
328 entity = (T)PortalService.CreateEntity(entity_type);
329 }
330 
331 var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
332 foreach (var property in properties)
333 {
334 object value = reader[property.Name];
335 object new_value = null;
336 if (value == null || value == DBNull.Value)
337 {
338 if (property.PropertyType == typeof(int) ||
339 property.PropertyType == typeof(Int32))
340 new_value = 0;
341 if (property.PropertyType == typeof(decimal))
342 new_value = 0m;
343 if (property.PropertyType == typeof(float))
344 new_value = 0f;
345 if (property.PropertyType == typeof(double))
346 new_value = 0d;
347 if (property.PropertyType == typeof(bool))
348 new_value = false;
349 if (property.PropertyType == typeof(DateTime))
350 new_value = DateTime.MinValue;
351 }
352 else
353 {
354 if (property.PropertyType == value.GetType())
355 new_value = value;
356 else if (property.PropertyType == typeof(DateTime?))
357 new_value = value;
358 else if (property.PropertyType == typeof(bool))
359 new_value = value.ToString() == "0" ? false : true;
360 else if (property.PropertyType == typeof(byte[]))
361 new_value = (byte[])value;
362 else
363 new_value = TypeDescriptor.GetConverter(property.PropertyType).ConvertFrom(value.ToString());
364 
365 //if (new_value != null)
366 //{
367 // //if (new_value.GetType() == typeof(string) && new_value.ToString() == " ")
368 // // new_value = "";
369 // if (new_value.GetType() == typeof(decimal) && (decimal)new_value != 0m && new_value.ToString().IndexOf(".") > 0)
370 // {
371 // string str_new_value = new_value.ToString();
372 // str_new_value = Regex.Replace(str_new_value, "0+$", "");
373 // new_value = decimal.Parse(str_new_value);
374 // }
375 //}
376 }
377 
378 property.SetValue(entity, new_value, null);
379 }
380 entities.Add(entity);
381 }
382 reader.Close();
383 
384 }
385 }
386 catch (Exception ex)
387 {
388 throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
389 }
390 finally
391 {
392 CloseConnection(connection);
393 }
394 command.Parameters.Clear();
395 return entities;
396 
397 }
398 
399 public virtual T ExecuteSingleObject<T>(string sqlSegment)
400 {
401 return ExecuteSingleObject<T>(sqlSegment, null);
402 }
403 public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters)
404 {
405 return ExecuteSingleObject<T>(sqlSegment, parameters, CommandType.Text);
406 }
407 public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
408 {
409 return ExecuteSingleObject<T>(sqlSegment, parameters, commandType, null);
410 }
411 public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType, string entity_type)
412 {
413 DbConnection connection = CreateConnection();
414 DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
415 
416 T entity = Activator.CreateInstance<T>();
417 try
418 {
419 using (DbDataReader reader = command.ExecuteReader())
420 {
421 if (reader.Read())
422 {
423 if (!string.IsNullOrEmpty(entity_type))
424 {
425 entity = (T)PortalService.CreateEntity(entity_type);
426 }
427 
428 var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
429 foreach (var property in properties)
430 {
431 object value = reader[property.Name];
432 object new_value = null;
433 if (value == DBNull.Value)
434 {
435 if (property.PropertyType == typeof(int) ||
436 property.PropertyType == typeof(Int32))
437 new_value = 0;
438 if (property.PropertyType == typeof(decimal))
439 new_value = 0m;
440 if (property.PropertyType == typeof(float))
441 new_value = 0f;
442 if (property.PropertyType == typeof(double))
443 new_value = 0d;
444 if (property.PropertyType == typeof(bool))
445 new_value = false;
446 if (property.PropertyType == typeof(DateTime))
447 new_value = DateTime.MinValue;
448 }
449 else
450 {
451 if (property.PropertyType == value.GetType())
452 new_value = value;
453 else if (property.PropertyType == typeof(DateTime?))
454 new_value = value;
455 else if (property.PropertyType == typeof(bool))
456 new_value = value.ToString() == "0" ? false : true;
457 else if (property.PropertyType == typeof(byte[]))
458 new_value = (byte[])value;
459 else
460 new_value = TypeDescriptor.GetConverter(property.PropertyType).ConvertFrom(value.ToString());
461 }
462 
463 property.SetValue(entity, new_value, null);
464 }
465 reader.Close();
466 }
467 }
468 }
469 catch (Exception ex)
470 {
471 throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
472 }
473 finally
474 {
475 CloseConnection(connection);
476 }
477 command.Parameters.Clear();
478 return entity;
479 }
480 
481 public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment)
482 {
483 return ExecuteSimpleTypeList<T>(sqlSegment, null);
484 }
485 public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment, IDataParameter[] parameters)
486 {
487 return ExecuteSimpleTypeList<T>(sqlSegment, parameters, CommandType.Text);
488 }
489 public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
490 {
491 DbConnection connection = CreateConnection();
492 DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
493 
494 IList<T> entities = new List<T>();
495 try
496 {
497 using (DbDataReader reader = command.ExecuteReader())
498 {
499 while (reader.Read())
500 {
501 T result = default(T);
502 object value = reader[0];
503 if (value == null || value == DBNull.Value)
504 continue;
505 result = (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(value.ToString());
506 entities.Add(result);
507 }
508 reader.Close();
509 }
510 }
511 catch (Exception ex)
512 {
513 throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
514 }
515 finally
516 {
517 CloseConnection(connection);
518 }
519 command.Parameters.Clear();
520 return entities;
521 }
522 
523 }
524 
525 public enum DataBaseType
526 {
527 Access,
528 SqlServer,
529 Oracle,
530 MySql
531 }
532 }
533