using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace GeneralLibrary.DbAccess
{
/// <summary>數(shù)據(jù)庫訪問，支持 SQL Server、Access 數(shù)據(jù)庫。</summary>
public class SQLHelper
{
private IDbCommand _DbCommand;
private IDbDataAdapter _DbDataAdapter;
private IDbTransaction _DbTransaction;
/// <summary>數(shù)據(jù)庫訪問類。</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>數(shù)據(jù)庫訪問類。</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>打開數(shù)據(jù)庫連接。</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>關(guān)閉數(shù)據(jù)庫連接。</summary>
private void CloseConnection()
{
if (this._DbCommand.Connection.State == ConnectionState.Open)
{
this._DbCommand.Connection.Close();
}
if (this._DbCommand != null)
{
this._DbCommand.Dispose();
}
}
/// <summary>執(zhí)行 SQL 語句。</summary>
/// <param name="cmdText">SQL 語句</param>
/// <returns>返回影響行數(shù)</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>執(zhí)行 SQL 語句。</summary>
/// <param name="cmdText">SQL 語句</param>
/// <param name="cmdParameters">@ 參數(shù)</param>
/// <returns>返回影響行數(shù)</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>執(zhí)行 SQL 語句。</summary>
/// <param name="cmdText">SQL 語句</param>
/// <param name="dt">返回查詢結(jié)果</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>執(zhí)行 SQL 語句。</summary>
/// <param name="cmdText">SQL 語句</param>
/// <param name="cmdParameters">@ 參數(shù)</param>
/// <param name="dt">返回查詢結(jié)果</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>執(zhí)行存儲過程。</summary>
/// <param name="procName">存儲過程名</param>
/// <returns>返回影響行數(shù)</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>執(zhí)行存儲過程。</summary>
/// <param name="procName">存儲過程名</param>
/// <param name="cmdParameters">@ 參數(shù)</param>
/// <returns>返回影響行數(shù)</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>執(zhí)行存儲過程。</summary>
/// <param name="procName">存儲過程名</param>
/// <param name="dt">返回查詢結(jié)果</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>執(zhí)行存儲過程。</summary>
/// <param name="procName">存儲過程名</param>
/// <param name="cmdParameters">@ 參數(shù)</param>
/// <param name="dt">返回查詢結(jié)果</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>執(zhí)行事務(wù)。</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;//執(zhí)行失敗
}
return true;//執(zhí)行成功
}
/// <summary>執(zhí)行事務(wù)。</summary>
/// <param name="cmdTexts">SQL 語句。</param>
/// <param name="lstParameter">@ 參數(shù)</param>
/// <param name="count">次數(shù)</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;//執(zhí)行失敗
}
return true;
}
}
}
