提供一個可直接使用的 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,回傳影響列數。Try...Catch...Finally 處理例外,並在 Finally 關閉連線。IDisposable 以正確釋放資源。