再发一个数据库操作类

 

现在可以选择的持久层的框架非常多,象从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();

            }

      }

  }

}

 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值