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
浙公网安备 33010602011771号