萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 電腦故障排除 >  >> 系統故障

VB.NET 連接 SQL Server 通用資料存取類別完整代碼

提供一個可直接使用的 VB.NET 類別 connect,封裝 SqlConnection、SqlCommand、SqlDataAdapter,支援 DataTable、DataSet、SqlDataReader 以及非查詢執行,適合快速開發資料庫應用。

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlException

Public Class connect
    Private s As String
    Private s1 As String
    Public Connect As New SqlConnection("server=Yiyang;database=KDERP;uid=sa;password=123")

    Sub New()
        Connect.Open()
    End Sub

    Public Property sql() As String
        Get
            Return s
        End Get
        Set(ByVal Value As String)
            s = Value
        End Set
    End Property

    'Public Property table() As String
    '    Get
    '        Return s1
    '    End Get
    '    Set(ByVal Value As String)
    '        s1 = Value
    '    End Set
    'End Property

    Public Function Retable() As DataTable
        Dim com As New DataSet()
        Dim ada As New SqlDataAdapter()
        Dim com1 As New SqlCommand()
        com1.Connection = Connect
        com1.CommandText = s
        ada.SelectCommand = com1
        ada.Fill(com, "t1")
        Return com.Tables(0)
    End Function

    Public Function Exec() As Integer
        Dim i As Integer
        Dim com As New SqlCommand()
        com.CommandText = s
        com.Connection = Connect
        i = com.ExecuteNonQuery()
        Return i
    End Function

    Public Function Retdataset(ByVal t As String) As DataSet
        Dim com As New SqlCommand()
        com.Connection = Connect
        com.CommandText = s
        Dim ada As New SqlDataAdapter()
        ada.SelectCommand = com
        Dim da As New DataSet()
        ada.Fill(da, t)
        Return da
    End Function

    Public Function Retdataread() As SqlDataReader
        Dim com As New SqlCommand()
        com.Connection = Connect
        com.CommandText = s
        Return com.ExecuteReader
    End Function
End Class

使用說明

  • 實例化後,設定 sql 屬性為 T-SQL 指令。
  • Retable():回傳第一個 DataTable,適合單一結果集。
  • Retdataset(ByVal t As String):回傳 DataSet,t 為資料表名稱。
  • Retdataread():回傳 SqlDataReader,適合大量資料串流讀取。
  • Exec():執行 INSERT/UPDATE/DELETE,回傳影響列數。

注意事項

  1. 連接字串請依實際環境修改(伺服器、資料庫、帳號、密碼)。
  2. 建議加入 Try...Catch...Finally 處理例外,並在 Finally 關閉連線。
  3. 生產環境請使用參數化查詢防止 SQL Injection。
  4. 考慮實作 IDisposable 以正確釋放資源。
copyright © 萬盛學電腦網 all rights reserved