using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using XiaoFeng.Model.Core;
using System.Threading.Tasks;
namespace XiaoFeng.Data
{
/// <summary>
/// 通用数据库操作类
/// 增加 加密连接字符功能
/// Author:jacky
/// QQ:7092734
/// Version : V 4.2
/// </summary>
public class DataHelper : IDisposable
{
#region 构造函数
/// <summary>
/// 构造函数
/// </summary>
public DataHelper() { this.ProviderType = DbProviderType.SqlServer; }
/// <summary>
/// 有参构造器
/// </summary>
/// <param name="connString">数据库连接字符串</param>
public DataHelper(string connString)
: this()
{
this.ConnectionString = connString;
}
#endregion
#region 属性
/// <summary>
/// 出错信息
/// </summary>
public string ErrorMessage { get; set; }
/// <summary>
/// 数据库连接对象
/// </summary>
private DbConnection DbConn = null;
/// <summary>
/// 数据库连接字符串
/// </summary>
public string ConnectionString { get; set; }
/// <summary>
/// 数据库连接超时时间
/// </summary>
public int ConnectionTimeOut { get; set; }
/// <summary>
/// 执行命令时超时间
/// </summary>
private int _CommandTimeOut = 0;
/// <summary>
/// 执行命令时超时间
/// </summary>
public int CommandTimeOut { get { return this._CommandTimeOut; } set { this._CommandTimeOut = value; } }
/// <summary>
/// 数据驱动 默认SqlServer
/// </summary>
private DbProviderType _ProviderType = DbProviderType.SqlServer;
/// <summary>
/// 数据驱动 默认SqlServer
/// </summary>
public DbProviderType ProviderType { get { return this._ProviderType; } set { this._ProviderType = value; } }
/// <summary>
/// 驱动工厂
/// </summary>
private DbProviderFactory _Provider;
/// <summary>
/// 驱动工厂
/// </summary>
private DbProviderFactory Provider
{
get
{
if (this._Provider == null)
this._Provider = ProviderFactory.GetDbProviderFactory(this.ProviderType);
return this._Provider;
}
set { this._Provider = value; }
}
/// <summary>
/// 是否使用事务处理
/// </summary>
private Boolean _IsTransaction = true;
/// <summary>
/// 是否使用事务处理
/// </summary>
public Boolean IsTransaction { get { return this._IsTransaction; } set { this._IsTransaction = value; } }
#endregion
#region 创建数据库连接Conn
/// <summary>
/// 创建Data数据库连接
/// </summary>
/// <param name="connString">数据库连接字符串或配置名称</param>
/// <returns></returns>
public DbConnection CreateConn(string connString = "")
{
try
{
if (connString.IsNullOrEmpty())
{
if (this.ConnectionString.IsNullOrEmpty())
{
this.ErrorMessage = "没有设置数据库连接配置."; return null;
}
}
if (this.ConnectionString.IndexOf("=", StringComparison.OrdinalIgnoreCase) == -1)
{
System.Configuration.ConnectionStringSettings ConnSetting = Config.GetConnectionString(this.ConnectionString);
if (ConnSetting == null)
{
string ConnApp = Config.GetConfig(this.ConnectionString);
if (ConnApp.IsNullOrEmpty())
{
this.ErrorMessage = "没有找到数据库相关配置."; return null;
}
else
this.ConnectionString = Encrypt.get(ConnApp);
}
else
{
if (ConnSetting.ProviderName.IsNullOrEmpty())
this.ConnectionString = Encrypt.get(ConnSetting.ConnectionString);
else
{
if (ConnSetting.ProviderName.IndexOf("{0}", StringComparison.OrdinalIgnoreCase) > -1)
this.ConnectionString = String.Format(Encrypt.get(ConnSetting.ProviderName), System.AppDomain.CurrentDomain.BaseDirectory + Encrypt.get(ConnSetting.ConnectionString).Replace("/", "\\").TrimStart('\\'));
else
{
if (ConnSetting.ProviderName.isPattern(@"System.Data.SqlClient"))
this.ProviderType = DbProviderType.SqlServer;
else if (ConnSetting.ProviderName.isPattern(@"MySql.Data.mySqlClient"))
this.ProviderType = DbProviderType.MySql;
else if (ConnSetting.ProviderName.isPattern(@"System.Data.OracleClient") || ConnSetting.ProviderName.isPattern(@"Oracle.DataAccess.Client"))
this.ProviderType = DbProviderType.Oracle;
if (this.ProviderType == DbProviderType.OleDb || this.ProviderType == DbProviderType.SQLite || this.ProviderType == DbProviderType.DB2)
this.ConnectionString = Encrypt.get(ConnSetting.ProviderName) + System.AppDomain.CurrentDomain.BaseDirectory + Encrypt.get(ConnSetting.ConnectionString).Replace("/", "\\").TrimStart('\\');
else this.ConnectionString = Encrypt.get(ConnSetting.ConnectionString);
}
}
}
}else
this.ConnectionString = Encrypt.get(connString);
DbConnection Conn = this.Provider.CreateConnection();
Conn.ConnectionString = this.ConnectionString;
this.ConnectionTimeOut = Conn.ConnectionTimeout;
return Conn;
}
catch (System.Data.Common.DbException e)
{
this.ErrorMessage = "创建数据库连接失败：" + e.Message;
LogHelper.WriteLog(e, "\r\n数据库连接字符串：" + this.ConnectionString + "[" + ProviderFactory.GetProviderInvariantName(this.ProviderType) + "]");
return null;
}
}
#endregion
#region 执行SQL语句
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="connString">配置名称</param>
/// <returns>返回执行行数</returns>
public int ExecuteNonQuery(string commandText, string connString = null)
{
int M = -1;
using (DbConnection Conn = this.CreateConn(connString ?? this.ConnectionString))
{
if (Conn == null) return -1;
Conn.Open();
if (this.IsTransaction)
{
using (DbTransaction Trans = Conn.BeginTransaction())
{
try
{
DbCommand Cmd = this.Provider.CreateCommand();
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.CommandText = commandText;
Cmd.Connection = Conn;
Cmd.Transaction = Trans;
M = Cmd.ExecuteNonQuery();
Trans.Commit();
}
catch (System.Data.Common.DbException ex)
{
this.ErrorMessage += "执行SQL语句失败:" + ex.Message.ToString() + "\r\nSQL语句：" + commandText + "\r\n";
LogHelper.WriteLog(ex, "\r\nSQL语句：" + commandText);
Trans.Rollback();
Conn.Close(); this.Dispose();
}
}
}
else
{
try
{
DbCommand Cmd = this.Provider.CreateCommand();
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.CommandText = commandText;
Cmd.Connection = Conn;
M = Cmd.ExecuteNonQuery();
}
catch (System.Data.Common.DbException ex)
{
Conn.Close(); Conn.Dispose(); this.Dispose();
this.ErrorMessage += "执行SQL语句失败:" + ex.Message.ToString() + "\r\nSQL语句：" + commandText + "\r\n";
LogHelper.WriteLog(ex, "\r\nSQL语句：" + commandText);
}
}
}
return M;
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="commandText">SQL数组</param>
/// <param name="connString">数据库连接字符串</param>
/// <returns></returns>
public int[] ExecuteNonQuery(string[] commandText, string connString = null)
{
int[] Count = new int[commandText.Length];
using (DbConnection Conn = this.CreateConn(connString ?? this.ConnectionString))
{
if (Conn == null) return new int[] { -1 };
Conn.Open();
if (this.IsTransaction)
{
using (DbTransaction Trans = Conn.BeginTransaction())
{
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.Connection = Conn;
Cmd.Transaction = Trans;
for (int i = 0; i < commandText.Length; i++)
{
Cmd.CommandText = commandText[i].ToString();
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
try
{
Count[i] = Cmd.ExecuteNonQuery();
Trans.Commit();
}
catch (System.Data.SqlClient.SqlException ex)
{
//Conn.Close(); Conn.Dispose(); this.Dispose();
this.ErrorMessage = "执行SQL语句失败:" + ex.Message.ToString() + "\r\nSQL语句：" + commandText;
LogHelper.WriteLog(ex, "\r\nSQL语句：" + commandText);
Trans.Rollback();
Count[i] = -1;
}
}
}
}
else
{
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.Connection = Conn;
for (int i = 0; i < commandText.Length; i++)
{
Cmd.CommandText = commandText[i].ToString();
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
try
{
Count[i] = Cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
Conn.Close(); this.Dispose();
this.ErrorMessage = "执行SQL语句失败:" + ex.Message.ToString() + "\r\nSQL语句：" + commandText;
LogHelper.WriteLog(ex, "\r\nSQL语句：" + commandText);
Count[i] = -1;
}
}
}
}
return Count;
}
#endregion
#region 异步执行SQL语句
/// <summary>
/// 异步执行SQL语句
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="callback">回调方法</param>
/// <param name="connString">配置名称</param>
/// <returns></returns>
public async void ExecuteNonQueryAsync(string commandText, Action<int> callback,string connString=null)
{
using (DbConnection Conn = this.CreateConn(connString ?? this.ConnectionString))
{
if (Conn == null) { callback(-2); }
Conn.Open();
if (this.IsTransaction)
{
using (DbTransaction Trans = Conn.BeginTransaction())
{
try
{
DbCommand Cmd = this.Provider.CreateCommand();
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.CommandText = commandText;
Cmd.Connection = Conn;
Cmd.Transaction = Trans;
int M = await Cmd.ExecuteNonQueryAsync();
callback(M);
Trans.Commit();
}
catch (System.Data.Common.DbException ex)
{
this.ErrorMessage += "执行SQL语句失败:" + ex.Message.ToString() + "\r\nSQL语句：" + commandText + "\r\n";
LogHelper.WriteLog(ex, "\r\nSQL语句：" + commandText);
Trans.Rollback(); callback(-1);
}
finally { Conn.Close(); this.Dispose(); }
}
}
else
{
try
{
DbCommand Cmd = this.Provider.CreateCommand();
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.CommandText = commandText;
Cmd.Connection = Conn;
int M = await Cmd.ExecuteNonQueryAsync();
callback(M);
}
catch (System.Data.Common.DbException ex)
{
this.ErrorMessage += "执行SQL语句失败:" + ex.Message.ToString() + "\r\nSQL语句：" + commandText + "\r\n";
LogHelper.WriteLog(ex, "\r\nSQL语句：" + commandText);
callback(-1);
}
finally { Conn.Close(); Conn.Dispose(); this.Dispose(); }
}
}
}
#endregion
#region 执行SQL语句返回首行首列
/// <summary>
/// 执行SQL语句返回首行首列
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="connString">配置名称</param>
/// <returns>返回首行首列数据</returns>
public object ExecuteScalar(string commandText, string connString = null)
{
object Data = null;
using (DbConnection Conn = this.CreateConn(connString ?? this.ConnectionString))
{
if (Conn == null) return null;
try
{
Conn.Open();
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.CommandText = commandText;
Cmd.Connection = Conn;
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Data = Cmd.ExecuteScalar();
}
catch (System.Data.Common.DbException e)
{
this.ErrorMessage = "执行SQL语句返回一个首行首列失败:" + e.Message.ToString() + "\r\nSQL语句：" + commandText; ;
LogHelper.WriteLog(e, "\r\nSQL语句：" + commandText);
}
finally { Conn.Close(); this.Dispose(); }
}
return Data;
}
#endregion
#region 异步执行SQL语句返回首行首列
/// <summary>
/// 异步执行SQL语句返回首行首列
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="callback">回调方法</param>
/// <param name="connString">配置名称</param>
/// <returns>返回首行首列数据</returns>
public async void ExecuteScalarAsync(string commandText,Action<object> callback, string connString = null)
{
object Data = null;
using (DbConnection Conn = this.CreateConn(connString ?? this.ConnectionString))
{
if (Conn == null) callback(null);
try
{
Conn.Open();
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.CommandText = commandText;
Cmd.Connection = Conn;
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Data = await Cmd.ExecuteScalarAsync();
callback(Data);
}
catch (System.Data.Common.DbException e)
{
this.ErrorMessage = "执行SQL语句返回一个首行首列失败:" + e.Message.ToString() + "\r\nSQL语句：" + commandText; ;
LogHelper.WriteLog(e, "\r\nSQL语句：" + commandText);
callback(null);
}
finally { Conn.Close(); this.Dispose(); }
}
}
#endregion
#region 执行SQL语句返回一个DataTable
/// <summary>
/// 执行SQL语句返回一个DataTable
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataTable</returns>
public DataTable ExecuteDataTable(string commandText, string connString = null)
{
DataTable Dt = new DataTable
{
Locale = System.Globalization.CultureInfo.CurrentCulture
};
using (DbConnection Conn = CreateConn(connString ?? this.ConnectionString))
{
try
{
if (Conn == null) return null;
DbDataAdapter Sda = this.Provider.CreateDataAdapter();
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.CommandText = commandText;
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.Connection = Conn;
Sda.SelectCommand = Cmd;
Sda.Fill(Dt);
}
catch (System.Data.Common.DbException e)
{
Conn.Close(); this.Dispose();
this.ErrorMessage = "执行SQL语句返回一个DataTable,失败:" + e.Message.ToString() + "\r\nSQL语句：" + commandText;
LogHelper.WriteLog(e, "\r\nSQL语句：" + commandText);
return null;
}
}
return Dt;
}
#endregion
#region 执行SQL语句返回一个DataSet
/// <summary>
/// 执行SQL语句返回一个DataSet
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataSet</returns>
public DataSet ExecuteDataSet(string commandText, string connString = null)
{
DataSet Ds = new DataSet
{
Locale = System.Globalization.CultureInfo.CurrentCulture
};
using (DbConnection Conn = CreateConn(connString ?? this.ConnectionString))
{
try
{
if (Conn == null) return null;
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.Connection = Conn;
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.CommandText = commandText;
DbDataAdapter Sda = this.Provider.CreateDataAdapter();
Sda.SelectCommand = Cmd;
if (this.CommandTimeOut > 0) Sda.SelectCommand.CommandTimeout = this.CommandTimeOut;
Sda.Fill(Ds);
}
catch (System.Data.Common.DbException e)
{
Conn.Close(); this.Dispose();
this.ErrorMessage = "执行SQL语句返回一个DataSet失败:" + e.Message.ToString() + "\r\nSQL语句：" + commandText; ;
LogHelper.WriteLog(e, "\r\nSQL语句：" + commandText);
return null;
}
}
return Ds;
}
#endregion
#region 执行SQL语句返回一个DataReader
/// <summary>
/// 执行SQL返回一个DataReader
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataReader</returns>
public DbDataReader ExecuteReader(string commandText, string connString = null)
{
try
{
DbConn = CreateConn(connString ?? this.ConnectionString);
if (DbConn == null) return null;
DbConn.Open();
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.CommandText = commandText;
Cmd.Connection = DbConn;
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
DbDataReader Sdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
return Sdr;
}
catch (System.Data.Common.DbException e)
{
this.Dispose();
this.ErrorMessage = "执行SQL语句返回一个DataReader失败:" + e.Message.ToString() + "\r\nSQL语句：" + commandText; ;
LogHelper.WriteLog(e, "\r\nSQL语句：" + commandText);
return null;
}
}
#endregion
/********************************************************************/
/* */
/* 下边是调用储存过程 */
/* */
/********************************************************************/
#region 用输入的数据替换到储存过程中
/// <summary>
/// 用输入的数据替换到储存过程中
/// </summary>
/// <param name="parameter">SQLParamerter</param>
/// <param name="commandText">储存过程</param>
/// <returns></returns>
public string GetParamInfo(DbParameter[] parameter, string commandText)
{
StringBuilder paramString = new StringBuilder("");
string paramSQLString = commandText;
if (parameter != null && parameter.Length > 0)
foreach (DbParameter p in parameter)
{
paramString.AppendLine(p.ParameterName + "[" + p.DbType.ToString() + "," + p.DbType.ToString() + "]" + "=" + p.Value);
string c = p.DbType.ToString() == "String" ? "'" : "";
paramSQLString = paramSQLString.Replace(p.ParameterName.ToString(), c + p.Value.GetValue() + c);
}
return "\r\n储存过程：" + commandText + "\r\n" + paramSQLString + "\r\n" + paramString.ToString();
}
#endregion
#region 创建储存过程参数
/// <summary>
/// 创建储存过程参数
/// </summary>
/// <param name="paramName">参数名</param>
/// <param name="paramValue">参数值</param>
/// <returns></returns>
public DbParameter MakeParam(string paramName, object paramValue)
{
DbParameter Param = this.Provider.CreateParameter();
Param.ParameterName = paramName;
Param.Value = paramValue;
return Param;
}
/// <summary>
/// 创建储存过程参数
/// </summary>
/// <param name="paramName">参数名</param>
/// <param name="paramValue">参数值</param>
/// <param name="parameterDirection">所属类型</param>
/// <returns></returns>
public DbParameter MakeParam(string paramName, object paramValue, ParameterDirection parameterDirection)
{
DbParameter Param = this.Provider.CreateParameter();
Param.ParameterName = paramName;
Param.Value = paramValue;
Param.Direction = parameterDirection;
return Param;
}
/// <summary>
/// 创建储存过程参数
/// </summary>
/// <param name="paramName">参数名</param>
/// <param name="paramValue">参数值</param>
/// <param name="paramType">参数类型</param>
/// <param name="paramSize">参数大小</param>
/// <param name="paramDirection">参数类型</param>
/// <returns></returns>
public DbParameter MakeParam(string paramName, object paramValue, DbType paramType, int paramSize = 0, ParameterDirection paramDirection = ParameterDirection.Input)
{
try
{
DbParameter Param = this.Provider.CreateParameter();
Param.ParameterName = paramName;
Param.DbType = paramType;
if (paramSize != 0) Param.Size = paramSize;
Param.Direction = paramDirection;
if (paramDirection == ParameterDirection.Input || paramDirection == ParameterDirection.InputOutput)
Param.Value = paramValue;
return Param;
}
catch (DbException e)
{
this.ErrorMessage = "创建储存过程参数出错：" + e.Message;
LogHelper.WriteLog(e);
return null;
}
}
#endregion
#region 返回一个带储存过程的Command
/// <summary>
/// 返回一个带储存过程的Command
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="param">Parameter数组</param>
/// <param name="connString">数据连接对象</param>
/// <returns>返回一个Command</returns>
public DbCommand MakeParamCommand(string commandText, DbParameter[] param, DbConnection connString)
{
return MakeParamCommand(commandText, CommandType.StoredProcedure, param, connString);
}
/// <summary>
/// 返回一个带储存过程的Command
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="commandType">储存过程类型</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">数据连接对象</param>
/// <returns>返回一个Command</returns>
public DbCommand MakeParamCommand(string commandText, CommandType commandType, DbParameter[] parameter, DbConnection connString)
{
DbCommand Cmd = this.Provider.CreateCommand();
Cmd.Connection = connString;
Cmd.CommandText = commandText;
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.CommandType = commandType;
try
{
parameter.Each(p => Cmd.Parameters.Add(p));
//foreach (DbParameter Para in Param) Cmd.Parameters.Add(Para);
}
catch (DbException e)
{
this.ErrorMessage = "把储存过程参数添加到Command中出错：" + e.Message;
LogHelper.WriteLog(e);
return null;
}
return Cmd;
}
#endregion
#region 执行储存过程返回执行行数
/// <summary>
/// 执行储存过程返回执行行数
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回执行行数</returns>
public int ExecuteNonQuery(string commandText, DbParameter[] parameter, string connString = null)
{
return ExecuteNonQuery(commandText, CommandType.StoredProcedure, parameter, connString ?? this.ConnectionString);
}
/// <summary>
/// 执行储存过程返回执行行数
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="commandType">CommandType</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回执行行数</returns>
public int ExecuteNonQuery(string commandText, CommandType commandType, DbParameter[] parameter, string connString = null)
{
int M = -1;
using (DbConnection Conn = this.CreateConn(connString ?? this.ConnectionString))
{
if (Conn == null) return -1;
Conn.Open();
if (this.IsTransaction)
{
using (DbTransaction Trans = Conn.BeginTransaction())
{
try
{
DbCommand Cmd = MakeParamCommand(commandText, commandType, parameter, Conn);
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Cmd.Transaction = Trans;
M = Cmd.ExecuteNonQuery();
Trans.Commit();
Cmd.Parameters.Clear();
}
catch (System.Data.Common.DbException e)
{
string paramString = this.GetParamInfo(parameter, commandText);
this.ErrorMessage = "执行储存过程返回执行行数失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
Trans.Rollback();
Conn.Close(); this.Dispose();
}
}
}
else
{
try
{
DbCommand Cmd = MakeParamCommand(commandText, commandType, parameter, Conn);
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
M = Cmd.ExecuteNonQuery();
Cmd.Parameters.Clear();
}
catch (System.Data.Common.DbException e)
{
Conn.Close(); Conn.Dispose(); this.Dispose();
string paramString = this.GetParamInfo(parameter, commandText);
this.ErrorMessage = "执行储存过程返回执行行数失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
}
}
}
return M;
}
/// <summary>
/// 执行储存过程返回执行行数
/// </summary>
/// <param name="commandList">CommandList结构体</param>
/// <returns></returns>
public int[] ExecuteNonQuery(List<CommandList> commandList)
{
int[] Count = new int[commandList.Count];
for (int i = 0; i < commandList.Count; i++)
{
this.ProviderType = commandList[i].ProviderType;
using (DbConnection Conn = this.CreateConn(commandList[i].ConnString))
{
if (Conn == null) return new int[] { -1 };
Conn.Open();
if (this.IsTransaction)
{
using (DbTransaction Trans = Conn.BeginTransaction())
{
try
{
DbCommand Cmd = MakeParamCommand(commandList[i].commandText, commandList[i].commandType, commandList[i].Param, Conn);
Cmd.Transaction = Trans;
Count[i] = Convert.ToInt32(Cmd.ExecuteNonQuery());
Cmd.Parameters.Clear();
Trans.Commit();
}
catch (System.Data.Common.DbException e)
{
//Conn.Close(); Conn.Dispose(); this.Dispose();
string paramString = this.GetParamInfo(commandList[i].Param, commandList[i].commandText);
this.ErrorMessage += "执行储存过程返回执行行数失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
Count[i] = -1;
Trans.Rollback();
}
}
}
else
{
try
{
DbCommand Cmd = MakeParamCommand(commandList[i].commandText, commandList[i].commandType, commandList[i].Param, Conn);
Count[i] = Convert.ToInt32(Cmd.ExecuteNonQuery());
Cmd.Parameters.Clear();
}
catch (System.Data.Common.DbException e)
{
Conn.Close(); this.Dispose();
string paramString = this.GetParamInfo(commandList[i].Param, commandList[i].commandText);
this.ErrorMessage += "执行储存过程返回执行行数失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
Count[i] = -1;
}
}
}
}
return Count;
}
#endregion
#region 执行储存过程返回首行首列
/// <summary>
/// 执行储存过程返回首行首列
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回首行首列</returns>
public object ExecuteScalar(string commandText, DbParameter[] parameter, string connString = null)
{
return ExecuteScalar(commandText, CommandType.StoredProcedure, parameter, connString ?? this.ConnectionString);
}
/// <summary>
/// 执行储存过程返回首行首列
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="commandType">CommandType类型</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回首行首列</returns>
public object ExecuteScalar(string commandText, CommandType commandType, DbParameter[] parameter, string connString = null)
{
object Data = null;
using (DbConnection Conn = CreateConn(connString ?? this.ConnectionString))
{
if (Conn == null) return -1;
try
{
Conn.Open();
DbCommand Cmd = MakeParamCommand(commandText, commandType, parameter, Conn);
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
Data = Cmd.ExecuteScalar();
Cmd.Parameters.Clear();
}
catch (System.Data.Common.DbException e)
{
Conn.Close(); this.Dispose();
string paramString = this.GetParamInfo(parameter, commandText);
this.ErrorMessage = "执行储存过程返回首行首列失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
}
}
return Data;
}
#endregion
#region 执行储存过程返回一个DataTable
/// <summary>
/// 执行储存过程返回一个DataTable
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataTable</returns>
public DataTable ExecuteDataTable(string commandText, DbParameter[] parameter, string connString = null)
{
return ExecuteDataTable(commandText, CommandType.StoredProcedure, parameter, connString ?? this.ConnectionString);
}
/// <summary>
/// 执行储存过程返回一个DataTable
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="commandType">CommandType类型</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataTable</returns>
public DataTable ExecuteDataTable(string commandText, CommandType commandType, DbParameter[] parameter, string connString = null)
{
DataTable Dt = new DataTable
{
Locale = System.Globalization.CultureInfo.CurrentCulture
};
using (DbConnection Conn = this.CreateConn(connString ?? this.ConnectionString))
{
try
{
if (Conn == null) return null;
Conn.Open();
DbCommand Cmd = MakeParamCommand(commandText, commandType, parameter, Conn);
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
DbDataAdapter Sda = this.Provider.CreateDataAdapter();
Sda.SelectCommand = Cmd;
Sda.Fill(Dt);
Cmd.Parameters.Clear();
}
catch (System.Data.Common.DbException e)
{
Conn.Close(); this.Dispose();
string paramString = this.GetParamInfo(parameter, commandText);
this.ErrorMessage = "执行储存过程返回一个DataTable失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
}
}
return Dt;
}
#endregion
#region 执行储存过程返回一个DataSet
/// <summary>
/// 执行储存过程返回一个DataSet
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataSet</returns>
public DataSet ExecuteDataSet(string commandText, DbParameter[] parameter, string connString = null)
{
return ExecuteDataSet(commandText, CommandType.StoredProcedure, parameter, connString ?? this.ConnectionString);
}
/// <summary>
/// 执行储存过程返回一个DataSet
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="commandType">CommandType类型</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataSet</returns>
public DataSet ExecuteDataSet(string commandText, CommandType commandType, DbParameter[] parameter, string connString = null)
{
DataSet Ds = new DataSet
{
Locale = System.Globalization.CultureInfo.CurrentCulture
};
using (DbConnection Conn = CreateConn(connString ?? this.ConnectionString))
{
try
{
if (Conn == null) return null;
Conn.Open();
DbCommand Cmd = MakeParamCommand(commandText, commandType, parameter, Conn);
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
DbDataAdapter Sda = this.Provider.CreateDataAdapter();
Sda.SelectCommand = Cmd;
Sda.Fill(Ds);
Cmd.Parameters.Clear();
}
catch (System.Data.Common.DbException e)
{
Conn.Close(); this.Dispose();
string paramString = this.GetParamInfo(parameter, commandText);
this.ErrorMessage = "执行储存过程返回一个DataSet失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
}
}
return Ds;
}
#endregion
#region 执行储存过程返回一个DataReader
/// <summary>
/// 执行储存过程返回一个DataReader
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataReader</returns>
public DbDataReader ExecuteReader(string commandText, DbParameter[] parameter, string connString = null)
{
return ExecuteReader(commandText, CommandType.StoredProcedure, parameter, connString ?? this.ConnectionString);
}
/// <summary>
/// 执行储存过程返回一个DataReader
/// </summary>
/// <param name="commandText">SQL语句或储存过程名称</param>
/// <param name="commandType">CommandType类型</param>
/// <param name="parameter">Parameter数组</param>
/// <param name="connString">配置名称</param>
/// <returns>返回一个DataReader</returns>
public DbDataReader ExecuteReader(string commandText, CommandType commandType, DbParameter[] parameter, string connString = null)
{
try
{
DbConn = CreateConn(connString ?? this.ConnectionString);
if (DbConn == null) return null;
DbConn.Open();
DbCommand Cmd = MakeParamCommand(commandText, commandType, parameter, DbConn);
if (this.CommandTimeOut > 0) Cmd.CommandTimeout = this.CommandTimeOut;
DbDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
Cmd.Parameters.Clear();
return Dr;
}
catch (System.Data.Common.DbException e)
{
this.Dispose();
string paramString = this.GetParamInfo(parameter, commandText);
this.ErrorMessage = "执行储存过程返回一个DataReader失败:" + e.Message.ToString() + paramString;
LogHelper.WriteLog(e, paramString);
return null;
}
}
#endregion
/********************************************************************/
/* */
/* 下边是数据库相关操作 */
/* */
/********************************************************************/
#region 插入数据
/// <summary>
/// 插入数据
/// </summary>
/// <typeparam name="T">模类型</typeparam>
/// <param name="model">模</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public virtual Boolean Add<T>(T model, string tableName = "")
{
if (model == null) return false;
Type t = model.GetType();
TableAttribute table = t.GetCustomAttribute<TableAttribute>();
if (table == null)
tableName = tableName.IsNullOrEmpty() ? t.Name : tableName;
else
tableName = table.Name;
List<DataField> list = this.GetList<T>(model);
if (list == null || list.Count == 0) return false;
return this.Add(tableName, list);
}
/// <summary>
/// 插入数据
/// </summary>
/// <typeparam name="T">模类型</typeparam>
/// <param name="tableName">表名</param>
/// <param name="model">模</param>
/// <returns></returns>
public virtual Boolean Add<T>(string tableName, T model)
{
return this.Add<T>(model, tableName);
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dataFields">字段值</param>
/// <returns></returns>
public virtual Boolean Add(string tableName, List<DataField> dataFields)
{
if (tableName.IsNullOrEmpty() || dataFields == null || dataFields.Count == 0) return false;
string Columns = "", Values = "";
DbParameter[] Param = new DbParameter[dataFields.Count];
int i = 0;
dataFields.Each(d =>
{
Columns += "[{0}],".format(d.Name);
Values += "@{0},".format(d.Name);
Param[i++] = MakeParam("@{0}".format(d.Name), d.Value);
});
Columns = Columns.TrimEnd(','); Values = Values.TrimEnd(',');
if (Columns.IsNullOrEmpty() || Values.IsNullOrEmpty()) return false;
return this.ExecuteNonQuery(@"insert into {0} ({1}) values({2})".format(tableName, Columns.TrimEnd(','), Values.TrimEnd(',')), CommandType.Text, Param) > 0;
}
#endregion
#region 更新数据
/// <summary>
/// 更新数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="data">匿名类型数据</param>
/// <returns></returns>
public virtual Boolean Update(string tableName, dynamic data)
{
if (tableName.IsNullOrEmpty() || data == null) return false;
List<DataField> list = this.GetList<dynamic>(data);
if (list == null || list.Count == 0) return false;
return this.Update(tableName, list);
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dataFields">字段值</param>
/// <returns></returns>
public virtual Boolean Update(string tableName, List<DataField> dataFields)
{
if (tableName.IsNullOrEmpty() || dataFields == null || dataFields.Count == 0) return false;
string Columns = "", Wheres = "";
DbParameter[] Param = new DbParameter[dataFields == null ? 0 : dataFields.Count];
int i = 0;
dataFields.Each(d =>
{
if (d.isWhere)
Wheres += (" and " + (d.Format.IsNullOrEmpty() ? "[{0}]=@{0}" : d.Format)).format(d.Name);
else
Columns += (d.Format.IsNullOrEmpty() ? "[{0}]=@{0}" : d.Format).format(d.Name) + ",";
Param[i++] = MakeParam(@"@{0}".format(d.Name), d.Value);
});
Columns = Columns.TrimEnd(',');
if (!Wheres.IsNullOrEmpty()) Wheres = Wheres.ReplacePattern(@"^ and ", " where ");
LogHelper.WriteLog(@"update {0} set {1}".format(tableName, Columns + Wheres));
if (Columns.IsNullOrEmpty()) return false;
return this.ExecuteNonQuery(@"update {0} set {1}".format(tableName, Columns + Wheres), CommandType.Text, Param) > 0;
}
#endregion
#region 删除数据
/// <summary>
/// 删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="data">匿名类型数据</param>
/// <returns></returns>
public virtual Boolean Delete(string tableName, dynamic data)
{
if (tableName.IsNullOrEmpty() || data == null) return false;
List<DataField> list = this.GetList<dynamic>(data);
if (list == null || list.Count == 0) return false;
return this.Delete(tableName, list);
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dataFields">数据</param>
/// <returns></returns>
public virtual Boolean Delete(string tableName, List<DataField> dataFields = null)
{
if (tableName.IsNullOrEmpty() || dataFields == null || dataFields.Count == 0) return false;
string Wheres = "";
List<DataField> list = dataFields == null ? null : dataFields.FindAll(d => d.isWhere);
DbParameter[] Param = new DbParameter[list == null ? 0 : list.Count]; int i = 0;
list.Each(d =>
{
Wheres += (" and " + (d.Format.IsNullOrEmpty() ? "[{0}]=@{0}" : d.Format)).format(d.Name);
Param[i++] = MakeParam(@"@{0}".format(d.Name), d.Value);
});
if (!Wheres.IsNullOrEmpty()) Wheres = Wheres.ReplacePattern(@"^ and ", " where ");
return this.ExecuteNonQuery(@"delete from {0}{1}".format(tableName, Wheres), CommandType.Text, Param) > 0;
}
#endregion
#region 查询数据
/// <summary>
/// 查询数据返回DataTable
/// </summary>
/// <param name="tableName">表名或SQL,表名就是查询或SQL则可以执行增删改查</param>
/// <param name="dataFields">字段值</param>
/// <returns></returns>
public virtual DataTable Select(string tableName, List<DataField> dataFields)
{
if (tableName.IsNullOrEmpty()) return new DataTable();
string SQL = "";
DbParameter[] Param = null; int i = 0;
if (tableName.isMatch(@"\s+"))
{
SQL = tableName;
if (dataFields != null && dataFields.Count > 0)
{
Param = new DbParameter[dataFields == null ? 0 : dataFields.Count];
dataFields.Each(d =>
{
if (SQL.isPattern(@"\$?\{[a-z]+?[a-z_0-9-]*?\}"))
SQL = SQL.ReplacePattern(@"\$?{{{0}}}".format(d.Name), "@{0}".format(d.Name));
else
SQL = SQL.ReplacePattern(@"\$?\{" + i + @"\}", "@{0}".format(d.Name));
Param[i++] = this.MakeParam(@"@{0}".format(d.Name), d.Value);
});
}
}
else
{
string Columns = "", Wheres = "";
SQL = "select {0} from {1}{2}";
if (dataFields == null || dataFields.Count == 0)
SQL = SQL.format("*", tableName, "");
else
{
Param = new DbParameter[dataFields.FindAll(d => d.isWhere).Count];
dataFields.Each(d =>
{
if (d.isWhere)
{
Wheres += (" and " + (d.Format.IsNullOrEmpty() ? "[{0}]=@{0}" : d.Format)).format(d.Name);
Param[i++] = this.MakeParam(@"@{0}".format(d.Name), d.Value);
}
else
Columns += "[{0}],".format(d.Name);
});
if (!Wheres.IsNullOrEmpty()) Wheres = Wheres.ReplacePattern(@"^ and ", " where ");
Columns = Columns.TrimEnd(',');
SQL = SQL.format(Columns.IsNullOrEmpty() ? " * " : Columns, tableName, Wheres);
}
}
return Param == null ? this.ExecuteDataTable(SQL) : this.ExecuteDataTable(SQL, CommandType.Text, Param);
}
/// <summary>
/// 查询数据返回列表
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="tableName">表名或SQL,表名就是查询或SQL则可以执行增删改查</param>
/// <param name="DataFields">字段值</param>
/// <returns></returns>
public virtual List<T> Select<T>(string tableName, List<DataField> DataFields = null)
{
return this.Select(tableName, DataFields).ToList<T>();
}
/// <summary>
/// 查询数据
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="tableName">表名或SQL,表名就是查询或SQL则可以执行增删改查</param>
/// <param name="DataFields">字段值</param>
/// <returns></returns>
public virtual T select<T>(string tableName, List<DataField> DataFields = null)
{
T t = default(T);
Type _t = typeof(T); if (_t == typeof(DataTable)) return t;
string SQL = "";
DbParameter[] Param = null; int i = 0;
if (tableName.isMatch(@"\s+"))
{
List<T> list = this.Select<T>(tableName, DataFields);
return (list == null || list.Count == 0) ? default(T) : list[0];
}
else
{
string Columns = "", Wheres = "";
if (this.ProviderType == DbProviderType.SQLite || this.ProviderType == DbProviderType.MySql
|| this.ProviderType == DbProviderType.PostgreSql)
SQL = "select {0} from {1}{2} limit 1";
else if (this.ProviderType == DbProviderType.Oracle)
{
SQL = "select {0} from {1}{2}";
Wheres += " and rownum <= 1";
}
else if (this.ProviderType == DbProviderType.OleDb || this.ProviderType == DbProviderType.SqlServer)
SQL = "select top 1 {0} from {1}{2}";
else if (this.ProviderType == DbProviderType.DB2)
SQL = "select {0} from {1}{2} fetch first 1 rows only";
else if (this.ProviderType == DbProviderType.Informix)
SQL = "select first 1 {0} from {1}{2}";
else return t;
Param = new DbParameter[DataFields.FindAll(d => d.isWhere).Count];
if (DataFields == null || DataFields.Count == 0)
SQL = SQL.format("*", tableName, "");
else
{
DataFields.Each(d =>
{
if (d.isWhere)
{
Wheres += (" and " + (d.Format.IsNullOrEmpty() ? "[{0}]=@{0}" : d.Format)).format(d.Name);
Param[i++] = this.MakeParam(@"@{0}".format(d.Name), d.Value);
}
else
Columns += "[{0}],".format(d.Name);
});
if (!Wheres.IsNullOrEmpty()) Wheres = Wheres.ReplacePattern(@"^ and ", " where ");
Columns = Columns.TrimEnd(',');
SQL = SQL.format(Columns.IsNullOrEmpty() ? " * " : Columns, tableName, Wheres);
}
}
return (Param == null ? this.ExecuteDataTable(SQL) : this.ExecuteDataTable(SQL, CommandType.Text, Param)).ToEntity<T>();
}
#endregion
#region 分页查询数据
/// <summary>
/// 分页查询数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="Columns">显示列</param>
/// <param name="Condition">条件</param>
/// <param name="OrderColumnName">排序字段</param>
/// <param name="OrderType">排序类型ASC或DESC</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">一页多少条</param>
/// <param name="PageCount">共多少页</param>
/// <param name="Counts">共多少条</param>
/// <param name="PrimaryKey">主键</param>
/// <returns></returns>
public virtual DataTable Select(string tableName, string Columns, string Condition, string OrderColumnName, string OrderType, int PageIndex, int PageSize, out int PageCount, out int Counts, string PrimaryKey = "")
{
switch (this.ProviderType)
{
case DbProviderType.SqlServer:
return new SqlHelper() { ConnectionString = this.ConnectionString }.Select(tableName, Columns, Condition, OrderColumnName, OrderType, PageIndex, PageSize, out PageCount, out Counts, PrimaryKey);
case DbProviderType.OleDb:
return new OleDbHelper() { ConnectionString = this.ConnectionString }.Select(tableName, Columns, Condition, OrderColumnName, OrderType, PageIndex, PageSize, out PageCount, out Counts, PrimaryKey);
case DbProviderType.SQLite:
return new SQLiteHelper() { ConnectionString = this.ConnectionString }.Select(tableName, Columns, Condition, OrderColumnName, OrderType, PageIndex, PageSize, out PageCount, out Counts, PrimaryKey);
default:
PageCount = Counts = 0;
return null;
}
}
/// <summary>
/// 分页查询数据
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="tableName">表名</param>
/// <param name="Columns">显示列</param>
/// <param name="Condition">条件</param>
/// <param name="OrderColumnName">排序字段</param>
/// <param name="OrderType">排序类型ASC或DESC</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">一页多少条</param>
/// <param name="PageCount">共多少页</param>
/// <param name="Counts">共多少条</param>
/// <param name="PrimaryKey">主键</param>
/// <returns></returns>
public virtual List<T> Select<T>(string tableName, string Columns, string Condition, string OrderColumnName, string OrderType, int PageIndex, int PageSize, out int PageCount, out int Counts, string PrimaryKey = "")
{
return this.Select(tableName, Columns, Condition, OrderColumnName, OrderType, PageIndex, PageSize, out PageCount, out Counts, PrimaryKey).ToList<T>();
}
#endregion
#region 执行数据
/// <summary>
/// 执行数据
/// </summary>
/// <param name="SQL">SQL语句:可执行增删改</param>
/// <param name="DataFields">字段值</param>
/// <returns></returns>
public virtual int Execute(string SQL, List<DataField> DataFields = null)
{
if (!SQL.isMatch(@"\s+")) return -1;
DbParameter[] Param = null; int i = 0;
Param = new DbParameter[DataFields == null ? 0 : DataFields.Count];
DataFields.Each(d =>
{
if (SQL.isPattern(@"\$?\{[a-z]+?[a-z_0-9-]*?\}"))
SQL = SQL.ReplacePattern(@"\$?{{{0}}}".format(d.Name), "@{0}".format(d.Name));
else
SQL = SQL.ReplacePattern(@"\$?\{" + i + @"\}", "@{0}".format(d.Name));
Param[i++] = this.MakeParam(@"@{0}".format(d.Name), d.Value);
});
return Param == null ? this.ExecuteNonQuery(SQL) : this.ExecuteNonQuery(SQL, CommandType.Text, Param);
}
#endregion
#region 泛类型转List
/// <summary>
/// 泛类型转List
/// </summary>
/// <typeparam name="T">泛类型</typeparam>
/// <param name="model">对象，如果是匿名类型则条件用new object[]</param>
/// <returns></returns>
private List<DataField> GetList<T>(T model)
{
List<DataField> list = new List<DataField>();
if (model == null) return list;
Type t = model.GetType();
t.GetProperties().Each(p =>
{
DataField data = new DataField
{
Name = p.Name,
isWhere = false
};
object value = p.GetValue(model, null);
if (value != null)
{
if ((p.PropertyType == typeof(object[]) || p.PropertyType.ToString().isPattern(@"\[\]$")))
{
data.Value = ((object[])value)[0];
data.isWhere = true;
}
else
data.Value = value;
list.Add(data);
}
});
t.GetFields().Each(f =>
{
DataField data = new DataField
{
Name = f.Name,
isWhere = false
};
object value = f.GetValue(model);
if (value != null)
{
if ((f.FieldType == typeof(object[]) || f.FieldType.ToString().isPattern(@"\[\]$")))
{
data.Value = ((object[])value)[0];
data.isWhere = true;
}
else
data.Value = value;
list.Add(data);
}
});
return list;
}
#endregion
#region 参数结构体
/// <summary>
/// 参数结构体
/// </summary>
public struct CommandList
{
/// <summary>
/// CommandText
/// </summary>
public string commandText;
/// <summary>
/// 执行类型
/// </summary>
public CommandType commandType;
/// <summary>
/// 储存参数
/// </summary>
public DbParameter[] Param;
/// <summary>
/// 数据库连接字符
/// </summary>
public string ConnString;
/// <summary>
/// 数据库类型
/// </summary>
public DbProviderType ProviderType;
}
#endregion
#region 回收资源
/// <summary>
/// 回收资源
/// </summary>
public void Dispose()
{
this.Dispose(true);
GC.SuppressFinalize(this);
}
/// <summary>
/// 回收资源
/// </summary>
/// <param name="disposing">是否释放</param>
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (DbConn != null)
{
DbConn.Close();
DbConn.Dispose();
DbConn = null;
}
else
{
}
}
}
/// <summary>
/// 回收资源
/// </summary>
~DataHelper()
{
this.Dispose(false);
}
#endregion
}
#region 数据库类型枚举
/// <summary>
/// 数据库类型枚举
/// </summary>
public enum DbProviderType : int
{
/// <summary>
/// SqlClient Data Provider
/// </summary>
SqlServer,
/// <summary>
/// OleDb Data Provider
/// </summary>
OleDb,
/// <summary>
/// MySql Data Provider
/// </summary>
MySql,
/// <summary>
/// SQLite Data Provider
/// </summary>
SQLite,
/// <summary>
/// OracleClient Data Provider
/// </summary>
Oracle,
/// <summary>
/// Odbc Data Provider
/// </summary>
ODBC,
/// <summary>
/// Firebird Data Provider
/// </summary>
Firebird,
/// <summary>
/// PostgreSql Data Provider
/// </summary>
PostgreSql,
/// <summary>
/// IBM DB2 Data Provider
/// </summary>
DB2,
/// <summary>
/// IBM Informix Data Provider
/// </summary>
Informix,
/// <summary>
/// Microsoft SQL Server Compact Data Provider 4.0
/// </summary>
SqlServerCe
}
#endregion
#region DbProviderFactory工厂类
/// <summary>
/// DbProviderFactory工厂类
/// </summary>
public static class ProviderFactory
{
/// <summary>
/// 驱动集
/// </summary>
private static Dictionary<DbProviderType, string> providerInvariantNames = new Dictionary<DbProviderType, string>();
/// <summary>
/// 驱动集
/// </summary>
private static Dictionary<DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20);
/// <summary>
/// 独占锁
/// </summary>
private static System.Threading.ReaderWriterLockSlim Lock = new System.Threading.ReaderWriterLockSlim();
/// <summary>
/// 构造器
/// </summary>
static ProviderFactory()
{
//加载已知的数据库访问类的程序集
providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient");
providerInvariantNames.Add(DbProviderType.OleDb, "System.Data.OleDb");
providerInvariantNames.Add(DbProviderType.ODBC, "System.Data.ODBC");
providerInvariantNames.Add(DbProviderType.Oracle, "Oracle.DataAccess.Client");
providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient");
providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite");
providerInvariantNames.Add(DbProviderType.Firebird, "FirebirdSql.Data.Firebird");
providerInvariantNames.Add(DbProviderType.PostgreSql, "Npgsql");
providerInvariantNames.Add(DbProviderType.DB2, "IBM.Data.DB2.iSeries");
providerInvariantNames.Add(DbProviderType.Informix, "IBM.Data.Informix");
providerInvariantNames.Add(DbProviderType.SqlServerCe, "System.Data.SqlServerCe");
}
/// <summary>
/// 获取指定数据库类型对应的程序集名称
/// </summary>
/// <param name="providerType">数据库类型枚举</param>
/// <returns></returns>
public static string GetProviderInvariantName(DbProviderType providerType)
{
return providerInvariantNames[providerType];
}
/// <summary>
/// 获取指定类型的数据库对应的DbProviderFactory
/// </summary>
/// <param name="providerType">数据库类型枚举</param>
/// <returns></returns>
public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)
{
Lock.EnterWriteLock();
try
{
//如果还没有加载，则加载该DbProviderFactory
if (!providerFactoies.ContainsKey(providerType))
{
try { providerFactoies.Add(providerType, ImportDbProviderFactory(providerType)); } catch { }
}
}
finally { Lock.ExitWriteLock(); }
return providerFactoies[providerType];
}
/// <summary>
/// 加载指定数据库类型的DbProviderFactory
/// </summary>
/// <param name="providerType">数据库类型枚举</param>
/// <returns></returns>
private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)
{
string providerName = providerInvariantNames[providerType];
DbProviderFactory factory = null;
try
{
//从全局程序集中查找
factory = DbProviderFactories.GetFactory(providerName);
}
catch (ArgumentException e)
{
factory = null;
throw e;
}
return factory;
}
}
#endregion
}
05-30
12-19
9729
9729
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
