再发一个数据库操作类

 

现在可以选择的持久层的框架非常多，象从Java移植过来的NHibernate就已经很成熟了，MS又推出了Linq。使用O/R映射技术，封装了具体数据库的操作，以访问对象的方式访问数据，可以使开发人更专注于领域模型或业务逻辑的设计，减轻了对某一具体数据库技术的学习负担，可以大大提高软件的生产效率，并提升软件的可移植性和可维护性。

    但对于面向数据的应用，比如大多数以内容展示为主的网站项目、复杂的数据处理、对反映速度及效率要求高的项目等等，一般的持久层框架，可能并不完全适合，在这种情况下，直接操作数据库当然是可以选择的了，然而这可能不OO，但是完全可以象Petshop4一样动手写一个专用于此项目的持久方案。即使以数据为中心的项目，思维也可以是面向对象的。

以后对数据库直接操作的机会越来越少了，我以前也写了一个数据库操作类，发到csdn论坛去了，现在仍然还有朋友在使用，现在发的这个是重写过的，我也在项目中有过使用。这个当然不是持久框架的，而只是封装了一下对数据库的操作而已，类似于SqlHelper，使用时比较方便。

比如执行一个查询返回一个DataTable,可以这样：

Db db = new db();

DataTable data = db.RunDataTable("select * from customers", CommandType.Text);

 

//调用一个存储过程返回一个DataSet：

db.CmdParaAdd("@Id", DbType.Int32, 2);

db.CmdParaAdd("@name", DbType.String, "abcd");

DataSet ds = db.RunDataSet("PCustomerByIdByName", CommandType.StoredProcedure);

从上面可看出，代码非常简洁，很好使用。

下面发两个，一个是专用了sql server的，还有一个可以用于sqlserver/mysql/postgresql/oledb的，以供参考和批评。

 

专用于sql server:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

using System.Web;

namespace DataBase {

    public class Dao {

        private SqlConnection conn;

        private SqlCommand cmd;

        private bool cmdExecuted = false;//命令是否执行过

        //用于是否开启事务判断

        public enum IsTrans {

            Yes = 1, No = 2

        }

        //每个类实例对应着一个Command,这样方便命令参数的添加和移出,所有数据库操作都已封装.

        //因此不支持命令对象的嵌套,一般很少有需要嵌套的场合,如需嵌套完全可以在sql中使用子句完成

        public Dao() {

            conn = new SqlConnection();

            conn.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

            cmd = conn.CreateCommand();

        }

        //生成存储过程参数

        private SqlParameter MakeParam(string ParamName, SqlDbType DbType, ParameterDirection Direction, object Value) {

            SqlParameter para = conn.CreateCommand().CreateParameter();

            para.ParameterName = ParamName;

            para.DbType = (DbType)DbType;

            para.Direction = Direction;

            if (Direction != ParameterDirection.Output && Direction != ParameterDirection.ReturnValue) {

                para.Value = Value;

            }

            return para;

        }

        //添加存储过程参数

