using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace GeneralLibrary.DbAccess
{
/// <summary>数据库访问,支持 SQL Server、Access 数据库。</summary>
public class SQLHelper
{
private IDbCommand _DbCommand;
private IDbDataAdapter _DbDataAdapter;
private IDbTransaction _DbTransaction;
/// <summary>数据库访问类。</summary>
public SQLHelper()
{
if (DbConfig.DbConnection == "" || DbConfig.DbConnection == null)
{
throw new Exception("链接字符串不能为空!");
}
switch (DbConfig.DbType)
{
case DbType.Access:
this._DbCommand = new OleDbCommand();
this._DbCommand.Connection = new OleDbConnection(DbConfig.DbConnection);
this._DbDataAdapter = new OleDbDataAdapter();
break;
case DbType.SQLServer:
this._DbCommand = new SqlCommand();
this._DbCommand.Connection = new SqlConnection(DbConfig.DbConnection);
this._DbDataAdapter = new SqlDataAdapter();
break;
}
}
/// <summary>数据库访问类。</summary>
public SQLHelper(string strConn, DbType dbtype)
{
switch (dbtype)
{
case DbType.Access:
this._DbCommand = new OleDbCommand();
this._DbCommand.Connection = new OleDbConnection(strConn);
this._DbDataAdapter = new OleDbDataAdapter();
break;
case DbType.SQLServer:
this._DbCommand = new SqlCommand();
this._DbCommand.Connection = new SqlConnection(strConn);
this._DbDataAdapter = new SqlDataAdapter();
break;
}
}
/// <summary>打开数据库连接。</summary>
private void OpenConnection()
{
try
{
if (this._DbCommand.Connection.State == ConnectionState.Closed)
{
this._DbCommand.Connection.Open();
}
}
catch (Exception dbex)
{
throw new Exception(dbex.Message);
}
}
/// <summary>关闭数据库连接。</summary>
private void CloseConnection()
{
if (this._DbCommand.Connection.State == ConnectionState.Open)
{
this._DbCommand.Connection.Close();
}
if (this._DbCommand != null)
{
this._DbCommand.Dispose();
}
}
/// <summary>执行 SQL 语句。</summary>
/// <param name="cmdText">SQL 语句</param>
/// <returns>返回影响行数</returns>
public int ExecuteSql(string cmdText)
{
try
{
this._DbCommand.CommandText = cmdText;
this.OpenConnection();
return this._DbCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
finally
{
this.CloseConnection();
}
}
/// <summary>执行 SQL 语句。</summary>
/// <param name="cmdText">SQL 语句</param>
/// <param name="cmdParameters">@ 参数</param>
/// <returns>返回影响行数</returns>
public int ExecuteSql(string cmdText, IDataParameter[] cmdParameters)
{
try
{
this._DbCommand.CommandText = cmdText;
foreach (IDataParameter parm in cmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this.OpenConnection();
return this._DbCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
finally
{
this.CloseConnection();
}
}
/// <summary>执行 SQL 语句。</summary>
/// <param name="cmdText">SQL 语句</param>
/// <param name="dt">返回查询结果</param>
public void ExecuteSql(string cmdText, out DataTable dt)
{
try
{
this._DbCommand.CommandText = cmdText;
this._DbDataAdapter.SelectCommand = this._DbCommand;
DataSet ds = new DataSet();
this._DbDataAdapter.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
}
/// <summary>执行 SQL 语句。</summary>
/// <param name="cmdText">SQL 语句</param>
/// <param name="cmdParameters">@ 参数</param>
/// <param name="dt">返回查询结果</param>
public void ExecuteSql(string cmdText, IDataParameter[] cmdParameters, out DataTable dt)
{
try
{
this._DbCommand.CommandText = cmdText;
foreach (IDataParameter parm in cmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this._DbDataAdapter.SelectCommand = this._DbCommand;
DataSet ds = new DataSet();
this._DbDataAdapter.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
}
/// <summary>执行存储过程。</summary>
/// <param name="procName">存储过程名</param>
/// <returns>返回影响行数</returns>
public int ExecuteProc(string procName)
{
try
{
this._DbCommand.CommandText = procName;
this._DbCommand.CommandType = CommandType.StoredProcedure;
this.OpenConnection();
return this._DbCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
finally
{
this.CloseConnection();
}
}
/// <summary>执行存储过程。</summary>
/// <param name="procName">存储过程名</param>
/// <param name="cmdParameters">@ 参数</param>
/// <returns>返回影响行数</returns>
public int ExecuteProc(string procName, IDataParameter[] cmdParameters)
{
try
{
this._DbCommand.CommandText = procName;
this._DbCommand.CommandType = CommandType.StoredProcedure;
foreach (IDataParameter parm in cmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this.OpenConnection();
return this._DbCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
finally
{
this.CloseConnection();
}
}
/// <summary>执行存储过程。</summary>
/// <param name="procName">存储过程名</param>
/// <param name="dt">返回查询结果</param>
public void ExecuteProc(string procName, out DataTable dt)
{
try
{
this._DbCommand.CommandText = procName;
this._DbCommand.CommandType = CommandType.StoredProcedure;
this._DbDataAdapter.SelectCommand = this._DbCommand;
DataSet ds = new DataSet();
this._DbDataAdapter.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
}
/// <summary>执行存储过程。</summary>
/// <param name="procName">存储过程名</param>
/// <param name="cmdParameters">@ 参数</param>
/// <param name="dt">返回查询结果</param>
public void ExecuteProc(string procName, IDataParameter[] cmdParameters, out DataTable dt)
{
try
{
this._DbCommand.CommandText = procName;
this._DbCommand.CommandType = CommandType.StoredProcedure;
foreach (IDataParameter parm in cmdParameters)
{
this._DbCommand.Parameters.Add(parm);
}
this._DbDataAdapter.SelectCommand = this._DbCommand;
DataSet ds = new DataSet();
this._DbDataAdapter.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
this.CloseConnection();
throw new Exception(ex.Message);
}
}
/// <summary>执行事务。</summary>
/// <param name="cmdTexts">SQL 语句</param>
/// <returns></returns>
public bool ExecuteTransaction(string[] cmdTexts)
{
try
{
this.OpenConnection();
this._DbTransaction = this._DbCommand.Connection.BeginTransaction();
this._DbCommand.Transaction = this._DbTransaction;
foreach (string cmdText in cmdTexts)
{
this._DbCommand.CommandText = cmdText;
this._DbCommand.ExecuteNonQuery();
}
this._DbTransaction.Commit();
}
catch
{
this._DbTransaction.Rollback();
this.CloseConnection();
return false;//执行失败
}
return true;//执行成功
}
/// <summary>执行事务。</summary>
/// <param name="cmdTexts">SQL 语句。</param>
/// <param name="lstParameter">@ 参数</param>
/// <param name="count">次数</param>
/// <returns></returns>
public bool ExecuteTransaction(string[] cmdTexts, List<IDataParameter[]> lstParameter, int count)
{
try
{
this.OpenConnection();
this._DbTransaction = this._DbCommand.Connection.BeginTransaction();
this._DbCommand.Transaction = this._DbTransaction;
for (int i = 0; i < count; i++)
{
this._DbCommand.CommandText = cmdTexts[i];
foreach (IDataParameter parm in lstParameter[i])
{
this._DbCommand.Parameters.Add(parm);
}
this._DbCommand.ExecuteNonQuery();
}
this._DbTransaction.Commit();
}
catch
{
this._DbTransaction.Rollback();
this.CloseConnection();
return false;//执行失败
}
return true;
}
}
}
联系客服