855      阿里云  技术社区[云栖]

C#之DatabaseHelper

         在C#最后的一个加密解密的实例中，说到了DatabaseHelper，DatabaseHelper是一种能连接4中数据库的DatabaseHelper类，也就是说在连接数据库时，你的数据库可能是SQL Server，可能是OLEDB，该类提供了几种初始化重载方法，可以直接将连接字符串传入，或者通过web.config配置文件ConnectionString["connectionstring"]，可以直接指明数据库类型，也可以不指明（该类可以自动分析）我们就需要这样一个中介可以连接不同的数据库——DatabaseHelper。下面是实战中DatabaseHelper的一段代码。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data ;
using System.Configuration ;
using System.Data.Common ;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.IO;

namespace WebHelper.DB
{
    public class DatabaseHelper:IDisposable 
    {
        private string strConnectionString;
        private DbConnection objConnection;
        private DbCommand objCommand;//表示对数据源执行的SQL语句或存储过程
        private DbProviderFactories objFactory = null;//用于创建类的一个或多个实例
        private bool boolHandleErrors;
        private string strLastError;
        private bool boolLogError;
        private string strLogFile;
        //根据databasehelper初始化一个新类
        public DatabaseHelper (string connectionstring,Providers.provider )
        {
            strConnectionString = connectionstring;//有参构造方法，在实例化类的时候可以指定数据库连接字符串
	//这个类采用了简单工厂设计模式，case 判断的是 你传递的参数类型，这个参数从代码上看应该是枚举类型，即你可以选择你使用的是哪种数据库，共4种选择，当然必须选对（跟你要使用的数据库一致），不然会出错
            switch (provider )
            {
                case Provider.SqlServer:
                    objFactory =SqlClientFactory.Instance ;
                    break ;
                case Providers.OleDb:
                    objFactory =OleDbFactory.Instance ;
                    break ;
                case Providers.Oracle:
                    objFactory =OracleClientFactory.Instance;
                    break ;
                case Providers.ODBC:
                    objFactory =OdbcFactory .Instance ;
                    break ;
                case Providers.ConfigDefined;
                string providername=ConfigurationManager.ConnectionString["connectionstring"].ProviderName;
                    switch (providername )
                    {
                        case "System.Data.SqlClient":
                            objFactory =SqlClientFactory .Instance ;
                            break ;
                        case "System.Data.OleDb":
                            objFactory =OleDbFactory .Instance ;
                            break ;
                        case "System.Data.OracleClient":
                            objFactory =OracleClientFactory.Instance;
                            break ;
                        case "System.Data.Odbc":
                            objFactory =OdbcFactory .Instance ;
                            break ;

                    }
                    break ;
            }
	//以下4句是简单工厂生产产品，就是创建一个连接对象，会自动根据你构造方法里面传递的参数来自动创建一个数据库连接对象
            objConnection =objFactory .CreateConnection();
            objCommand =objFactory.CreateCommand();

            objConnection .ConnectionString =strConnectionString ;
            objConnection .Connection= objConnection ;
        }
        //参数名为provider
         public DatabaseHelper (Providers provider)
            :this(ConfigurationManager.ConnectionString["connectionstring"].ConnectionString,provider)
        {

        }
         //参数名为connectionstring
        public DatabaseHelper (string connectionstring)
            :this(connectionstring ,Providers.SqlServer )
         {

         }
        public DatabaseHelper ()
        
