爱华网usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Data.OleDb;
usingSystem.Data.SqlClient;
namespaceGeneralLibrary.DbAccess
{
///<summary>数据库访问，支持SQLServer、Access数据库。</summary>
publicclassSQLHelper
{
privateIDbCommand_DbCommand;
privateIDbDataAdapter_DbDataAdapter;
privateIDbTransaction_DbTransaction;
///<summary>数据库访问类。</summary>
publicSQLHelper()
{
if(DbConfig.DbConnection==""||DbConfig.DbConnection==null)
{
thrownewException("链接字符串不能为空！");
}
switch(DbConfig.DbType)
{
caseDbType.Access:
this._DbCommand=newOleDbCommand();
this._DbCommand.Connection=newOleDbConnection(DbConfig.DbConnection);
this._DbDataAdapter=newOleDbDataAdapter();
break;
caseDbType.SQLServer:
this._DbCommand=newSqlCommand();
this._DbCommand.Connection=newSqlConnection(DbConfig.DbConnection);
this._DbDataAdapter=newSqlDataAdapter();
break;
}
}
///<summary>数据库访问类。</summary>
publicSQLHelper(stringstrConn,DbTypedbtype)
{
switch(dbtype)
{
caseDbType.Access:
this._DbCommand=newOleDbCommand();
this._DbCommand.Connection=newOleDbConnection(strConn);
this._DbDataAdapter=newOleDbDataAdapter();
break;
caseDbType.SQLServer:
this._DbCommand=newSqlCommand();
this._DbCommand.Connection=newSqlConnection(strConn);
this._DbDataAdapter=newSqlDataAdapter();
break;
}
}
///<summary>打开数据库连接。</summary>
privatevoidOpenConnection()
{
try
{
if(this._DbCommand.Connection.State==ConnectionState.Closed)
{
this._DbCommand.Connection.Open();
}
}
catch(Exceptiondbex)
{
thrownewException(dbex.Message);
}
}
///<summary>关闭数据库连接。</summary>
privatevoidCloseConnection()
{
if(this._DbCommand.Connection.State==ConnectionState.Open)
{
this._DbCommand.Connection.Close();
}
if(this._DbCommand!=null)
{
this._DbCommand.Dispose();
}
}
///<summary>执行SQL语句。</summary>
///<paramname="cmdText">SQL语句</param>
///<returns>返回影响行数</returns>
publicintExecuteSql(stringcmdText)
{
try
{
this._DbCommand.CommandText=cmdText;
this.OpenConnection();
returnthis._DbCommand.ExecuteNonQuery();
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
finally
{
this.CloseConnection();
}
}
///<summary>执行SQL语句。</summary>
///<paramname="cmdText">SQL语句</param>
///<paramname="cmdParameters">@参数</param>
///<returns>返回影响行数</returns>
publicintExecuteSql(stringcmdText,IDataParameter[]cmdParameters)
{
try
{
this._DbCommand.CommandText=cmdText;
foreach(IDataParameterparmincmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this.OpenConnection();
returnthis._DbCommand.ExecuteNonQuery();
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
finally
{
this.CloseConnection();
}
}
///<summary>执行SQL语句。</summary>
///<paramname="cmdText">SQL语句</param>
///<paramname="dt">返回查询结果</param>
publicvoidExecuteSql(stringcmdText,outDataTabledt)
{
try
{
this._DbCommand.CommandText=cmdText;
this._DbDataAdapter.SelectCommand=this._DbCommand;
DataSetds=newDataSet();
this._DbDataAdapter.Fill(ds);
dt=ds.Tables[0];
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
}
///<summary>执行SQL语句。</summary>
///<paramname="cmdText">SQL语句</param>
///<paramname="cmdParameters">@参数</param>
///<paramname="dt">返回查询结果</param>
publicvoidExecuteSql(stringcmdText,IDataParameter[]cmdParameters,outDataTabledt)
{
try
{
this._DbCommand.CommandText=cmdText;
foreach(IDataParameterparmincmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this._DbDataAdapter.SelectCommand=this._DbCommand;
DataSetds=newDataSet();
this._DbDataAdapter.Fill(ds);
dt=ds.Tables[0];
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
}
///<summary>执行存储过程。</summary>
///<paramname="procName">存储过程名</param>
///<returns>返回影响行数</returns>
publicintExecuteProc(stringprocName)
{
try
{
this._DbCommand.CommandText=procName;
this._DbCommand.CommandType=CommandType.StoredProcedure;
this.OpenConnection();
returnthis._DbCommand.ExecuteNonQuery();
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
finally
{
this.CloseConnection();
}
}
///<summary>执行存储过程。</summary>
///<paramname="procName">存储过程名</param>
///<paramname="cmdParameters">@参数</param>
///<returns>返回影响行数</returns>
publicintExecuteProc(stringprocName,IDataParameter[]cmdParameters)
{
try
{
this._DbCommand.CommandText=procName;
this._DbCommand.CommandType=CommandType.StoredProcedure;
foreach(IDataParameterparmincmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this.OpenConnection();
returnthis._DbCommand.ExecuteNonQuery();
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
finally
{
this.CloseConnection();
}
}
///<summary>执行存储过程。</summary>
///<paramname="procName">存储过程名</param>
///<paramname="dt">返回查询结果</param>
publicvoidExecuteProc(stringprocName,outDataTabledt)
{
try
{
this._DbCommand.CommandText=procName;
this._DbCommand.CommandType=CommandType.StoredProcedure;
this._DbDataAdapter.SelectCommand=this._DbCommand;
DataSetds=newDataSet();
this._DbDataAdapter.Fill(ds);
dt=ds.Tables[0];
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
}
///<summary>执行存储过程。</summary>
///<paramname="procName">存储过程名</param>
///<paramname="cmdParameters">@参数</param>
///<paramname="dt">返回查询结果</param>
publicvoidExecuteProc(stringprocName,IDataParameter[]cmdParameters,outDataTabledt)
{
try
{
this._DbCommand.CommandText=procName;
this._DbCommand.CommandType=CommandType.StoredProcedure;
foreach(IDataParameterparmincmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this._DbDataAdapter.SelectCommand=this._DbCommand;
DataSetds=newDataSet();
this._DbDataAdapter.Fill(ds);
dt=ds.Tables[0];
}
catch(Exceptionex)
{
this.CloseConnection();
thrownewException(ex.Message);
}
}
///<summary>执行事务。</summary>
///<paramname="cmdTexts">SQL语句</param>
///<returns></returns>
publicboolExecuteTransaction(string[]cmdTexts)
{
try
{
this.OpenConnection();
this._DbTransaction=this._DbCommand.Connection.BeginTransaction();
this._DbCommand.Transaction=this._DbTransaction;
foreach(stringcmdTextincmdTexts)
{
this._DbCommand.CommandText=cmdText;
this._DbCommand.ExecuteNonQuery();
}
this._DbTransaction.Commit();
}
catch
{
this._DbTransaction.Rollback();
this.CloseConnection();
returnfalse;//执行失败
}
returntrue;//执行成功
}
///<summary>执行事务。</summary>
///<paramname="cmdTexts">SQL语句。</param>
///<paramname="lstParameter">@参数</param>
///<paramname="count">次数</param>
///<returns></returns>
publicboolExecuteTransaction(string[]cmdTexts,List<IDataParameter[]>lstParameter,intcount)
{
try
{
this.OpenConnection();
this._DbTransaction=this._DbCommand.Connection.BeginTransaction();
this._DbCommand.Transaction=this._DbTransaction;
for(inti=0;i<count;i++)
{
this._DbCommand.CommandText=cmdTexts[i];
foreach(IDataParameterparminlstParameter[i])
{
this._DbCommand.Parameters.Add(parm);
}
this._DbCommand.ExecuteNonQuery();
}
this._DbTransaction.Commit();
}
catch
{
this._DbTransaction.Rollback();
this.CloseConnection();
returnfalse;//执行失败
}
returntrue;
}
}
}
