using System; using System.Collections.Generic; using System.Text; using System.Data; namespace Database ... { /**//// <summary> /// 访问接口类 /// </summary> abstract class DBOperator ...{ /**//// <summary> /// 得到数据库联接 /// </summary> public abstract IDbConnection Connection ...{ get; } /**//// <summary> /// 打开数据库联接 /// </summary> public abstract void Open(); /**//// <summary> /// 关闭数据库连接 /// </summary> public abstract void Close(); /**//// <summary> /// 开始一个事物 /// </summary> public abstract void BeginTrans(); /**//// <summary> /// 提交一个事物 /// </summary> public abstract void CommitTrans(); /**//// <summary> /// 事务回滚 /// </summary> public abstract void RollbackTrans(); /**//// <summary> /// 执行SQl函数,用與沒有儲存過程或者儲存過程沒有參數 /// </summary> /// <param name="strSql">SQL語句或儲存過程</param> /// <returns></returns> public abstract int exeSql(string strSql); /**//// <summary> /// 执行SQl函数,用與存在儲存過程的 /// </summary> /// <param name="strSql">存储过程名称</param> /// <param name="strParams">存储过程变量名</param> /// <param name="objValues">传输参数名称</param> /// <returns>执行行数</returns> public abstract int exeSql(string strSql, string[] strParams, object[] objValues); /**//// <summary> /// 返回SQL执行的数据,用與沒有儲存過程或者儲存過程沒有參數 /// </summary> /// <param name="QueryString">SQL語句或儲存過程</param> /// <returns>数据</returns> public abstract DataSet exeSqlForDataSet(string QueryString); /**//// <summary> /// 返回SQL执行的数据,用與存在儲存過程的 /// </summary> /// <param name="QueryString">执行的SQL语句</param> /// <param name="strParams">存储过程变量名</param> /// <param name="objValues">传输参数名称</param> /// <returns>数据</returns> public abstract DataSet exeSqlForDataSet(string QueryString, string[] strParams, object[] objValues); /**//// <summary> /// 返回错误信息 /// </summary> /// <param name="Err">错误</param> /// <returns>错误表</returns> public abstract DataSet DataError(Exception Err); }} using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; namespace Database ... { class OleDBOperator:DBOperator ...{ private OleDbConnection conn;//数据库连接 private OleDbTransaction trans;//事务处理类 private bool inTransaction = false;//指示当前是否正处于事务中 public override IDbConnection Connection ...{ get ...{ return this.conn; } } public OleDBOperator(string strConnection) ...{ this.conn = new OleDbConnection(strConnection); } public override void Open() ...{ if (conn.State.ToString().ToUpper() != "OPEN") ...{ this.conn.Open(); } } public override void Close() ...{ if (conn.State.ToString().ToUpper() == "OPEN") ...{ this.conn.Close(); } } public override void BeginTrans() ...{ trans = conn.BeginTransaction(); inTransaction = true; } public override void CommitTrans() ...{ trans.Commit(); inTransaction = false; } public override void RollbackTrans() ...{ trans.Rollback(); inTransaction = false; } public override int exeSql(string strSql) ...{ OleDbCommand cmd = new OleDbCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } cmd.CommandText = strSql; return cmd.ExecuteNonQuery(); } public override int exeSql(string strSql, string[] strParams, object[] objValues) ...{ OleDbCommand cmd = new OleDbCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } if ((strParams != null) && (strParams.Length != objValues.Length)) ...{ throw new Exception("查询参数和值不对应!"); } cmd.CommandText = strSql; if (strParams != null) ...{ cmd.CommandType = CommandType.StoredProcedure; for (int i = 0; i < strParams.Length; i++) ...{ cmd.Parameters.Add(new OleDbParameter(strParams[i], objValues[i])); } } return cmd.ExecuteNonQuery(); } public override DataSet exeSqlForDataSet(string QueryString) ...{ OleDbCommand cmd = new OleDbCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } DataSet ds = new DataSet(); OleDbDataAdapter ad = new OleDbDataAdapter(); cmd.CommandText = QueryString; ad.SelectCommand = cmd; ad.Fill(ds); return ds; } public override DataSet exeSqlForDataSet(string QueryString, string[] strParams, object[] objValues) ...{ OleDbCommand cmd = new OleDbCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } if ((strParams != null) && (strParams.Length != objValues.Length)) ...{ throw new Exception("查询参数和值不对应!"); } if (strParams != null) ...{ cmd.CommandType = CommandType.StoredProcedure; for (int i = 0; i < strParams.Length; i++) ...{ cmd.Parameters.Add(new OleDbParameter(strParams[i], objValues[i])); } } DataSet ds = new DataSet(); OleDbDataAdapter ad = new OleDbDataAdapter(); cmd.CommandText = QueryString; ad.SelectCommand = cmd; ad.Fill(ds); return ds; } public override DataSet DataError(Exception Err) ...{ DataSet ErrDataSet = new DataSet("Errors"); DataTable ErrDataTable = ErrDataSet.Tables.Add("Error"); ErrDataTable.Columns.Add("王康提醒您"); ErrDataTable.Rows.Add(new object[] ...{ Err.Message }); return ErrDataSet; } }} using System; using System.Data; using System.Data.SqlClient; namespace Database ... { class SqlDBOperator : DBOperator ...{ private SqlConnection conn;//数据库连接 private SqlTransaction trans;//事务处理类 private bool inTransaction = false;//指示当前是否正处于事务中 public override IDbConnection Connection ...{ get ...{ return this.conn; } } public SqlDBOperator(string strConnection) ...{ this.conn = new SqlConnection(strConnection); } public override void Open() ...{ if (conn.State.ToString().ToUpper() != "OPEN") ...{ this.conn.Open(); } } public override void Close() ...{ if (conn.State.ToString().ToUpper() == "OPEN") ...{ this.conn.Close(); } } public override void BeginTrans() ...{ trans = conn.BeginTransaction(); inTransaction = true; } public override void CommitTrans() ...{ trans.Commit(); inTransaction = false; } public override void RollbackTrans() ...{ trans.Rollback(); inTransaction = false; } public override int exeSql(string strSql) ...{ SqlCommand cmd = new SqlCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } cmd.CommandText = strSql; return cmd.ExecuteNonQuery(); } public override int exeSql(string strSql, string[] strParams, object[] objValues) ...{ SqlCommand cmd = new SqlCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } if ((strParams != null) && (strParams.Length != objValues.Length)) ...{ throw new Exception("查询参数和值不对应!"); } cmd.CommandText = strSql; if (strParams != null) ...{ cmd.CommandType = CommandType.StoredProcedure; for (int i = 0; i < strParams.Length; i++) ...{ cmd.Parameters.Add(new SqlParameter(strParams[i], objValues[i])); } } return cmd.ExecuteNonQuery(); } public override DataSet exeSqlForDataSet(string QueryString) ...{ SqlCommand cmd = new SqlCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } DataSet ds = new DataSet(); SqlDataAdapter ad = new SqlDataAdapter(); cmd.CommandText = QueryString; ad.SelectCommand = cmd; ad.Fill(ds); return ds; } public override DataSet exeSqlForDataSet(string QueryString, string[] strParams, object[] objValues) ...{ SqlCommand cmd = new SqlCommand(); cmd.Connection = this.conn; if (inTransaction) ...{ cmd.Transaction = trans; } if ((strParams != null) && (strParams.Length != objValues.Length)) ...{ throw new Exception("查询参数和值不对应!"); } if (strParams != null) ...{ cmd.CommandType = CommandType.StoredProcedure; for (int i = 0; i < strParams.Length; i++) ...{ cmd.Parameters.Add(new SqlParameter(strParams[i], objValues[i])); } } DataSet ds = new DataSet(); SqlDataAdapter ad = new SqlDataAdapter(); cmd.CommandText = QueryString; ad.SelectCommand = cmd; ad.Fill(ds); return ds; } public override DataSet DataError(Exception Err) ...{ DataSet ErrDataSet = new DataSet("Errors"); DataTable ErrDataTable = ErrDataSet.Tables.Add("Error"); ErrDataTable.Columns.Add("王康提醒您"); ErrDataTable.Rows.Add(new object[] ...{ Err.Message }); return ErrDataSet; } }} using System; using System.Collections.Generic; using System.Text; namespace Database ... { class DBOperatorFactory ...{ public static DBOperator GetDBOperator(string strConnection) ...{ if (strConnection.IndexOf("provider=") < 0) ...{ return new SqlDBOperator(strConnection); } else ...{ return new OleDBOperator(strConnection); } } }} using System; using System.Data; using System.Configuration; namespace Database ... { /**//// <summary> /// /// </summary> public class MODEL ...{ private string connstring; /**//// <summary> /// /// </summary> public string Connstring ...{ set ...{ connstring = value; } } /**//// <summary> /// 查询数据库操作 /// </summary> /// <param name="strSql">SQL语句,或存储过程名称</param> /// <returns>查询结果</returns> public DataSet GetData(string strSql) ...{ DataSet ds = new DataSet(); DBOperator db = DBOperatorFactory.GetDBOperator(connstring); db.Open(); db.BeginTrans(); try ...{ ds = db.exeSqlForDataSet(strSql); db.CommitTrans(); } catch (Exception Err) ...{ db.RollbackTrans(); ds = db.DataError(Err); } finally ...{ db.Close(); } return ds; } /**//// <summary> /// 查询数据库操作 /// </summary> /// <param name="strSql">SQL语句,或存储过程名称</param> /// <param name="strParams">输入存储过程的参数名称集</param> /// <param name="objValues">传入存储过程的参数</param> /// <returns>查询结果</returns> public DataSet GetData(string strSql, string[] strParams, object[] objValues) ...{ DataSet ds = new DataSet(); DBOperator db = DBOperatorFactory.GetDBOperator(connstring); db.Open(); db.BeginTrans(); try ...{ ds = db.exeSqlForDataSet(strSql, strParams, objValues); db.CommitTrans(); } catch (Exception Err) ...{ db.RollbackTrans(); ds = db.DataError(Err); } finally ...{ db.Close(); } return ds; } /**//// <summary> /// /// </summary> /// <param name="strSql"></param> /// <returns></returns> public int exeSql(string strSql) ...{ int reslut = -1; DBOperator db = DBOperatorFactory.GetDBOperator(connstring); db.Open(); db.BeginTrans(); try ...{ reslut = db.exeSql(strSql); db.CommitTrans(); } catch ...{ db.RollbackTrans(); } finally ...{ db.Close(); } return reslut; } /**//// <summary> /// 执行SQL语句或存储过程 /// </summary> /// <param name="strSql">SQL语句,或存储过程名称</param> /// <param name="strParams">输入存储过程的参数名称集</param> /// <param name="objValues">传入存储过程的参数</param> public int exeSql(string strSql, string[] strParams, object[] objValues) ...{ int reslut = -1; DBOperator db = DBOperatorFactory.GetDBOperator(connstring); db.Open(); db.BeginTrans(); try ...{ reslut = db.exeSql(strSql, strParams, objValues); db.CommitTrans(); } catch ...{ db.RollbackTrans(); } finally ...{ db.Close(); } return reslut; } }}