Don't assign connection string to dapper everywhere in ASP.net WebForm
前言
一般使用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時的程式碼看起來會簡潔一點