        public void CmdParaAdd(string ParamName, SqlDbType DbType, object value) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, DbType, ParameterDirection.Input, value));

        }

        public void CmdParaAdd(string ParamName, SqlDbType DbType, ParameterDirection Direction) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, DbType, Direction, 0));

        }

        public void CmdParaAdd(string ParamName, SqlDbType DbType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, DbType, ParameterDirection.ReturnValue, 0));

        }

        public void CmdParaAdd(string ParamName, SqlDbType DbType, ParameterDirection Direction, object value) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, DbType, Direction, value));

        }

        //返回参数值

        public object GetParamValue(string paramName) {

            return cmd.Parameters[paramName].Value;

        }

        //运行查询不返回记录

        public int RunNon(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            this.Open();

            int returnValue = cmd.ExecuteNonQuery();

            this.Close();

            cmdExecuted = true;

            return returnValue;

        }

        public int RunNon(string cmdStr, CommandType cmdType, IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunNon(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                this.Open();

                if (cmd.Transaction == null) {

                    cmd.Transaction = conn.BeginTransaction();

                }

                int returnValue = 0;

                try {

                    returnValue = cmd.ExecuteNonQuery();

                    cmd.Transaction.Commit();

                }

                catch {

                    cmd.Transaction.Rollback();

                }

                finally {

                    cmd.Transaction = null;

                    this.Close();

                    cmdExecuted = true;

                }

                return returnValue;

            }

        }

        //返回首行首列

        public object RunScalar(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            this.Open();

            object returnValue = cmd.ExecuteScalar();

            this.Close();

            cmdExecuted = true;

            return returnValue;

        }

        public object RunScalar(string cmdStr, CommandType cmdType, IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunScalar(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                this.Open();

 

                if (cmd.Transaction == null) {

                    cmd.Transaction = conn.BeginTransaction();

                }

                object returnValue = null;

                try {

                    returnValue = cmd.ExecuteScalar();

                    cmd.Transaction.Commit();

                }

                catch {

                    cmd.Transaction.Rollback();

                }

                finally {

                    cmd.Transaction = null;

                    this.Close();

                    cmdExecuted = true;

                }

                return returnValue;

            }

        }

        //返回DataReader

        public SqlDataReader RunReader(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            this.Open();

            SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmdExecuted = true;

            return dataReader;

        }

        //返回DataSet

        public DataSet RunDataSet(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = cmd;

            DataSet ds = new DataSet();

            this.Open();

            da.Fill(ds);

            this.Close();

            cmdExecuted = true;

            return ds;

        }

        public DataSet RunDataSet(string cmdStr, CommandType cmdType, IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunDataSet(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = cmd;

                DataSet ds = new DataSet();

                this.Open();

                if (da.SelectCommand.Transaction == null) {

                    da.SelectCommand.Transaction = conn.BeginTransaction();

                }

                try {

                    da.Fill(ds);

                    da.SelectCommand.Transaction.Commit();

                }

                catch {

                    da.SelectCommand.Transaction.Rollback();

                }

                finally {

                    da.SelectCommand.Transaction = null;

                    this.Close();

                    cmdExecuted = true;

                }

                return ds;

            }

        }

        //返回DataTable

        public DataTable RunDataTable(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = cmd;

            DataTable dt = new DataTable();

            this.Open();

            da.Fill(dt);

 

            this.Close();

            cmdExecuted = true;

            return dt;

        }

 

        public DataTable RunDataTable(string cmdStr, CommandType cmdType, IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunDataTable(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = cmd;

                DataTable dt = new DataTable();

                this.Open();

                if (da.SelectCommand.Transaction == null) {

                    da.SelectCommand.Transaction = conn.BeginTransaction();

                }

                try {

                    da.Fill(dt);

                    da.SelectCommand.Transaction.Commit();

                }

                catch {

                    da.SelectCommand.Transaction.Rollback();

                }

                finally {

                    da.SelectCommand.Transaction = null;

 

                    this.Close();

                    cmdExecuted = true;

                }

                return dt;

            }

        }

        //连接的打开与关闭

        private void Open() {

            if (conn.State == ConnectionState.Closed) {

                conn.Open();

            }

        }

        private void Close() {

            if (conn.State == ConnectionState.Open)

                conn.Close();

        }

    }

}

 

******************************************************************************************

可用于sqlserver/mysql/postgresql/oledb：（注：需要用到MySql.Data.dll和NPgsql.dll）

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Data.OleDb;

using System.Configuration;

using MySql.Data.MySqlClient;

using Npgsql;

namespace DataBase {

    public class Db {

        private IDbConnection conn;

        private IDbCommand cmd;

        private bool cmdExecuted = false;

        private string connType;      

        private IDbDataAdapter GetDataAdapter() {

            switch (connType) {

                case "sqlserver":

                  return  new SqlDataAdapter();

                case "mysql":

                  return  new MySqlDataAdapter();

                case "postgresql":

                  return  new NpgsqlDataAdapter();

                default:

                  return   new OleDbDataAdapter();

            }

        }

        //用于是否开启事务判断

        public enum IsTrans {

           Yes=1,No=2

        }

      

        //每个类实例对应着一个Command,这样方便命令参数的添加和移出,所有数据库操作都已封装.

        //因此不支持命令对象的嵌套,一般很少有需要嵌套的场合,如需嵌套完全可以在sql中使用子句完成

        public Db() {

            this.connType = ConfigurationManager.AppSettings["connType"].ToLower();

            string connStr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

            switch (this.connType) {

                case "sqlserver":

                    conn = new SqlConnection(connStr);

                    break;

                case "mysql":

                    conn = new MySqlConnection(connStr);

                    break;

                case "postgresql":

                    conn = new NpgsqlConnection(connStr);

                    break;

                default:

                    conn = new OleDbConnection(connStr);

                    break;

            }

            cmd = conn.CreateCommand();

        }

        //生成存储过程参数 

        private IDbDataParameter MakeParam(string ParamName, DbType dbType, ParameterDirection Direction, object Value) {

            IDbDataParameter para = conn.CreateCommand().CreateParameter();

            para.ParameterName = ParamName;

            para.DbType = dbType;

            para.Direction = Direction;

            if (Direction != ParameterDirection.Output && Direction != ParameterDirection.ReturnValue) {

                para.Value = Value;

            }

           

            return para;

        }

        //添加存储过程参数

