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 水果