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