using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.Reflection;
using System.Collections.Specialized;
using System.Configuration;
using System.ComponentModel;
using System.IO;
using System.Text.RegularExpressions;
using CP.Utility;
namespace CP.DBService
{
public class DBHelper
{
protected string ConnectionString { get; set; }
protected DataBaseType DBType { get; set; }
private static DBHelper helper;
private static bool hasRead = false;
private static DBHelper gpmHelper;
protected DBHelper() { }
/// <summary>
/// 创建单例模式
/// </summary>
/// <param name="connectionString"></param>
/// <param name="dbType"></param>
/// <returns></returns>
public static DBHelper CreateInstance()
{
if (helper == null)
{
helper = new DBHelper();
helper.ConnectionString = EncryptHelper.DecryptDES(ConfigurationManager.ConnectionStrings["BizConnectionString"].ConnectionString, "masicsyc");
//helper.ConnectionString = ConfigurationManager.ConnectionStrings["BizConnectionString"].ConnectionString;
helper.DBType = DataBaseType.Oracle;
}
return helper;
}
public static DBHelper GPMCreateInstance()
{
if (gpmHelper == null)
{
gpmHelper = new DBHelper();
gpmHelper.ConnectionString = EncryptHelper.DecryptDES(ConfigurationManager.ConnectionStrings["GPMConnectionString"].ConnectionString, "masicgpm");
//helper.ConnectionString = ConfigurationManager.ConnectionStrings["GPMConnectionString"].ConnectionString;
gpmHelper.DBType = DataBaseType.Oracle;
}
return gpmHelper;
}
public static DBHelper CreateNonSingleTonInstance()
{
helper = new DBHelper();
helper.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
helper.DBType = DataBaseType.Oracle;
return helper;
}
DbTransaction transaction = null;
public DbTransaction Transaction
{
get { return transaction; }
set { transaction = value; }
}
public void Commit()
{
Transaction.Commit();
Transaction = null;
GC.Collect();
}
public void Rollback()
{
Transaction.Rollback();
Transaction = null;
GC.Collect();
}
public virtual void BeginTransaction()
{
DbConnection transConnection = CreateConnection();
DbTransaction transaction = transConnection.BeginTransaction();
Transaction = transaction;
}
public virtual void CloseConnection(DbConnection connection)
{
if (Transaction == null)
connection.Close();
}
/// <summary>
/// 创建连接
/// </summary>
/// <returns></returns>
protected DbConnection CreateConnection()
{
if (Transaction != null) return Transaction.Connection;
DbConnection connection = null;
switch (DBType)
{
case DataBaseType.Access:
connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
break;
case DataBaseType.MySql:
connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
break;
case DataBaseType.Oracle:
connection = new System.Data.OracleClient.OracleConnection(ConnectionString);
break;
case DataBaseType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
break;
}
connection.Open();
return connection;
}
public DbCommand CreateCommand()
{
DbCommand command = CreateConnection().CreateCommand();
command.Transaction = Transaction;
return command;
}
/// <summary>
/// 创建命令
/// </summary>
/// <param name="connection"></param>
/// <param name="sqlSegment"></param>
/// <param name="parameters"></param>
/// <returns></returns>
protected DbCommand CreateCommand(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
DbCommand command = connection.CreateCommand();
command.CommandText = sqlSegment;
command.CommandType = commandType;
if (parameters != null)
command.Parameters.AddRange(parameters);
command.Transaction = Transaction;
return command;
}
/// <summary>
/// 创建适配器
/// </summary>
/// <param name="connection"></param>
/// <param name="sqlSegment"></param>
/// <param name="parameters"></param>
/// <returns></returns>
protected DbDataAdapter CreateAdapter(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
command.CommandText = sqlSegment;
//command.CommandType = commandType;
//if (parameters != null)
// command.Parameters.AddRange(parameters);
DbDataAdapter adapter = null;
switch (DBType)
{
case DataBaseType.Access:
adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
break;
case DataBaseType.MySql:
adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
break;
case DataBaseType.Oracle:
adapter = new System.Data.OracleClient.OracleDataAdapter((System.Data.OracleClient.OracleCommand)command);
break;
case DataBaseType.SqlServer:
adapter = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command);
break;
}
return adapter;
}
public virtual T ExecuteScalar<T>(string sqlSegment)
{
return ExecuteScalar<T>(sqlSegment, null);
}
public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters)
{
return ExecuteScalar<T>(sqlSegment, null, CommandType.Text);
}
public virtual T ExecuteScalar<T>(string sqlSegment, CommandType commandType)
{
return ExecuteScalar<T>(sqlSegment, null, commandType);
}
public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
T result = default(T);
object value = ExecuteScalar(sqlSegment, parameters, commandType);
if (value != null && value != DBNull.Value)
{
result = (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(value.ToString());
}
return result;
}
public virtual object ExecuteScalar(string sqlSegment)
{
return ExecuteScalar(sqlSegment, null);
}
public virtual object ExecuteScalar(string sqlSegment, IDataParameter[] parameters)
{
return ExecuteScalar(sqlSegment, null, CommandType.Text);
}
public virtual object ExecuteScalar(string sqlSegment, CommandType commandType)
{
return ExecuteScalar(sqlSegment, null, commandType);
}
public virtual object ExecuteScalar(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
DbConnection connection = CreateConnection();
DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
object result = command.ExecuteScalar();
command.Parameters.Clear();
CloseConnection(connection);
return result;
}
/// <summary>
/// 执行SQL语句，返回影响的记录数，用于增/删/改数据
/// </summary>
/// <param name="sqlSegment"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public virtual int ExecuteNonQuery(string sqlSegment)
{
return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
}
public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters)
{
return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
}
public virtual int ExecuteNonQuery(string sqlSegment, CommandType commandType)
{
return ExecuteNonQuery(sqlSegment, null, commandType);
}
public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
DbConnection connection = CreateConnection();
DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
int result = command.ExecuteNonQuery();
command.Parameters.Clear();
CloseConnection(connection);
return result;
}
/// <summary>
/// 执行SQL语句，返回一张表，用于查询数据
/// </summary>
/// <param name="sqlSegment"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public virtual DataTable ExecuteDataTable(string sqlSegment)
{
return ExecuteDataTable(sqlSegment, null);
}
public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters)
{
return ExecuteDataTable(sqlSegment, null, CommandType.Text);
}
public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
DataTable dataTable = new DataTable();
DbConnection connection = CreateConnection();
DbDataAdapter adapter = CreateAdapter(connection, sqlSegment, parameters, commandType);
adapter.Fill(dataTable);
adapter.SelectCommand.Parameters.Clear();
CloseConnection(connection);
return dataTable;
}
/// <summary>
/// 执行SQL语句，返回一个Reader对象，用于查询数据
/// </summary>
/// <param name="sqlSegment"></param>
/// <param name="parameters"></param>
/// <returns></returns>
//public virtual DbDataReader ExecuteReader(string sqlSegment)
//{
// return ExecuteReader(sqlSegment, null);
//}
//public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters)
//{
// return ExecuteReader(sqlSegment, null,CommandType.Text);
//}
//public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters,CommandType commandType)
//{
// DbConnection connection = CreateConnection();
// DbCommand command = CreateCommand(connection, sqlSegment, parameters,commandType);
// DbDataReader reader = command.ExecuteReader();
// return reader;
//}
public virtual IList<T> ExecuteObject<T>(string sqlSegment)
{
return ExecuteObject<T>(sqlSegment, null);
}
public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters)
{
return ExecuteObject<T>(sqlSegment, parameters, CommandType.Text);
}
public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
return ExecuteObject<T>(sqlSegment, parameters, commandType, null);
}
public virtual IList<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType, string entity_type)
{
DbConnection connection = CreateConnection();
DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
IList<T> entities = new List<T>();
try
{
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
T entity = Activator.CreateInstance<T>();
if (!string.IsNullOrEmpty(entity_type))
{
entity = (T)PortalService.CreateEntity(entity_type);
}
var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var property in properties)
{
object value = reader[property.Name];
object new_value = null;
if (value == null || value == DBNull.Value)
{
if (property.PropertyType == typeof(int) ||
property.PropertyType == typeof(Int32))
new_value = 0;
if (property.PropertyType == typeof(decimal))
new_value = 0m;
if (property.PropertyType == typeof(float))
new_value = 0f;
if (property.PropertyType == typeof(double))
new_value = 0d;
if (property.PropertyType == typeof(bool))
new_value = false;
if (property.PropertyType == typeof(DateTime))
new_value = DateTime.MinValue;
}
else
{
if (property.PropertyType == value.GetType())
new_value = value;
else if (property.PropertyType == typeof(DateTime?))
new_value = value;
else if (property.PropertyType == typeof(bool))
new_value = value.ToString() == "0" ? false : true;
else if (property.PropertyType == typeof(byte[]))
new_value = (byte[])value;
else
new_value = TypeDescriptor.GetConverter(property.PropertyType).ConvertFrom(value.ToString());
//if (new_value != null)
//{
// //if (new_value.GetType() == typeof(string) && new_value.ToString() == " ")
// // new_value = "";
// if (new_value.GetType() == typeof(decimal) && (decimal)new_value != 0m && new_value.ToString().IndexOf(".") > 0)
// {
// string str_new_value = new_value.ToString();
// str_new_value = Regex.Replace(str_new_value, "0+$", "");
// new_value = decimal.Parse(str_new_value);
// }
//}
}
property.SetValue(entity, new_value, null);
}
entities.Add(entity);
}
reader.Close();
}
}
catch (Exception ex)
{
throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
}
finally
{
CloseConnection(connection);
}
command.Parameters.Clear();
return entities;
}
public virtual T ExecuteSingleObject<T>(string sqlSegment)
{
return ExecuteSingleObject<T>(sqlSegment, null);
}
public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters)
{
return ExecuteSingleObject<T>(sqlSegment, parameters, CommandType.Text);
}
public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
return ExecuteSingleObject<T>(sqlSegment, parameters, commandType, null);
}
public virtual T ExecuteSingleObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType, string entity_type)
{
DbConnection connection = CreateConnection();
DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
T entity = Activator.CreateInstance<T>();
try
{
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (!string.IsNullOrEmpty(entity_type))
{
entity = (T)PortalService.CreateEntity(entity_type);
}
var properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var property in properties)
{
object value = reader[property.Name];
object new_value = null;
if (value == DBNull.Value)
{
if (property.PropertyType == typeof(int) ||
property.PropertyType == typeof(Int32))
new_value = 0;
if (property.PropertyType == typeof(decimal))
new_value = 0m;
if (property.PropertyType == typeof(float))
new_value = 0f;
if (property.PropertyType == typeof(double))
new_value = 0d;
if (property.PropertyType == typeof(bool))
new_value = false;
if (property.PropertyType == typeof(DateTime))
new_value = DateTime.MinValue;
}
else
{
if (property.PropertyType == value.GetType())
new_value = value;
else if (property.PropertyType == typeof(DateTime?))
new_value = value;
else if (property.PropertyType == typeof(bool))
new_value = value.ToString() == "0" ? false : true;
else if (property.PropertyType == typeof(byte[]))
new_value = (byte[])value;
else
new_value = TypeDescriptor.GetConverter(property.PropertyType).ConvertFrom(value.ToString());
}
property.SetValue(entity, new_value, null);
}
reader.Close();
}
}
}
catch (Exception ex)
{
throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
}
finally
{
CloseConnection(connection);
}
command.Parameters.Clear();
return entity;
}
public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment)
{
return ExecuteSimpleTypeList<T>(sqlSegment, null);
}
public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment, IDataParameter[] parameters)
{
return ExecuteSimpleTypeList<T>(sqlSegment, parameters, CommandType.Text);
}
public virtual IList<T> ExecuteSimpleTypeList<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
{
DbConnection connection = CreateConnection();
DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
IList<T> entities = new List<T>();
try
{
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
T result = default(T);
object value = reader[0];
if (value == null || value == DBNull.Value)
continue;
result = (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(value.ToString());
entities.Add(result);
}
reader.Close();
}
}
catch (Exception ex)
{
throw new Exception("执行SQL语句发生错误:" + sqlSegment + ".详细描述" + ex.Message, ex);
}
finally
{
CloseConnection(connection);
}
command.Parameters.Clear();
return entities;
}
}
public enum DataBaseType
{
Access,
SqlServer,
Oracle,
MySql
}
}
原文：http://www.cnblogs.com/asingna/p/5011894.html