//*****************************************************************************************************************
//* 编写人 ：peace
//* EMAIL : peacechzh@126.com
//* 开发日期：2009-10-21
//* 修 改 人：
//* 修改日期：
//* 描 述：数据库工厂访问类
//* 更新描述：里面供调用执行的各方法可带参数执行，在外部指定参数名和参数值即可。
//* 最终期望：支持.NET所支持的所有数据库并达到系统的无缝切换（尽情的忽悠吧O(∩_∩)O~）
//*****************************************************************************************************************
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Reflection;
namespace DataProvider
{
public class DataProviderFactory
{
public DbConnection conn;//抽象类型
private DbCommand cmd;//抽象类型
private DbProviderFactory provider;
private DbParameter Para;//不同数据库参数类型的抽象类型
private DbDataAdapter Adapter;//对应不同数据库的数据适配器
Dictionary<Type, String> ParametersFormat;//不同数据库参数格式化类型
public string retParaformat = string.Empty;//最终返回的格式化标志，如@{0},:{0}
public DataProviderFactory()
{
//从配置文件中取出标示数据库类型的字符串并通过ProviderName的不同支持不同类型的数据库
string providerName = ConfigurationManager.ConnectionStrings["ConnStr"].ProviderName;//也可以用索引,从1开始
//创建一个数据库对应的实例，使用该实例就可以创建对应的connection,command 和adapater等等对象
provider = DbProviderFactories.GetFactory(providerName);
//创建具体的数据库连接类型和命令执行类型
conn = provider.CreateConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
cmd = provider.CreateCommand();
cmd.Connection = conn;
//创建具体的参数类型
Para = provider.CreateParameter();
//创建具体的适配器类型
Adapter = provider.CreateDataAdapter();
//不同数据库参数前缀格式化
ParametersFormat = new Dictionary<Type, String>();
ParametersFormat.Add(typeof(System.Data.SqlClient.SqlCommand), "@{0}");//因SQL SERVER只返回{0}没有@前缀,在此初始化处理
//返回格式化标志
retParaformat = GetParameterFormat(cmd);
}
/// <summary>
/// 添加参数
/// </summary>
/// <param>参数名称</param>
/// <param>参数数据类型</param>
/// <param>参数值</param>
/// <param>参数对象的集合</param>
public void AddParam(string ParaName, DbType SqlType, object ParaValue, DbParameterCollection ParaCollect)
{
//不允许将一个DbCommand对象的Parameters插入到另外一个DbCommand对象，那么多个参数的话可以加上下面一句判断
//如果已经存在至少一个对象时，再深层拷贝一个
if (ParaCollect.Count >= 1)
{
Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
}
Para.ParameterName = string.Format(retParaformat, ParaName);
Para.DbType = SqlType;
if (ParaValue == null)
{
Para.Value = string.Empty;//DBNull.Value;
}
else
{
Para.Value = ParaValue;
}
ParaCollect.Add(Para);
}
public void AddParam(string ParaName, object ParaValue, DbParameterCollection ParaCollect)
{
if (ParaCollect.Count >= 1)
{
Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
}
Para.ParameterName = string.Format(retParaformat, ParaName);//将参数格式化为具体的数据库参数格式
if (ParaValue == null)
{
Para.Value = string.Empty;
}
else
{
Para.Value = ParaValue;
}
ParaCollect.Add(Para);
}
/// <summary>
/// 存储过程输入参数
/// </summary>
/// <param></param>
/// <param></param>
/// <param></param>
public void AddInputParam(string ParaName, object ParaValue, DbParameterCollection ParaCollect)
{
if (ParaCollect.Count >= 1)
{
Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
}
Para.ParameterName = string.Format(retParaformat.Replace(":",""), ParaName);//ORACLE存储过程参数前没有冒号
if (ParaValue == null)
{
Para.Value = string.Empty;
}
else
{
Para.Value = ParaValue;
}
ParaCollect.Add(Para);
}
/// <summary>
/// 存储过程输出参数
/// </summary>
/// <param></param>
/// <param></param>
/// <param></param>
public void AddOutputParam(string ParaName, DbParameterCollection ParaCollect)
{
if (ParaCollect.Count >= 1)
{
Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
}
Para.ParameterName = string.Format(retParaformat.Replace(":", ""), ParaName);
Para.Value = string.Empty;
ParaCollect.Add(Para);
ParaCollect[Para.ParameterName].Direction = System.Data.ParameterDirection.Output;//指定该参数为输出参数
}
/// <summary>
/// 存储过程返回值参数
/// </summary>
/// <param></param>
/// <param></param>
/// <param></param>
public void AddReturnParam(string ParaName,DbParameterCollection ParaCollect)
{
if (ParaCollect.Count >= 1)
{
Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
}
Para.ParameterName = string.Format(retParaformat.Replace(":", ""), ParaName);
Para.Value = string.Empty;
ParaCollect.Add(Para);
ParaCollect[Para.ParameterName].Direction = System.Data.ParameterDirection.ReturnValue;//指定该参数为返回值参数
}
/// <summary>
/// 抽象参数集合类型
/// </summary>
/// <returns></returns>
public DbParameterCollection GetParmCollection()
{
return cmd.Parameters;
}
/// <summary>
/// 执行SQL并返回数据集
/// </summary>
/// <param></param>
/// <returns></returns>
public DataSet ExecDataSet(string Sql)
{
DataSet ds = new DataSet();
try
{
this.Open();
cmd.CommandText = Replace(Sql);
Adapter.SelectCommand = cmd;
Adapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 执行SQL语句并返回DataReader对象
/// </summary>
/// <param></param>
/// <param></param>
/// <returns></returns>
public DbDataReader ExecuteDataReader(DbConnection dbcon,string cmdText)
{
try
{
if (dbcon.State == ConnectionState.Closed)
{
dbcon.Open();
}
cmd.CommandText = Replace(cmdText);
DbDataReader dr = cmd.ExecuteReader();
cmd.Parameters.Clear();
cmd.Dispose();
return dr;
}
catch
{
dbcon.Close();//发生异常在此处关闭，否则在调用显式处关闭
return null;
}
}
/// <summary>
/// 判断记录是否存在
/// </summary>
/// <param></param>
/// <returns></returns>
public bool Exist(string Sql)
{
bool exist;
this.Open();
cmd.CommandText = Replace(Sql);
DbDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
exist = true; //记录存在
}
else
{
exist = false; //记录不存在
}
dr.Close();
this.Close();
return exist;
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param></param>
public void ExecSql(string Sql)
{
try
{
this.Open();
cmd.CommandText = Replace(Sql);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.Close();
}
}
/// <summary>
/// 执行SQL语句,返回一个单值
/// </summary>
/// <param></param>
/// <returns></returns>
public string ReturnValue(string Sql)
{
object returnValue = string.Empty;
try
{
this.Open();
cmd.CommandText = Replace(Sql);
returnValue = cmd.ExecuteScalar();
if (returnValue == null)
{
returnValue = string.Empty;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.Close();
}
return returnValue.ToString();
}
/// <summary>
/// 执行多条SQL语句并启用数据库事务
/// </summary>
/// <param></param>
public bool ExecSqlTran(List<String> SQLStringList)
{
this.Open();
DbTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
cmd.CommandText = Replace(SQLStringList[n]);
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch
{
trans.Rollback();
return false;
}
finally
{
this.Close();
}
}
/// <summary>
/// 执行存储过程并返回结果集
/// </summary>
/// <param>存储过程名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName)
{
DataSet ds = new DataSet();
try
{
this.Open();
cmd.CommandText = storedProcName;
cmd.CommandType = CommandType.StoredProcedure;
Adapter.SelectCommand = cmd;
//Adapter.SelectCommand.CommandTimeout = 1200;//可以设置适当的超时时间(秒)，避免选择时间段过大导致填充数据集超时
Adapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 执行存储过程，方法不返回结果集
/// </summary>
/// <param></param>
public void RunVoidProcedure(string storedProcName)
{
cmd.CommandText = storedProcName;
cmd.CommandType = CommandType.StoredProcedure;
try
{
this.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.Close();
}
}
/// <summary>
/// 将实体类的属性进行参数转换(ORACLE测试通不过，必须要求所有参数都包含在语句中才行)
/// </summary>
/// <param></param>
/// <param></param>
//public void ConvertToParameters(object model, DbParameterCollection ParaCollect)
//{
// Type T = model.GetType();
// PropertyInfo[] propert = T.GetProperties();
// for (int i = 0; i < propert.Length; i++)
// {
// AddParam(propert[i].Name, propert[i].GetValue(model, null), ParaCollect);
// }
//}
/// <summary>
/// 将实体类的属性进行参数转换
/// </summary>
/// <param></param>
/// <param></param>
public void ConvertToParameters(object model, DbParameterCollection ParaCollect,List<string> fields)
{
Type T = model.GetType();
PropertyInfo[] propert = T.GetProperties();
for (int i = 0; i < propert.Length; i++)
{
if (fields.Contains(propert[i].Name)) //检测必须参数化的实体属性
{
AddParam(propert[i].Name, propert[i].GetValue(model, null), ParaCollect);
}
}
}
/// <summary>
/// 通过反射将取出的数据写入实体类(ORACLE测试通不过,需进行类型强制转换)
/// </summary>
/// <param></param>
/// <param></param>
//public void GetModel(object model, string cmdText)
//{
// PropertyInfo propertyInfo;
// DbDataReader dr = ExecuteDataReader(conn, cmdText);
// while (dr.Read())
// {
// for (int i = 0; i < dr.FieldCount; i++)
// {
// propertyInfo = model.GetType().GetProperty(dr.GetName(i));
// if (propertyInfo != null)
// {
// if (dr.GetValue(i) != DBNull.Value)
// {
// //Type t = dr.GetValue(i).GetType();
// propertyInfo.SetValue(model, dr.GetValue(i), null);
// }
// }
// }
// }
// dr.Close();
// conn.Close();
//}
/// <summary>
/// 通过反射将数据绑定到实体对象，由于不同数据库对应于.NET的数据类型不一样
/// 需做强制类型转换
/// </summary>
/// <param></param>
/// <param></param>
public void GetModel(object model, string cmdText)
{
PropertyInfo propertyInfo;
DbDataReader dr = ExecuteDataReader(conn, cmdText);
object _value;
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
propertyInfo = model.GetType().GetProperty(dr.GetName(i));
if (propertyInfo != null && dr.GetValue(i) != DBNull.Value)
{
switch (propertyInfo.PropertyType.ToString())
{
case "System.String":
{
_value = Convert.ToString(dr.GetValue(i));//字符串是全球通用类型，也可以不用转换
propertyInfo.SetValue(model, _value, null);
}break;
case "System.Int32":
{
_value = Convert.ToInt32(dr.GetValue(i));
propertyInfo.SetValue(model, _value, null);
} break;
case "System.Single":
{
_value = Convert.ToSingle(dr.GetValue(i));
propertyInfo.SetValue(model, _value, null);
} break;
case "System.Decimal":
{
_value = Convert.ToDecimal(dr.GetValue(i));
propertyInfo.SetValue(model, _value, null);
} break;
case "System.Double":
{
_value = Convert.ToDouble(dr.GetValue(i));
propertyInfo.SetValue(model, _value, null);
} break;
case "":
{
_value = Convert.ToDateTime(dr.GetValue(i));
propertyInfo.SetValue(model, _value, null);
} break;
default: break;
}
}
}
}
dr.Close();
conn.Close();
}
/// <summary>
/// 根据不同的数据库命令对象返回该类型数据库参数的前缀格式化字符串
/// </summary>
/// <param></param>
/// <returns></returns>
private string GetParameterFormat(DbCommand command)
{
if (!ParametersFormat.ContainsKey(command.GetType()))
{
this.Open();//读取参数前缀时需打开数据库连接
ParametersFormat.Add(
command.GetType(),
command.Connection.GetSchema("DataSourceInformation")
.Rows[0]["ParameterMarkerFormat"].ToString());
//conn.Close();在真正执行语句的时候去关闭，避免重复打开
}
return ParametersFormat[command.GetType()];
}
private void Open()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
private void Close()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
/// <summary>
/// 替换DML语句里的参数前缀
/// </summary>
/// <param></param>
/// <returns></returns>
public string Replace(string str)
{
return str.Replace("$", retParaformat.Substring(0, 1));
}
}
}
文件下载：PeaceHelper.cs
您可能感兴趣的文章: