| 数据工厂类,实现了跨数据库的操作,多个数据库helper |
|
|
|
|
usingSystem;
usingSystem.Collections;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Configuration;
/
title:数据工厂类,实现了跨数据库的操作,多个数据库helper
date:2016-04-29
author:YUXiaoWei
/
namespaceDBHelper
{
///
///数据工厂类,实现了跨数据库的操作.
///
publicclassDataAccess
{
///
///Webconfig配置连接字符串
///
privatestring_confirString="ConnectionString";
///
///数据工厂类
///
publicDataAccess()
{
}
///
///数据工厂类
///
///web.config关键字
publicDataAccess(stringconfigString)
{
ConfigString=configString;
}
///
///属性,设置数据库连接字符串
///
publicstringConfigString
{
get
{
return_confirString;
}
set
{
_confirString=value;
}
}
//==============================================GetProviderName==============================
#region获得数据库的类型publicstringGetProviderName(stringConfigString)
///
///返回数据提供者
///
///返回数据提供者
publicstringGetProviderName(stringConfigString)
{
ConnectionStringSettingsCollectionConfigStringCollention=ConfigurationManager.ConnectionStrings;
if(ConfigStringCollention==null||ConfigStringCollention.Count<=0)
{
thrownewException("web.config中无连接字符串!");
}
ConnectionStringSettingsStringSettings=null;
if(ConfigString==string.Empty)
{
StringSettings=ConfigurationManager.ConnectionStrings["ConnectionString"];
}
else
{
StringSettings=ConfigurationManager.ConnectionStrings[ConfigString];
}
returnStringSettings.ProviderName;
}
///
///返回数据提供者
///
///
publicstringGetProviderName()
{
returnGetProviderName(ConfigString);
}
#endregion
//=====================================================获得连接字符串====================================
///
///获得连接字符串
///
///
publicstringGetConnectionString(stringConfigString)
{
ConnectionStringSettingsCollectionConfigStringCollention=ConfigurationManager.ConnectionStrings;
if(ConfigStringCollention==null||ConfigStringCollention.Count<=0)
{
thrownewException("web.config中无连接字符串!");
}
ConnectionStringSettingsStringSettings=null;
if(ConfigString==string.Empty)
{
StringSettings=ConfigurationManager.ConnectionStrings["ConnectionString"];
}
else
{
StringSettings=ConfigurationManager.ConnectionStrings[ConfigString];
}
returnStringSettings.ConnectionString;
}
publicstringGetConnectionString()
{
returnGetConnectionString(ConfigString);
}
//==============================================GetDbproviderFactory=========================
#region返回数据工厂publicDbProviderFactoryGetDbProviderFactory()
///
///返回数据工厂
///
///
publicDbProviderFactoryGetDbProviderFactory()
{
DbProviderFactoryf=null;
stringProviderName=GetProviderName();
switch(ProviderName)
{
case"System.Data.SqlClient":
f=GetDbProviderFactory("System.Data.SqlClient");
break;
case"System.Data.OracleClient":
f=GetDbProviderFactory("System.Data.OracleClient");
break;
case"System.Data.OleDb":
f=GetDbProviderFactory("System.Data.OleDb");
break;
case"System.Data.SQLite":
f=GetDbProviderFactory("System.Data.SQLite");
break;
default:
f=GetDbProviderFactory("System.Data.SqlClient");
break;
}
returnf;
}
///
///返回数据工厂
///
///
///
publicDbProviderFactoryGetDbProviderFactory(stringprovidername)
{
returnDbProviderFactories.GetFactory(providername);
}
#endregion
//==============================================CreateConnection=============================
#region创建数据库连接publicDbConnectionCreateConnection()
///
///创建数据库连接
///
///
publicDbConnectionCreateConnection()
{
DbConnectioncon=GetDbProviderFactory().CreateConnection();
con.ConnectionString=GetConnectionString();
returncon;
}
///
///创建数据库连接
///
///
///
publicDbConnectionCreateConnection(stringprovdername)
{
DbConnectioncon=GetDbProviderFactory(provdername).CreateConnection();
con.ConnectionString=GetConnectionString();
returncon;
}
#endregion
//==============================================CreateCommand================================
#region创建执行命令对象publicoverrideDbCommandCreateCommand(stringsql,CommandTypecmdType,DbParameter[]parameters)
///
///创建执行命令对象
///
///
///
///
///
publicDbCommandCreateCommand(stringsql,CommandTypecmdType,DbParameter[]parameters)
{
DbCommand_command=GetDbProviderFactory().CreateCommand();
_command.Connection=CreateConnection();
_command.CommandText=sql;
_command.CommandType=cmdType;
if(parameters!=null&¶meters.Length>0)
{
foreach(DbParameterparaminparameters)
{
_command.Parameters.Add(param);
}
}
return_command;
}
///
///创建执行命令对象
///
///SQL语句
///执行命令对象实例
publicDbCommandCreateCommand(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
returnCreateCommand(sql,CommandType.Text,parameters);
}
///
///创建执行命令对象
///
///SQL语句
///执行命令对象实例
publicDbCommandCreateCommand(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
returnCreateCommand(sql,cmdtype,parameters);
}
///
///创建执行命令对象
///
///ＳＱＬ语句
///参数
///执行命令对象实例
publicDbCommandCreateCommand(stringsql,DbParameter[]parameters)
{
returnCreateCommand(sql,CommandType.Text,parameters);
}
#endregion
//=========================================CreateAdapter()==============================================
#region创建数据适配器CreateAdapter(stringsql)
///
///创建数据适配器
///
///SQL,语句
///数据适配器实例
publicDbDataAdapterCreateAdapter(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
returnCreateAdapter(sql,CommandType.Text,parameters);
}
///
///创建数据适配器
///
///SQL语句
///命令类型
///数据适配器实例
publicDbDataAdapterCreateAdapter(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
returnCreateAdapter(sql,cmdtype,parameters);
}
///
///创建数据适配器
///
///数据库连接字符串
///SQL语句
///命令类型
///参数
///数据适配器实例
publicDbDataAdapterCreateAdapter(stringsql,CommandTypecmdtype,DbParameter[]parameters)
{
DbConnection_connection=CreateConnection();
DbCommand_command=GetDbProviderFactory().CreateCommand();
_command.Connection=_connection;
_command.CommandText=sql;
_command.CommandType=cmdtype;
if(parameters!=null&¶meters.Length>0)
{
foreach(DbParameter_paraminparameters)
{
_command.Parameters.Add(_param);
}
}
DbDataAdapterda=GetDbProviderFactory().CreateDataAdapter();
da.SelectCommand=_command;
returnda;
}
#endregion
//=========================================CreateParameter===================================
#region生成参数publicoverrideSqlParameterCreateParameter(stringfield,stringdbtype,stringvalue)
///
///创建参数
///
///参数字段
///参数类型
///参数值
///
publicDbParameterCreateParameter(stringfield,stringdbtype,stringvalue)
{
DbParameterp=GetDbProviderFactory().CreateParameter();
p.ParameterName=field;
p.Value=value;
returnp;
}
#endregion
//======================================================ExecuteCommand()============================================
#region执行非查询语句,并返回受影响的记录行数ExecuteCommand(stringsql)
///
///执行非查询语句,并返回受影响的记录行数
///
///SQL语句
///受影响记录行数
publicintExecuteCommand(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
returnExecuteCommand(sql,CommandType.Text,parameters);
}
///
///执行非查询语句,并返回受影响的记录行数
///
///SQL语句
///命令类型
///受影响记录行数
publicintExecuteCommand(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
returnExecuteCommand(sql,CommandType.Text,parameters);
}
///
///执行非查询语句,并返回受影响的记录行数
///
///SQL语句
///参数
///受影响记录行数
publicintExecuteCommand(stringsql,DbParameter[]parameters)
{
returnExecuteCommand(sql,CommandType.Text,parameters);
}
///
///批量执行SQL语句
///
///SQL列表
///
publicboolExecuteCommand(ArrayListSqlList)
{
DbConnectioncon=CreateConnection();
con.Open();
booliserror=false;
stringstrerror="";
DbTransactionSqlTran=con.BeginTransaction();
try
{
for(inti=0;i
{
DbCommand_command=GetDbProviderFactory().CreateCommand();
_command.Connection=con;
_command.CommandText=SqlList[i].ToString();
_command.Transaction=SqlTran;
_command.ExecuteNonQuery();
}
}
catch(Exceptionex)
{
iserror=true;
strerror=ex.Message;
}
finally
{
if(iserror)
{
SqlTran.Rollback();
thrownewException(strerror);
}
else
{
SqlTran.Commit();
}
con.Close();
}
if(iserror)
{
returnfalse;
}
else
{
returntrue;
}
}
///
///执行非查询语句,并返回受影响的记录行数
///
///SQL语句
///命令类型
///参数
///受影响记录行数
publicintExecuteCommand(stringsql,CommandTypecmdtype,DbParameter[]parameters)
{
int_result=0;
DbCommand_command=CreateCommand(sql,cmdtype,parameters);
try
{
_command.Connection.Open();
_result=_command.ExecuteNonQuery();
}
catch(Exceptionex)
{
thrownewException(ex.Message);
}
finally
{
_command.Connection.Close();
}
return_result;
}
#endregion
//=====================================================ExecuteScalar()=============================================
#region执行非查询语句,并返回首行首列的值ExecuteScalar(stringsql)
///
///执行非查询语句,并返回首行首列的值
///
///SQL语句
///Object
publicobjectExecuteScalar(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
returnExecuteScalar(sql,CommandType.Text,parameters);
}
///
///执行非查询语句,并返回首行首列的值
///
///SQL语句
///命令类型
///Object
publicobjectExecuteScalar(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
returnExecuteScalar(sql,CommandType.Text,parameters);
}
///
///执行非查询语句,并返回首行首列的值
///
///SQL语句
///参数
///Object
publicobjectExecuteScalar(stringsql,DbParameter[]parameters)
{
returnExecuteScalar(sql,CommandType.Text,parameters);
}
///
///执行非查询语句,并返回首行首列的值
///
///SQL语句
///命令类型
///参数
///Object
publicobjectExecuteScalar(stringsql,CommandTypecmdtype,DbParameter[]parameters)
{
object_result=null;
DbCommand_command=CreateCommand(sql,cmdtype,parameters);
try
{
_command.Connection.Open();
_result=_command.ExecuteScalar();
}
catch
{
throw;
}
finally
{
_command.Connection.Close();
}
return_result;
}
#endregion
//=====================================================ExecuteReader()=============================================
#region执行查询，并以DataReader返回结果集ExecuteReader(stringsql)
///
///执行查询，并以DataReader返回结果集
///
///SQL语句
///IDataReader
publicDbDataReaderExecuteReader(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
returnExecuteReader(sql,CommandType.Text,parameters);
}
///
///执行查询，并以DataReader返回结果集
///
///SQL语句
///命令类型
///IDataReader
publicDbDataReaderExecuteReader(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
returnExecuteReader(sql,CommandType.Text,parameters);
}
///
///执行查询，并以DataReader返回结果集
///
///SQL语句
///参数
///IDataReader
publicDbDataReaderExecuteReader(stringsql,DbParameter[]parameters)
{
returnExecuteReader(sql,CommandType.Text,parameters);
}
///
///执行查询，并以DataReader返回结果集
///
///SQL语句
///命令类型
///参数
///IDataReader
publicDbDataReaderExecuteReader(stringsql,CommandTypecmdtype,DbParameter[]parameters)
{
DbDataReader_result;
DbCommand_command=CreateCommand(sql,cmdtype,parameters);
try
{
_command.Connection.Open();
_result=_command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
throw;
}
finally
{
}
return_result;
}
#endregion
//=====================================================GetDataSet()================================================
#region执行查询，并以DataSet返回结果集GetDataSet(stringsql)
///
///执行查询，并以DataSet返回结果集
///
///SQL语句
///DataSet
publicDataSetGetDataSet(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
returnGetDataSet(sql,CommandType.Text,parameters);
}
///
///执行查询，并以DataSet返回结果集
///
///SQL语句
///命令类型
///DataSet
publicvirtualDataSetGetDataSet(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
returnGetDataSet(sql,CommandType.Text,parameters);
}
///
///执行查询，并以DataSet返回结果集
///
///SQL语句
///参数
///DataSet
publicvirtualDataSetGetDataSet(stringsql,DbParameter[]parameters)
{
returnGetDataSet(sql,CommandType.Text,parameters);
}
///
///执行查询，并以DataSet返回结果集
///
///SQL语句
///命令类型
///参数
///DataSet
publicvirtualDataSetGetDataSet(stringsql,CommandTypecmdtype,DbParameter[]parameters)
{
DataSet_result=newDataSet();
IDataAdapter_dataAdapter=CreateAdapter(sql,cmdtype,parameters);
try
{
_dataAdapter.Fill(_result);
}
catch
{
throw;
}
finally
{
}
return_result;
}
///
///执行查询,并以DataSet返回指定记录的结果集
///
///SQL语句
///开始索引
///显示记录
///DataSet
publicvirtualDataSetGetDataSet(stringsql,intStartIndex,intRecordCount)
{
returnGetDataSet(sql,StartIndex,RecordCount);
}
#endregion
//=====================================================GetDataView()===============================================
#region执行查询，并以DataView返回结果集GetDataView(stringsql)
///
///执行查询，并以DataView返回结果集
///
///SQL语句
///命令类型
///参数
///DataView
publicDataViewGetDataView(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
DataViewdv=GetDataSet(sql,CommandType.Text,parameters).Tables[0].DefaultView;
returndv;
}
///
///执行查询，并以DataView返回结果集
///
///SQL语句
///命令类型
///参数
///DataView
publicDataViewGetDataView(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
DataViewdv=GetDataSet(sql,cmdtype,parameters).Tables[0].DefaultView;
returndv;
}
///
///执行查询，并以DataView返回结果集
///
///SQL语句
///命令类型
///参数
///DataView
publicDataViewGetDataView(stringsql,DbParameter[]parameters)
{
DataViewdv=GetDataSet(sql,CommandType.Text,parameters).Tables[0].DefaultView;
returndv;
}
///
///执行查询，并以DataView返回结果集
///
///SQL语句
///命令类型
///参数
///DataView
publicDataViewGetDataView(stringsql,CommandTypecmdtype,DbParameter[]parameters)
{
DataViewdv=GetDataSet(sql,cmdtype,parameters).Tables[0].DefaultView;
returndv;
}
///
///执行查询,并以DataView返回指定记录的结果集
///
///SQL语句
///开始索引
///显示记录
///DataView
publicDataViewGetDataView(stringsql,intStartIndex,intRecordCount)
{
returnGetDataSet(sql,StartIndex,RecordCount).Tables[0].DefaultView;
}
#endregion
//=====================================================GetDataTable()==============================================
#region执行查询，并以DataTable返回结果集GetDataTable(stringsql)
///
///执行查询，并以DataTable返回结果集
///
///SQL语句
///命令类型
///参数
///DataTable
publicDataTableGetDataTable(stringsql)
{
DbParameter[]parameters=newDbParameter[0];
DataTabledt=GetDataSet(sql,CommandType.Text,parameters).Tables[0];
returndt;
}
///
///执行查询，并以DataTable返回结果集
///
///SQL语句
///命令类型
///参数
///DataTable
publicDataTableGetDataTable(stringsql,CommandTypecmdtype)
{
DbParameter[]parameters=newDbParameter[0];
DataTabledt=GetDataSet(sql,cmdtype,parameters).Tables[0];
returndt;
}
///
///执行查询，并以DataTable返回结果集
///
///SQL语句
///命令类型
///参数
///DataTable
publicDataTableGetDataTable(stringsql,DbParameter[]parameters)
{
DataTabledt=GetDataSet(sql,CommandType.Text,parameters).Tables[0];
returndt;
}
///
///执行查询，并以DataTable返回结果集
///
///SQL语句
///命令类型
///参数
///DataTable
publicDataTableGetDataTable(stringsql,CommandTypecmdtype,DbParameter[]parameters)
{
DataTabledt=GetDataSet(sql,cmdtype,parameters).Tables[0];
returndt;
}
///
///执行查询,并以DataTable返回指定记录的结果集
///
///SQL语句
///开始索引
///显示记录
///DataTable
publicDataTableGetDataTable(stringsql,intStartIndex,intRecordCount)
{
returnGetDataSet(sql,StartIndex,RecordCount).Tables[0];
}
///
///执行查询,返回以空行填充的指定条数记录集
///
///SQL语句
///显示记录条数
///DataTable
publicDataTableGetDataTable(stringsql,intSizeCount)
{
DataTabledt=GetDataSet(sql).Tables[0];
intb=SizeCount-dt.Rows.Count;
if(dt.Rows.Count
{
for(inti=0;i
{
DataRowdr=dt.NewRow();
dt.Rows.Add(dr);
}
}
returndt;
}
#endregion
}
} |
|
|
|
|
|
|
|
|
|
|
|
