高級打字員的技術雲

前言

一般使用Dapper的時候，都要先寫一段new SqlConnection()並指派DB連線字串

由於工作上我碰到會修改Web.config裡DB連線字串的key/name值情況，要是 *.aspx.cs 一個一個打開來到處編輯修改，也太沒效率

所以自訂一個物件封裝DbConnection統一集中處理"指派DB連線字串"這檔事

實作

新增一個 MyDbConnection.cs 類別檔，如下 

/*引用命名空間*/
using System.Configuration;
using System.Data;
using System.Data.Common; 
using System.Data.SqlClient;

namespace WebApplication1_WebFormDapper
{
    /// <summary>
    /// 實作 IDbConnection 介面，Dapper才會認得 MyDbConnection類別
    /// MyDbConnection 為 DbConnection 的封裝
    /// </summary>
    public class MyDbConnection : IDbConnection
    {
        #region 自訂
        private DbConnection _conn { get; set; }

        public MyDbConnection()
        {
            //創建 SQL連線物件，Odbc的話就把 SqlClientFactory 改寫成 OdbcFactory 即可
            this._conn =  SqlClientFactory.Instance.CreateConnection();
            //指派預設DB連線字串
            this._conn.ConnectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; 
        }
        public MyDbConnection(string connectionString):this()
        { 
            //指派DB連線字串
            this._conn.ConnectionString = connectionString;
        }

        #endregion

          
        public string ConnectionString
        { 
            get 
            { 
                return this._conn.ConnectionString; 
            } 
            set 
            { 
                this._conn.ConnectionString = value;
            }
        }

        public int ConnectionTimeout { get { return this._conn.ConnectionTimeout; } }

        public string Database { get { return this._conn.Database; } }

        public ConnectionState State { get { return this._conn.State; } }

        public IDbTransaction BeginTransaction()
        {
            return this._conn.BeginTransaction();
        }

        public IDbTransaction BeginTransaction(IsolationLevel il)
        {
            return this._conn.BeginTransaction(il);
        }

        public void ChangeDatabase(string databaseName)
        {
            this._conn.ChangeDatabase(databaseName);
        }

        public void Open()
        {
            this._conn.Open();
        }
        public void Close()
        {
            this._conn.Close();
        }

        public IDbCommand CreateCommand()
        {
            return this._conn.CreateCommand();
        }

        public void Dispose()
        {
            this._conn.Dispose();
            this.Dispose();
        }


    }
}

*.aspx.cs裡使用Dapper的語法↓

using System;
using System.Collections.Generic;
using System.Text;
/*引用命名空間*/
using Dapper;

namespace WebApplication1_WebFormDapper
{

    public partial class WebForm1 : System.Web.UI.Page
    {
        /// <summary>
        /// 自訂的 Db連線物件，這裡不用額外指派DB連線字串
        /// </summary>
        private MyDbConnection _conn = new MyDbConnection();

        protected void Page_Load(object sender, EventArgs e)
        {
              
            IEnumerable<dynamic> rows =  this._conn.Query("Select * from ApplicableProduct");
            
            StringBuilder sb = new StringBuilder();
            
            foreach (dynamic row in rows)//走訪結果
            { 
                sb.Append(row.Product1 + "<br/>");
            }//end foreach

            string result = sb.ToString();
            li_result.Text = result;//顯示結果

        }
    }
}

結語

如此一來，使用Dapper時的程式碼看起來會簡潔一點