|
using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; namespace GeneralLibrary.DbAccess { /// <summary>數(shù)據(jù)庫(kù)訪問(wèn)，支持 SQL Server,、Access 數(shù)據(jù)庫(kù),。</summary> public class SQLHelper { private IDbCommand _DbCommand; private IDbDataAdapter _DbDataAdapter; private IDbTransaction _DbTransaction; /// <summary>數(shù)據(jù)庫(kù)訪問(wèn)類,。</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ù)庫(kù)訪問(wèn)類,。</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>打開(kāi)數(shù)據(jù)庫(kù)連接,。</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ù)庫(kù)連接。</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 語(yǔ)句,。</summary> /// <param name="cmdText">SQL 語(yǔ)句</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 語(yǔ)句,。</summary> /// <param name="cmdText">SQL 語(yǔ)句</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 語(yǔ)句。</summary> /// <param name="cmdText">SQL 語(yǔ)句</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 語(yǔ)句,。</summary> /// <param name="cmdText">SQL 語(yǔ)句</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í)行存儲(chǔ)過(guò)程。</summary> /// <param name="procName">存儲(chǔ)過(guò)程名</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í)行存儲(chǔ)過(guò)程,。</summary> /// <param name="procName">存儲(chǔ)過(guò)程名</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í)行存儲(chǔ)過(guò)程。</summary> /// <param name="procName">存儲(chǔ)過(guò)程名</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í)行存儲(chǔ)過(guò)程,。</summary> /// <param name="procName">存儲(chǔ)過(guò)程名</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 語(yǔ)句</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 語(yǔ)句。</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; } } } |
|
|
來(lái)自： liuyci > 《我的圖書(shū)館》