        public void CmdParaAdd(string ParamName, DbType dbType, object value) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, dbType, ParameterDirection.Input, value));

        }

        public void CmdParaAdd(string ParamName, DbType dbType, ParameterDirection Direction) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, dbType, Direction, 0));

        }

        public void CmdParaAdd(string ParamName, DbType dbType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, dbType, ParameterDirection.ReturnValue, 0));

        }

        public void CmdParaAdd(string ParamName, DbType dbType, ParameterDirection Direction, object value) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }

            cmd.Parameters.Add(MakeParam(ParamName, dbType, Direction, value));

        }

        //返回参数值

        public object GetParamValue(string paramName) {

            IDbDataParameter parameter = null;

            switch (connType) {

                case "sqlserver":

                    parameter = (SqlParameter)cmd.Parameters[paramName];

                    break;

                case "mysql":

                    parameter = (MySqlParameter)cmd.Parameters[paramName];

                    break;

                case "postgresql":

                    parameter = (NpgsqlParameter)cmd.Parameters[paramName];

                    break;

                default:

                    parameter = (OleDbParameter)cmd.Parameters[paramName];

                    break;

            }

            return parameter.Value;

        }

        //运行查询不返回记录

        public int RunNon(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            this.Open();          

            int returnValue = cmd.ExecuteNonQuery();

            this.Close();

            cmdExecuted = true;          

            return returnValue;

        }

        public int RunNon(string cmdStr, CommandType cmdType,IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunNon(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                this.Open();

                if (cmd.Transaction == null) {

                    cmd.Transaction = conn.BeginTransaction();

                }

                int returnValue = 0;

                try {

                    returnValue = cmd.ExecuteNonQuery();

                    cmd.Transaction.Commit();

                }

                catch {

                    cmd.Transaction.Rollback();

                }

                finally {

                    cmd.Transaction = null;

                    this.Close();

                    cmdExecuted = true;

                }

                return returnValue;

            }

        }

        //返回首行首列

        public object RunScalar(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            this.Open();

            object returnValue = cmd.ExecuteScalar();

            this.Close();

            cmdExecuted = true;

            return returnValue;

        }

        public object RunScalar(string cmdStr, CommandType cmdType, IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunScalar(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                this.Open();

                if (cmd.Transaction == null) {

                    cmd.Transaction = conn.BeginTransaction();

                }

                object returnValue = null;

                try {

                    returnValue = cmd.ExecuteScalar();

                    cmd.Transaction.Commit();

                }

                catch {

                    cmd.Transaction.Rollback();

                }

                finally {

                    cmd.Transaction = null;

                    this.Close();

                    cmdExecuted = true;

                }

                return returnValue;

            }

        }

        //返回DataReader

        public IDataReader  RunReader(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            this.Open();

            IDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmdExecuted = true;

            return dataReader;

        }

        //返回DataSet

        public DataSet RunDataSet(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            IDbDataAdapter da =GetDataAdapter();            

            da.SelectCommand = cmd;

            DataSet ds = new DataSet();

            this.Open();

            da.Fill(ds);

           

            this.Close();

            cmdExecuted = true;               

            return ds;

        }

        public DataSet RunDataSet(string cmdStr, CommandType cmdType, IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunDataSet(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                IDbDataAdapter da = GetDataAdapter();

              

                da.SelectCommand = cmd;

                DataSet ds = new DataSet();

                this.Open();

                if (da.SelectCommand.Transaction == null) {

                    da.SelectCommand.Transaction = conn.BeginTransaction();

                }

                try {

                    da.Fill(ds);

                    da.SelectCommand.Transaction.Commit();

                }

                catch {

                    da.SelectCommand.Transaction.Rollback();

                }

                finally {

                    da.SelectCommand.Transaction = null;

                   

                    this.Close();

                    cmdExecuted = true;

                }

                return ds;

            }

        }

        //返回DataTable

 

        public DataTable RunDataTable(string cmdStr, CommandType cmdType) {

            if (cmdExecuted == true) { cmd.Parameters.Clear(); }

            cmd.CommandText = cmdStr;

            cmd.CommandType = cmdType;

            IDbDataAdapter da =GetDataAdapter();

            da.SelectCommand = cmd;

            DataSet ds = new DataSet();

            this.Open();

            da.Fill(ds);            

            this.Close();

            cmdExecuted = true;

            return ds.Tables[0];

        }

        public DataTable RunDataTable(string cmdStr, CommandType cmdType, IsTrans trans) {

            if (trans == IsTrans.No) {

                return RunDataTable(cmdStr, cmdType);

            }

            else {

                if (cmdExecuted == true) { cmd.Parameters.Clear(); }

                cmd.CommandText = cmdStr;

                cmd.CommandType = cmdType;

                IDbDataAdapter da = GetDataAdapter();

                da.SelectCommand = cmd;

                DataSet ds = new DataSet();

                this.Open();

                if (da.SelectCommand.Transaction == null) {

                    da.SelectCommand.Transaction = conn.BeginTransaction();

                }

                try {

                    da.Fill(ds);

                    da.SelectCommand.Transaction.Commit();

                }

                catch {

                    da.SelectCommand.Transaction.Rollback();

                }

                finally {

                    da.SelectCommand.Transaction = null;

                  

                    this.Close();

                    cmdExecuted = true;

                }

                return ds.Tables[0];

               

            }

        }

       //连接的打开与关闭

        private void Open() {

            if (conn.State == ConnectionState.Closed) {

                conn.Open();

            }

        }

        private void Close() {

            if (conn.State == ConnectionState.Open)

                conn.Close();

        }

        private void Dispose() {

            if (conn != null) {

                conn.Dispose();

            }

      }

  }

}