ASP.NET 多層架構基類設計：DataCore 封裝資料存取與重載 RunProcedure 實現多型預存程序呼叫

多層架構分層原則

• Data Layer (DAL)：唯一直接操作資料庫的層，封裝 ADO.NET 細節、連線管理、交易控制、SQL/預存程序執行。
• Business Logic Layer (BLL)：引用 DAL，實作業務規則、驗證、工作流程協調、資料轉換 (Entity ↔ DTO)。
• Presentation Layer (UI)：ASP.NET 頁面/控制項，僅負責綁定、輸入收集、導向，不含 SQL/連線字串。

DataCore 基類設計目標

1. 集中管理資料庫連線字串、連線物件建立、開啟/關閉邏輯。
2. 提供統一預存程序呼叫介面，支援不同回傳類型 (Scalar、DataReader、DataSet、Output 參數)。
3. 透過**方法重載**實現同名方法處理不同簽名，簡化衍生類呼叫。
4. 實作 IDisposable 確保連線資源釋放。

DataCore 完整實作 (VB.NET)

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public MustInherit Class DataCore
    Implements IDisposable
    
    ' 連線字串從 config 讀取，衍生類可覆寫
    Protected Overridable ReadOnly Property ConnectionString() As String
        Get
            Return ConfigurationManager.ConnectionStrings("DefaultConn").ConnectionString
        End Get
    End Property
    
    Private _connection As SqlConnection
    Protected ReadOnly Property Connection() As SqlConnection
        Get
            If _connection Is Nothing Then
                _connection = New SqlConnection(ConnectionString)
            End If
            Return _connection
        End Get
    End Property
    
    ' 統一建立 Command，設定預存程序、參數、CommandType
    Protected Function BuildCommand(storedProcName As String, params As SqlParameter()) As SqlCommand
        Dim cmd As New SqlCommand(storedProcName, Connection)
        cmd.CommandType = CommandType.StoredProcedure
        If params IsNot Nothing Then
            cmd.Parameters.AddRange(params)
        End If
        Return cmd
    End Function
    
    ' === 重載 1：執行無回傳結果集的預存程序 (Insert/Update/Delete)，回傳影響列數與 ReturnValue ===
    Protected Function RunProcedure(storedProcName As String, params As SqlParameter(), ByRef rowsAffected As Integer) As Integer
        Using cmd = BuildCommand(storedProcName, params)
            ' 加入 ReturnValue 參數
            Dim retParam = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
            retParam.Direction = ParameterDirection.ReturnValue
            
            Connection.Open()
            rowsAffected = cmd.ExecuteNonQuery()
            Connection.Close()
            
            Return CInt(retParam.Value)
        End Using
    End Function
    
    ' === 重載 2：執行回傳 DataReader (單一結果集，唯讀、前向) ===
    Protected Function RunProcedure(storedProcName As String, params As SqlParameter()) As SqlDataReader
        Dim cmd = BuildCommand(storedProcName, params)
        Connection.Open()
        ' CommandBehavior.CloseConnection: Reader 關閉時自動關閉連線
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function
    
    ' === 重載 3：執行回傳 DataSet (單一資料表，指定表名) ===
    Protected Function RunProcedure(storedProcName As String, params As SqlParameter(), tableName As String) As DataSet
        Using cmd = BuildCommand(storedProcName, params)
            Dim ds As New DataSet()
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(ds, tableName)
            Return ds
        End Using
    End Function
    
    ' === 重載 4：填充現有 DataSet (支援多表、合併) ===
    Protected Sub RunProcedure(storedProcName As String, params As SqlParameter(), dataSet As DataSet, tableName As String)
        Using cmd = BuildCommand(storedProcName, params)
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dataSet, tableName)
        End Using
    End Sub
    
    ' === 釋放連線 ===
    Public Sub Dispose() Implements IDisposable.Dispose
        If _connection IsNot Nothing Then
            If _connection.State <> ConnectionState.Closed Then _connection.Close()
            _connection.Dispose()
            _connection = Nothing
        End If
    End Sub
End Class

衍生類具體 DAL 實作範例

Public Class UserDAL : Inherits DataCore
    
    Public Function GetUserById(userId As Integer) As DataTable
        Dim params = { New SqlParameter("@UserId", userId) }
        Dim ds = RunProcedure("usp_GetUserById", params, "User")
        Return ds.Tables("User")
    End Function
    
    Public Function GetUsersByRole(roleId As Integer) As SqlDataReader
        Dim params = { New SqlParameter("@RoleId", roleId) }
        Return RunProcedure("usp_GetUsersByRole", params)
    End Function
    
    Public Function CreateUser(username As String, email As String, passwordHash As String, ByRef newUserId As Integer) As Boolean
        Dim params = {
            New SqlParameter("@Username", username),
            New SqlParameter("@Email", email),
            New SqlParameter("@PasswordHash", passwordHash),
            New SqlParameter("@NewUserId", SqlDbType.Int) With {.Direction = ParameterDirection.Output}
        }
        Dim rows As Integer
        Dim retVal = RunProcedure("usp_CreateUser", params, rows)
        newUserId = CInt(params.First(p => p.ParameterName = "@NewUserId").Value)
        Return retVal = 0 ' 假設 SP 回傳 0 表示成功
    End Function
    
    Public Function UpdateUserProfile(userId As Integer, profileXml As String) As Integer
        Dim params = {
            New SqlParameter("@UserId", userId),
            New SqlParameter("@ProfileXml", profileXml)
        }
        Dim rows As Integer
        Return RunProcedure("usp_UpdateUserProfile", params, rows)
    End Function
End Class

重載設計優勢分析

進階擴展：交易支援與非同步

' 交易範圍方法 (衍生類使用)
Protected Function ExecuteInTransaction(actions As Action()) As Boolean
    Connection.Open()
    Dim tran = Connection.BeginTransaction()
    Try
        For Each act In actions
            act() ' 內部使用 Connection + tran
        Next
        tran.Commit()
        Return True
    Catch
        tran.Rollback()
        Return False
    Finally
        Connection.Close()
    End Try
End Function

' .NET 4.5+ 非同步版本 (需衍生類實作)
Protected Async Function RunProcedureAsync(storedProcName As String, params As SqlParameter()) As Task(Of SqlDataReader)
    Dim cmd = BuildCommand(storedProcName, params)
    Await Connection.OpenAsync()
    Return Await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection)
End Function

最佳實務檢核

• [ ] 連線字串集中管理 (config/環境變數)，不硬編碼。
• [ ] 所有 SQL 參數化，**杜絕字串拼接** 防 SQL Injection。
• [ ] 連線/Command/Reader/Adapter 善用 Using 確保釋放。
• [ ] CommandBehavior.CloseConnection 僅用於回傳 DataReader 給呼叫端自行管理的情境。
• [ ] 基類標記 MustInherit (abstract)，不可直接實例化。
• [ ] 記錄慢查詢、例外資訊 (Log4net/Serilog/NLog)。

結語

DataCore 基類透過**方法重載**封裝四種預存程序呼叫模式 (Scalar/Output、DataReader、DataSet、填充 DataSet)，配合統一連線管理、參數化命令建構、交易支援擴展，構建了清晰、可復用、可測試的 DAL 基礎設施。衍生類僅需專注業務邏輯與參數準備，即可獲得強健的資料存取能力。此模式為《Wrox .Net Website Programming Problem-Design-Solution》經典架構核心，至今仍具參考價值。