数据库访问类

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