傳統寫法直接使用 SqlConnection、SqlCommand 等 SQL Server 專用類別,導致程式碼緊耦合特定資料庫。DbProviderFactory 抽象廠模式讓你:
<connectionStrings>
<!-- SQL Server -->
<add name="MSSQL"
connectionString="Data Source=localhost;Initial Catalog=test;User ID=sa;Password=sa;"
providerName="System.Data.SqlClient" />
<!-- Oracle (需安裝 ODP.NET) -->
<add name="OracleDB"
connectionString="Data Source=ORCL;User Id=scott;Password=tiger;"
providerName="Oracle.ManagedDataAccess.Client" />
<!-- MySQL (需安裝 MySql.Data) -->
<add name="MySQL"
connectionString="Server=localhost;Database=test;Uid=root;Pwd=123456;"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
public class DatabaseHelper
{
///
/// 執行查詢並回傳 DataSet
///
public static DataSet GetDataSet(string sqlCommand, string connectionName = "MSSQL")
{
// 1. 取得對應的 Provider Factory
var factory = DbProviderFactories.GetFactory(
ConfigurationManager.ConnectionStrings[connectionName].ProviderName);
// 2. 建立連線、命令、適配器 (全介面型別)
using (var connection = factory.CreateConnection())
using (var command = factory.CreateCommand())
using (var adapter = factory.CreateDataAdapter())
{
connection.ConnectionString =
ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
command.Connection = connection;
command.CommandText = sqlCommand;
adapter.SelectCommand = command;
var ds = new DataSet();
adapter.Fill(ds);
return ds;
}
}
///
/// 執行非查詢命令 (INSERT/UPDATE/DELETE)
///
public static int ExecuteNonQuery(string sqlCommand, string connectionName = "MSSQL")
{
var factory = DbProviderFactories.GetFactory(
ConfigurationManager.ConnectionStrings[connectionName].ProviderName);
using (var connection = factory.CreateConnection())
using (var command = factory.CreateCommand())
{
connection.ConnectionString =
ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
command.Connection = connection;
command.CommandText = sqlCommand;
connection.Open();
return command.ExecuteNonQuery();
}
}
///
/// 執行純量查詢 (COUNT, SUM 等)
///
public static object ExecuteScalar(string sqlCommand, string connectionName = "MSSQL")
{
var factory = DbProviderFactories.GetFactory(
ConfigurationManager.ConnectionStrings[connectionName].ProviderName);
using (var connection = factory.CreateConnection())
using (var command = factory.CreateCommand())
{
connection.ConnectionString =
ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
command.Connection = connection;
command.CommandText = sqlCommand;
connection.Open();
return command.ExecuteScalar();
}
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DB.aspx.cs" Inherits="DB" %>
<!DOCTYPE html>
<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
<title>資料庫廠商無關查詢示範</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlDatabase" runat="server" AutoPostBack="true"
OnSelectedIndexChanged="ddlDatabase_SelectedIndexChanged">
<asp:ListItem Text="SQL Server" Value="MSSQL" />
<asp:ListItem Text="Oracle" Value="OracleDB" />
<asp:ListItem Text="MySQL" Value="MySQL" />
</asp:DropDownList>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"
CssClass="table table-striped"></asp:GridView>
</div>
</form>
</body>
</html>
public partial class DB : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid("MSSQL");
}
}
protected void ddlDatabase_SelectedIndexChanged(object sender, EventArgs e)
{
BindGrid(ddlDatabase.SelectedValue);
}
private void BindGrid(string connName)
{
try
{
// 切換資料庫只需改連線名稱,查詢語法需自行處理方言差異
string sql = connName == "OracleDB"
? "SELECT * FROM \"TABLE\"" // Oracle 雙引號
: "SELECT * FROM [Table]"; // SQL Server 方括號
var ds = DatabaseHelper.GetDataSet(sql, connName);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (Exception ex)
{
// 實務應記錄日誌並顯示友善錯誤訊息
Response.Write($"<div style='color:red'>錯誤: {ex.Message}</div>");
}
}
}
| 類別/介面 | 角色 | 傳統寫法 | Factory 寫法 |
|---|---|---|---|
| DbProviderFactory | 抽象廠介面 | 無 | DbProviderFactories.GetFactory() |
| DbConnection | 連線介面 | SqlConnection | factory.CreateConnection() |
| DbCommand | 命令介面 | SqlCommand | factory.CreateCommand() |
| DbDataAdapter | 資料適配器 | SqlDataAdapter | factory.CreateDataAdapter() |
| DbParameter | 參數介面 | SqlParameter | factory.CreateParameter() |
Factory 只解決連線/命令物件建立,不解決 SQL 語法差異:
解決方案:建立 SQL 建構器或使用 Dapper/Entity Framework 等 ORM 處理方言差異。
var param = factory.CreateParameter();
param.ParameterName = "@name"; // 或 :name, ?
param.Value = "張三";
command.Parameters.Add(param);
DbProviderFactories 需安裝 System.Data.Common 套件Microsoft.Extensions.Configuration + DbContext (EF Core) 或 DapperDbProviderFactory 提供了 ADO.NET 層級的資料庫廠商抽象,配合 `web.config` 連線字串的 `providerName`,讓應用程式具備切換資料庫的能力。雖然無法完全消除 SQL 方言差異,但對於連線管理、命令建立、參數處理等核心 ADO.NET 操作提供了統一介面,是建立多資料庫支援系統的基石。