VB.NET 連接 SQL Server 通用資料庫類別庫實作

企業應用常需集中管理資料庫連接參數與連接建立邏輯。本文提供一個完整的 VB.NET 類別庫實作，包含連接參數結構、多載連接方法、狀態屬性、組織架構解析輔助類別，可直接整合至專案中復用。

核心設計：PublicApplicationVal 結構

Public Structure PublicApplicationVal
    ' 連接參數
    Public DbServer As String      ' 資料庫伺服器名稱
    Public UserId As String        ' 登入帳號
    Public UserPWD As String       ' 登入密碼
    Public DBase As String         ' 資料庫名稱
    
    ' 擴充查詢參數 (選用)
    Public DBTable As String       ' 常用資料表名稱
    Public QueryFieldName As String ' 查詢欄位名稱
    Public QueryFieldValue As String ' 查詢欄位值
End Structure

DBaseBindComm 核心連接類別

無參數版本：使用硬編碼預設值 (測試/除錯用)

Public Overloads Function LinkedSqlServer() As SqlConnection
    Dim strConn As String = "server=erp;user id=erpsa;password=erpsa;database=erpdata;"
    Dim conn As New SqlConnection(strConn)
    
    Try
        conn.Open()
        StateVal = True
        Return conn
    Catch ex As Exception
        StateVal = False
        Return Nothing
    End Try
End Function

有參數版本：使用結構體傳入參數 (生產環境標準用法)

Public Overloads Function LinkedSqlServer(
    ByVal IPublicApplication As PublicApplicationVal) As SqlConnection
    
    Dim strConn As String = String.Format(
        "server={0};user id={1};password={2};database={3};",
        IPublicApplication.DbServer,
        IPublicApplication.UserId,
        IPublicApplication.UserPWD,
        IPublicApplication.DBase
    )
    
    Dim conn As New SqlConnection(strConn)
    
    Try
        conn.Open()
        StateVal = True
        Return conn
    Catch ex As Exception
        StateVal = False
        Return Nothing
    End Try
End Function

連接狀態唯讀屬性

Private StateVal As Boolean

Public ReadOnly Property State() As Boolean
    Get
        Return StateVal
    End Get
End Property

OrganComm 組織架構解析輔助類別

解析 18 碼部門編碼：前 6 碼=頂層、中 6 碼=一級、後 6 碼=二級

Public Structure PublicOrganIDStructure
    Public DeptTopID As String   ' 頂層部門 (6 碼)
    Public DeptMidId As String   ' 一級部門 (6 碼)
    Public DeptLowId As String   ' 二級部門 (6 碼)
End Structure

Public Function IParseDeptOrgid(ByVal IDeptOrgid As String) As PublicOrganIDStructure
    Dim OrganID As New PublicOrganIDStructure()
    Dim Orgid As String = Microsoft.VisualBasic.Left(Trim(IDeptOrgid), 12)
    
    OrganID.DeptTopID = Microsoft.VisualBasic.Left(Orgid, 6)
    OrganID.DeptMidId = Microsoft.VisualBasic.Right(Orgid, 6)
    OrganID.DeptLowId = Microsoft.VisualBasic.Right(Trim(IDeptOrgid), 6)
    
    Return OrganID
End Function

命名空間與部署建議

Namespace Sunerp.CommClass.Comm
    ' 上述所有類別與結構
End Namespace

專案整合步驟

1. 建立「類別庫」專案，命名為 Sunerp.CommClass
2. 加入 System.Data.SqlClient 參考
3. 貼上上述程式碼，編譯產生 Sunerp.CommClass.dll
4. 主專案加入參考此 DLL
5. Imports Sunerp.CommClass.Comm 即可使用

使用範例

Dim dbParams As New PublicApplicationVal With {
    .DbServer = "PROD-SQL01",
    .UserId = "app_user",
    .UserPWD = ConfigurationManager.AppSettings("DBPassword"),
    .DBase = "ERP_PROD"
}

Dim dbComm As New DBaseBindComm()
Dim conn As SqlConnection = dbComm.LinkedSqlServer(dbParams)

If dbComm.State Then
    Using conn
        ' 執行資料操作...
    End Using
Else
    LogError("資料庫連接失敗")
End If

' 組織架構解析
Dim orgComm As New OrganComm()
Dim orgInfo = orgComm.IParseDeptOrgid("100001200002300003")
' 結果: Top=100001, Mid=200002, Low=300003

進階擴充建議

• 連接池管理：預設 SqlClient 已內建連接池，確保正確 Using 區塊釋放
• 加密連接字串：密碼欄位使用 SecureString 或 DPAPI 加密儲存
• 多資料庫支援：擴充結構體加入 ProviderType，支援 Oracle/MySQL/PostgreSQL
• 重試邏輯：整合 Polly 等彈性庫實作連接重試
• 健康檢查：加入 Ping() 方法執行 SELECT 1 驗證連線存活

完整程式碼下載結構

Sunerp.CommClass/
├── Comm/
│   ├── PublicApplicationVal.vb
│   ├── DBaseBindComm.vb
│   └── OrganComm.vb
├── Sunerp.CommClass.sln
└── bin/Release/Sunerp.CommClass.dll