萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

C# 封裝 ADO.NET 通用資料存取類 MyDBase 實作教學

設計目標

將 SqlConnection、SqlCommand、SqlDataAdapter 等 ADO.NET 核心物件封裝為可重用的 MyDBase 類別,支援:

  • Windows 整合驗證 / SQL Server 驗證雙模式連線
  • 查詢回傳 DataSet / SqlDataReader
  • 執行非查詢命令 (INSERT/UPDATE/DELETE)
  • 標量查詢 (ExecuteScalar)
  • 交易控制 (ExecuteSQLWithTrans)
  • 錯誤狀態追蹤 (ErrorCode, ErrMessage)

核心程式碼結構

public class MyDBase
{
    private bool _errorCode = false;
    private string _errorMessage;
    private SqlConnection _cn = new SqlConnection();
    private DataSet _rs;

    // 建構子:Windows 驗證
    public MyDBase(string server, string database) { ... }

    // 建構子:SQL 驗證
    public MyDBase(string server, string database, string uid, string pwd) { ... }

    // 查詢回傳 DataSet
    public DataSet GetRecordset(string sql) { ... }

    // 執行非查詢
    public void ExecuteSQL(string sql) { ... }

    // 標量查詢 (回傳 int)
    public int ExecuteSQLScalar(string sql) { ... }

    // 標量查詢 (回傳 string)
    public string ExecuteSQLScalarTOstring(string sql) { ... }

    // 交易執行並回傳標量
    public string ExecuteSQLWithTrans(string sql) { ... }

    // 回傳 DataReader (自動關閉連線)
    public SqlDataReader DBDataReader(string sql) { ... }

    // 關閉連線
    public void DBClose() { ... }

    // 錯誤屬性
    public bool ErrorCode() => _errorCode;
    public string ErrMessage() => _errorMessage;
}

關鍵實作細節

連線字串組裝

// Windows 整合驗證
_cn.ConnectionString = $@"workstation id={server};packet size=4096;integrated security=SSPI;data source={server};persist security info=False;initial catalog={database}";

// SQL Server 驗證
_cn.ConnectionString = $@"workstation id={server};packet size=4096;user id={uid};pwd={pwd};data source={server};persist security info=False;initial catalog={database}";

參數化查詢防止 SQL Injection

雖然範例類別未直接暴露參數化方法,但實際使用時建議擴充:


public DataSet GetRecordset(string sql, params SqlParameter[] parameters)
{
    using (var cmd = new SqlCommand(sql, _cn))
    {
        cmd.Parameters.AddRange(parameters);
        var da = new SqlDataAdapter(cmd);
        var ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}

交易處理模式


public string ExecuteSQLWithTrans(string sql)
{
    using (var trans = _cn.BeginTransaction())
    using (var cmd = new SqlCommand(sql, _cn, trans))
    {
        try
        {
            cmd.ExecuteNonQuery();
            // 範例:取回 SCOPE_IDENTITY()
            cmd.CommandText = "SELECT SCOPE_IDENTITY()";
            var result = cmd.ExecuteScalar()?.ToString() ?? "";
            trans.Commit();
            return result;
        }
        catch
        {
            trans.Rollback();
            throw;
        }
    }
}

使用範例


using (var db = new MyDBase(@".\SQLEXPRESS", "MyDB", "sa", "pwd"))
{
    if (db.ErrorCode()) 
    {
        Console.WriteLine("連線失敗: " + db.ErrMessage());
        return;
    }

    // 查詢
    var ds = db.GetRecordset("SELECT * FROM Users WHERE Age > @Age", 
        new SqlParameter("@Age", 18));
    
    // 新增
    db.ExecuteSQL("INSERT INTO Log (Msg) VALUES (@Msg)", 
        new SqlParameter("@Msg", "測試"));

    // 交易
    var id = db.ExecuteSQLWithTrans("INSERT INTO Orders (Amt) VALUES (100); SELECT SCOPE_IDENTITY();");
}

改進建議 (現代化)

  1. 實作 IDisposable:使用 using 區塊自動釋放連線。
  2. 非同步支援:加入 async/await 版本 (ExecuteNonQueryAsync 等)。
  3. 連線池:連線字串預設啟用連線池,避免頻繁開關連線開銷。
  4. 記錄器整合:引入 ILogger (Serilog/NLog) 取代簡單的錯誤旗標。
  5. Dapper/Entity Framework:新專案建議直接採用成熟 ORM/Micro-ORM,減少手寫 ADO.NET 程式碼。

總結

MyDBase 展示了 ADO.NET 基礎封裝的核心概念:連線管理、命令執行、資料填充、交易控制與錯誤處理。雖然現代開發多改用 Dapper/EF Core,但理解底層封裝原理有助於排查效能問題與處理特殊場景。

copyright © 萬盛學電腦網 all rights reserved