c#NET万能数据库访问封装类ACCESSSQLServerOracle.docx
《c#NET万能数据库访问封装类ACCESSSQLServerOracle.docx》由会员分享,可在线阅读,更多相关《c#NET万能数据库访问封装类ACCESSSQLServerOracle.docx(37页珍藏版)》请在冰豆网上搜索。
c#NET万能数据库访问封装类ACCESSSQLServerOracle
usingSystem;
usingSystem.Collections;
usingSystem.Collections.Specialized;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Data.OleDb;
usingSystem.Data.OracleClient;
usingSystem.Configuration;
usingSystem.Reflection;
namespaceSystemFramework.DAL
{
/**////
///Allrightsreserved
///数据访问基础类
///用户可以修改满足自己项目的需要。
///
publicclassDataBaseLayer
{
//数据库连接字符串(web.config来配置)
//
privatestringconnectionString;
publicstringConntionString
{
get
{
returnconnectionString;
}
set
{
connectionString=value;
}
}
publicDataBaseLayer(stringstrConnect,stringdataType)
{
this.ConntionString=strConnect;
this.DbType=dataType;
}
publicDataBaseLayer()
{
this.connectionString=ConfigurationSettings.AppSettings["ConnectionString"];
this.dbType=ConfigurationSettings.AppSettings["DataType"];
//this.connectionString="datasource=192.168.1.43;userid=sa;pwd=sa;database=temphrdb";
//this.dbType="SqlServer";
}
/**////
///数据库类型
///
privatestringdbType;
publicstringDbType
{
get
{
if(dbType==string.Empty||dbType==null)
{
return"Access";
}
else
{
returndbType;
}
}
set
{
if(value!
=string.Empty&&value!
=null)
{
dbType=value;
}
if(dbType==string.Empty||dbType==null)
{
dbType=ConfigurationSettings.AppSettings["DataType"];
}
if(dbType==string.Empty||dbType==null)
{
dbType="Access";
}
}
}
转换参数#region转换参数
privateSystem.Data.IDbDataParameteriDbPara(stringParaName,stringDataType)
{
switch(this.DbType)
{
case"SqlServer":
returnGetSqlPara(ParaName,DataType);
case"Oracle":
returnGetOleDbPara(ParaName,DataType);
case"Access":
returnGetOleDbPara(ParaName,DataType);
default:
returnGetSqlPara(ParaName,DataType);
}
}
privateSystem.Data.SqlClient.SqlParameterGetSqlPara(stringParaName,stringDataType)
{
switch(DataType)
{
case"Decimal":
returnnewSystem.Data.SqlClient.SqlParameter(ParaName,System.Data.SqlDbType.Decimal);
case"Varchar":
returnnewSystem.Data.SqlClient.SqlParameter(ParaName,System.Data.SqlDbType.VarChar);
case"DateTime":
returnnewSystem.Data.SqlClient.SqlParameter(ParaName,System.Data.SqlDbType.DateTime);
case"Iamge":
returnnewSystem.Data.SqlClient.SqlParameter(ParaName,System.Data.SqlDbType.Image);
case"Int":
returnnewSystem.Data.SqlClient.SqlParameter(ParaName,System.Data.SqlDbType.Int);
case"Text":
returnnewSystem.Data.SqlClient.SqlParameter(ParaName,System.Data.SqlDbType.NText);
default:
returnnewSystem.Data.SqlClient.SqlParameter(ParaName,System.Data.SqlDbType.VarChar);
}
}
privateSystem.Data.OracleClient.OracleParameterGetOraclePara(stringParaName,stringDataType)
{
switch(DataType)
{
case"Decimal":
returnnewSystem.Data.OracleClient.OracleParameter(ParaName,System.Data.OracleClient.OracleType.Double);
case"Varchar":
returnnewSystem.Data.OracleClient.OracleParameter(ParaName,System.Data.OracleClient.OracleType.VarChar);
case"DateTime":
returnnewSystem.Data.OracleClient.OracleParameter(ParaName,System.Data.OracleClient.OracleType.DateTime);
case"Iamge":
returnnewSystem.Data.OracleClient.OracleParameter(ParaName,System.Data.OracleClient.OracleType.BFile);
case"Int":
returnnewSystem.Data.OracleClient.OracleParameter(ParaName,System.Data.OracleClient.OracleType.Int32);
case"Text":
returnnewSystem.Data.OracleClient.OracleParameter(ParaName,System.Data.OracleClient.OracleType.LongVarChar);
default:
returnnewSystem.Data.OracleClient.OracleParameter(ParaName,System.Data.OracleClient.OracleType.VarChar);
}
}
privateSystem.Data.OleDb.OleDbParameterGetOleDbPara(stringParaName,stringDataType)
{
switch(DataType)
{
case"Decimal":
returnnewSystem.Data.OleDb.OleDbParameter(ParaName,System.Data.DbType.Decimal);
case"Varchar":
returnnewSystem.Data.OleDb.OleDbParameter(ParaName,System.Data.DbType.String);
case"DateTime":
returnnewSystem.Data.OleDb.OleDbParameter(ParaName,System.Data.DbType.DateTime);
case"Iamge":
returnnewSystem.Data.OleDb.OleDbParameter(ParaName,System.Data.DbType.Binary);
case"Int":
returnnewSystem.Data.OleDb.OleDbParameter(ParaName,System.Data.DbType.Int32);
case"Text":
returnnewSystem.Data.OleDb.OleDbParameter(ParaName,System.Data.DbType.String);
default:
returnnewSystem.Data.OleDb.OleDbParameter(ParaName,System.Data.DbType.String);
}
}
#endregion
创建Connection和Command#region创建Connection和Command
privateIDbConnectionGetConnection()
{
switch(this.DbType)
{
case"SqlServer":
returnnewSystem.Data.SqlClient.SqlConnection(this.ConntionString);
case"Oracle":
returnnewSystem.Data.OracleClient.OracleConnection(this.ConntionString);
case"Access":
returnnewSystem.Data.OleDb.OleDbConnection(this.ConntionString);
default:
returnnewSystem.Data.SqlClient.SqlConnection(this.ConntionString);
}
}
privateIDbCommandGetCommand(stringSql,IDbConnectioniConn)
{
switch(this.DbType)
{
case"SqlServer":
returnnewSystem.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn);
case"Oracle":
returnnewSystem.Data.OracleClient.OracleCommand(Sql,(OracleConnection)iConn);
case"Access":
returnnewSystem.Data.OleDb.OleDbCommand(Sql,(OleDbConnection)iConn);
default:
returnnewSystem.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn);
}
}
privateIDbCommandGetCommand()
{
switch(this.DbType)
{
case"SqlServer":
returnnewSystem.Data.SqlClient.SqlCommand();
case"Oracle":
returnnewSystem.Data.OracleClient.OracleCommand();
case"Access":
returnnewSystem.Data.OleDb.OleDbCommand();
default:
returnnewSystem.Data.SqlClient.SqlCommand();
}
}
privateIDataAdapterGetAdapater(stringSql,IDbConnectioniConn)
{
switch(this.DbType)
{
case"SqlServer":
returnnewSystem.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn);
case"Oracle":
returnnewSystem.Data.OracleClient.OracleDataAdapter(Sql,(OracleConnection)iConn);
case"Access":
returnnewSystem.Data.OleDb.OleDbDataAdapter(Sql,(OleDbConnection)iConn);
default:
returnnewSystem.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn);;
}
}
privateIDataAdapterGetAdapater()
{
switch(this.DbType)
{
case"SqlServer":
returnnewSystem.Data.SqlClient.SqlDataAdapter();
case"Oracle":
returnnewSystem.Data.OracleClient.OracleDataAdapter();
case"Access":
returnnewSystem.Data.OleDb.OleDbDataAdapter();
default:
returnnewSystem.Data.SqlClient.SqlDataAdapter();
}
}
privateIDataAdapterGetAdapater(IDbCommandiCmd)
{
switch(this.DbType)
{
case"SqlServer":
returnnewSystem.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd);
case"Oracle":
returnnewSystem.Data.OracleClient.OracleDataAdapter((OracleCommand)iCmd);
case"Access":
returnnewSystem.Data.OleDb.OleDbDataAdapter((OleDbCommand)iCmd);
default:
returnnewSystem.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd);
}
}
#endregion
执行简单SQL语句#region执行简单SQL语句
/**////
///执行SQL语句,返回影响的记录数
///
///SQL语句
///影响的记录数
publicintExecuteSql(stringSqlString)
{
using(System.Data.IDbConnectioniConn=this.GetConnection())
{
using(System.Data.IDbCommandiCmd=GetCommand(SqlString,iConn))
{
iConn.Open();
try
{
introws=iCmd.ExecuteNonQuery();
returnrows;
}
catch(System.ExceptionE)
{
thrownewException(E.Message);
}
finally
{
if(iConn.State!
=ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**////
///执行多条SQL语句,实现数据库事务。
///
///多条SQL语句
publicvoidExecuteSqlTran(ArrayListSQLStringList)
{
using(System.Data.IDbConnectioniConn=this.GetConnection())
{
iConn.Open();
using(System.Data.IDbCommandiCmd=GetCommand())
{
iCmd.Connection=iConn;
using(System.Data.IDbTransactioniDbTran=iConn.BeginTransaction())
{
iCmd.Transaction=iDbTran;
try
{
for(intn=0;n{
stringstrsql=SQLStringList[n].ToString();
if(strsql.Trim().Length>1)
{
iCmd.CommandText=strsql;
iCmd.ExecuteNonQuery();
}
}
iDbTran.Commit();
}
catch(System.ExceptionE)
{
iDbTran.Rollback();
thrownewException(E.Message);
}
finally
{
if(iConn.State!
=ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
}
/**////
///执行带一个存储过程参数的的SQL语句。
///
///SQL语句
///参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加
///影响的记录数
publicintExecuteSql(stringSqlString,stringcontent)
{
using(System.Data.IDbConnectioniConn=this.GetConnection())
{
using(System.Data.IDbCommandiCmd=GetCommand(SqlString,iConn))
{
System.Data.IDataParametermyParameter=this.iDbPara("@content","Text");
myParameter.Value=content;
iCmd.Parameters.Add(myParameter);
iConn.Open();
try
{
introws=iCmd.ExecuteNonQuery();
returnrows;
}
catch(System.Exceptione)
{
thrownewException(e.Message);
}
finally
{
if(iConn.State!
=ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**////
///向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
///
///SQL语句
///图像字节,数据库的字段类型为image的情况
///影响的记录数
publicintExecuteSqlInsertImg(stringSqlString,byte[]fs)