            :this(ConfigurationManager.ConnectionString["connectionstring"].ConnectionString,Providers.ConfigDefined)
        {

        }
        //设置一个值预测是否处理错误，如果处理错误，则true，否则，false;
        public bool HandleErrors
        {
            get 
            {
                return boolHandleErrors ;
            }
            set 
            {
                boolHandleErrors =value ;
            }
        }
        //得到最后一个错误
        public string LastError
        {
            get 
            {
                return strLastError ;
            }
        }
        //设置一个值预测是否记录错误，如果记录错误，则为true，若没有记录错误，则false.
        public bool LogErrors
        {
            get 
            {
                return boolLogError ;
            }
            set 
            {
                boolLogError =value ;
            }
        }
        //创建一个记录错误的文件
        public string LogFile
        {
            get 
            {
                return strLogFile ;
            }
            set 
            {
                strLogFile =value ;
            }
        }
        //写入参数
        public int AddParameter(string name,object value)
        {
            DbParameter p=objFactory .CreateParameter();
            p.ParameterName =name;
            p.Value =value ;
            return objCommand.Parameters .Add (p);
        }
        public int  AddParameter(DbParameter  parameter)
        {
            return objCommand .Parameters .Add (parameter );
        }
        //得到命令
        public DbCommand Command
        {
            get 
            {
                return objCommand ;
            }
        }
        //开始处理
        public void BeginTransaction()
        {
            //如果数据库的连接状态是关闭，则打开连接
            if(objConnection .State ==System.Data .ConnectionState .Closed )
            {
                objConnection .Open ();
            }
            objConnection.BeginTransaction=objConnection .BeginTransaction ();
         }
        //COMMIT命令用于把事务所做的修改保存到数据库
        public void CommitTransaction()
        {
            objCommand.Transaction.Commit ();
            objConnection.Close();
        }
        //回滚处理
        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback ();
            objConnection .Close ();
        }
        //executenonequery是指执行非查询SQL命令，如：增、删、改等。
        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery (query ,CommandType.Text ,ConnectionState.CloseOnExit);
        }
        //确定查询命令的类型
        public int ExecuteNonQuery(string query,CommandType commandtype)
        {
            return ExecuteNonQuery (query ,CommandType.Text ,ConnectionState.CloseOnExit);
        }
        public ExecuteNoneQuery(string query,CommandType commandtype,ConnectionState connectionstate)
        {
            objCommand.CommandText =query ;
            objCommand .CommandType=commandtype ;
            int i=-1;
            try 
            {
                if (objConnection .State ==System.Data .ConnectionState .Closed )
                {
                    objConnection .Open ();
                }
                i=objCommand.ExecuteNonQuery ();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally 
            {
                objCommand.Parameters .Clear ();

                if (connectionstate ==ConnectionState.CloseOnExit)
                {
                    objConnection .Close ();
                }
            }
            return  i ;
        }   
        //从数据库中查询结果集，并返回结果集中的数据
        public object ExecuteScalar(string query)
        {
            return ExecuteScalar (query,CommandType.Text ,ConnectionState.CloseOnExit);
        }
        public object ExecuteScalar(string query,ConnectionState connectionstate)
        {
            return ExecuteScalar (query,CommandType.Text ,connectionstate );
        }
        public object ExecuteScalar(string query,CommandType commandtype,ConnectionState connectionstate)
        {
            objCommand.CommandText =query ;

            objCommand .CommandType=commandtype ;

            object o=null ;
           
            try 
            {
                if (objConnection.State ==System.Data.ConnectionState.Closed )
                {
                    objConnection .Open ();
                }
                o=objCommand .ExecuteScalar ()
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally 
            {
                objCommand .Parameters.Clear ();
                if (connectionstate ==ConnectionState.CloseOnExit)
                {
                    objConnection .Close ();
                }
            }
            return o;
        }
        //尽可能快地对数据库进行查询、查询内容、数据库连接状态并执行不同的结果
        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader (query,CommandType.Text ,ConnectionState .CloseOnExit);
        }
        public DbDataReader ExecuteReader(string query,CommandType,CommandType commandtype)
        {
            return ExecuteReader (query,commandtype ,ConnectionState.CloseOnExit);
        }
        public DbDataReader ExecuteReader(string query,ConnectionState connectionstate)
        {
            return ExecuteReader (query ,CommandType.Text ,connectionstate );
        }
        public DbDataReader ExecuteReader(string query,CommandType commandtype,ConnectionState connectionstate)
        {
            objCommand .CommandType =query ;

            objCommand .CommandType =commandtype;
            DbDataReader reader=null;
            try
            {
                if (objConnection .State ==System.Data.ConnectionState .Closed )//如果数据源连接处于关闭状态

                {
                    objConnection .Open ();//打开连接
                }
                if(connectionstate ==ConnectionState.CloseOnExit)//如果数据库关闭
                {
                    reader=objCommand.ExecuteReader(CommandBehavior.CloseConnection );//在流读取数据模式下,数据库连接不能有效关闭的情况.当reader对象在生成时使用了CommandBehavior.CloseConnection,那数据库连接将reader对象关闭时自动关闭.

                }
                else 
                {
                    reader =objCommand .ExecuteReader ;
                }
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally 
            {
                objCommand.Parameters .Clear ();
            }
            return reader ;
        }
        //为了保证修改数据与数据库的数据保持一致
        public DataSet ExecuteDataSet(string query)
        {
            //可以把DataSet当成内存中的数据库，DataSet是不依赖于数据库的独立数据集合。
            return ExecuteDataSet(query,CommandType.Text,ConnectionState.CloseOnExit); 
        }
        public DataSet ExecuteDataSet(string query,CommandType commandtype)
        {
            return ExecuteDataSet (query,commandtype,ConnectionState.CloseOnExit);
        }
        public DataSet ExecuteDataSet(string query,ConnectionState connectionstate)
        {
            return ExecuteDataSet (query,CommandType.Text ,connectionstate );
        }
        public DataSet ExecuteDataSet(string query,CommandType commandtype,ConnectionState connectionstate)//对数据资料传参
        {
            DbDataAdapter adapter=objFactory.CreateDataAdapter();//创建一个连接可以连接一组SQL命令和一个数据库
            objCommand .CommandText =query ;
            objCommand .CommandType=commandtype ;
            adapter.SelectCommand =objCommand;
            DataSet ds=new DataSet ();
            try
            {
                adapter.Fill (ds);
            }
            catch (Exception ex)
            {
                HandleException(ex);
            }
            finally 
            {
                objCommand .Parameters .Clear ();//清除参数

                if (connectionstate ==ConnectionState.CloseOnExit)
                {
                    if(objConnection.State==System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }
        public DataTable ReaderToTable(DbDataReader reader)//DataTable 是一个临时保存数据的网格虚拟表，它无须代码就可以简单的绑定数据库
        {
            DataTable newTable=new DataTable ();
            DataColumn col;
            DataRow row;
            for (int i =0;i<reader.FieldCount-1;i++)
            {
                col=new DataColumn();
                col.ColumnName=reader.GetName(i);
                col.DataType=reader.GetFieldType(i);
                new Table.Columns.Add(col);
            }
            while(reader.Read())
            {
                row=newTable.NewRow();
                for(int j=0;j<reader.FieldCount-1;j++)
                {
                    row[j]=reader[j];
                }
                newTable.Rows.Add(row);
            }
            return newTable;
        }
        private void HandleException (Exception ex)//异常处理
        {
            if logErrors
            {
                WriteToLog(ex.Message );
            }
            if (HandleErrors)
            {
                strLastError=ex.Message ;
            }
            else
            {
                throw ex;
            }
        }
        private void  WriteLog(string msg)
        {
            StreamWriter writer=File.AppendAllText(LogFile);
            writer.WriteLine(DateTime.Now .ToString ()+"-"+msg);
            write.Close();
        }
        //执行与释放或重置非托管资源相关的应用程序
        public void Dispose()//销毁对象，即数据库中的连接，资源分配
        {
            objConnection.Close();
            objConnection.Dispose();
            objCommand.Dispose();
        }
     }
        //指定数据提供者的类型
    public enum Providers
    {
        SqlServer,OleDb,oracle,ODBC,ConfigDefined
    }
    //指定连接状态
    public enum ConnectionState
    {
        KeepOpen,CloseOnExit
    }
}

最后更新：2017-04-03 12:55:00

  上一篇： 2014，整装待发

  下一篇： HDU 1263 水果