|
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; } } } |
|
|
