萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 使用 DbProviderFactory 實現資料庫廠商無關連線完整範例

為什麼需要 DbProviderFactory?

傳統寫法直接使用 SqlConnection、SqlCommand 等 SQL Server 專用類別,導致程式碼緊耦合特定資料庫。DbProviderFactory 抽象廠模式讓你:

  • 透過設定檔切換資料庫 (SQL Server, Oracle, MySQL, PostgreSQL, SQLite...)
  • 程式碼零修改支援多種資料庫
  • 符合相依性反轉原則,利於測試與維護

Web.config 連線字串配置

<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();
        }
    }
}

前端頁面繫結 GridView (Default.aspx)

<%@ 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>

後端程式碼 (DB.aspx.cs)

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連線介面SqlConnectionfactory.CreateConnection()
DbCommand命令介面SqlCommandfactory.CreateCommand()
DbDataAdapter資料適配器SqlDataAdapterfactory.CreateDataAdapter()
DbParameter參數介面SqlParameterfactory.CreateParameter()

實務注意事項

1. SQL 方言差異

Factory 只解決連線/命令物件建立,不解決 SQL 語法差異:

  • 分頁:SQL Server `OFFSET/FETCH` vs Oracle `ROWNUM` vs MySQL `LIMIT`
  • 識別碼引用:SQL Server `[ ]` vs Oracle/MySQL `" "`
  • 函數名稱:`GETDATE()` vs `SYSDATE` vs `NOW()`
  • 參數前綴:`@param` vs `:param` vs `?`

解決方案:建立 SQL 建構器或使用 Dapper/Entity Framework 等 ORM 處理方言差異。

2. 參數化查詢

var param = factory.CreateParameter();
param.ParameterName = "@name"; // 或 :name, ?
param.Value = "張三";
command.Parameters.Add(param);

3. 連線池與效能

  • ADO.NET 內建連線池,`using` 區塊結束時實際歸還池而非關閉
  • 避免頻繁開關連線,批次操作時共用連線物件

4. .NET Core / .NET 5+ 差異

  • DbProviderFactories 需安裝 System.Data.Common 套件
  • 建議改用 Microsoft.Extensions.Configuration + DbContext (EF Core) 或 Dapper
  • 原生支援依賴注入,生命週期管理更完善

總結

DbProviderFactory 提供了 ADO.NET 層級的資料庫廠商抽象,配合 `web.config` 連線字串的 `providerName`,讓應用程式具備切換資料庫的能力。雖然無法完全消除 SQL 方言差異,但對於連線管理、命令建立、參數處理等核心 ADO.NET 操作提供了統一介面,是建立多資料庫支援系統的基石。

copyright © 萬盛學電腦網 all rights reserved