VB.Net2.0資料庫通用存取函式

原出處

編輯app.config，在【configuration】標籤內加入連線字串的設定如：


...

connectionString="Data Source=localhost;Initial Catalog=MyTestDB;Integrated Security=True"
providerName="System.Data.SqlClient" />
connectionString="Data Source=127.0.0.1;Initial Catalog=MyTestDB;User ID=sa;Password=1234"
providerName="System.Data.SqlClient" />

...

通用存取函式類別:
Imports System.Data.SqlClient
Imports System.Configuration
Public Class DBAccessFunc
'''

''' 從ConfigurationManager中的ConnectionStrings找出Initial Catalog
'''

''' ConnectionStrings名稱(String)
''' 回傳資料庫名稱
''' 從ConfigurationManager中的ConnectionStrings找出Initial Catalog
Public Shared Function getDBName(ByVal ConnName As String) As String
Dim val As String = ""
Dim ConnString As String = ConfigurationManager.ConnectionStrings(ConnName).ConnectionString
Dim i As Integer = ConnString.IndexOf("Initial Catalog=")
If i > -1 Then
val = ConnString.Substring(i + "Initial Catalog=".Length)
i = val.IndexOf(";")
If i > -1 Then
val = val.Substring(0, i)
End If
End If
Return val
End Function

Public Shared Function getConnString(ByVal ConnName As String) As String
Return ConfigurationManager.ConnectionStrings(ConnName).ConnectionString
End Function


Public Shared Function getConn(ByVal ConnName As String) As SqlConnection
Return New SqlConnection(getConnString(ConnName))
End Function
'''

''' 查詢資料庫
'''

''' 連線
''' 查詢內容
''' 回傳查詢結果的DataTable
''' 查詢資料庫並回傳查詢結果的DataTable
Public Shared Function getTable(ByRef cn As SqlConnection, ByRef sql As String, Optional ByVal isColseConn As Boolean = True) As Data.DataTable
Dim da As New SqlDataAdapter(sql, cn)
Dim dt As New Data.DataTable("dt_xml")
Try
If cn.State = ConnectionState.Closed Then cn.Open()
da.SelectCommand.CommandTimeout = 36000000
da.Fill(dt)
Catch ex As Exception
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
Throw ex
End Try
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
Return dt
End Function

'''

''' 執行命令
'''

''' 連線
''' 查詢內容
''' 回傳受影響的資料筆數
''' 執行命令並回傳受影響的資料筆數
Public Shared Function doCmd(ByRef cn As SqlConnection, ByRef sql As String, Optional ByRef param() As SqlParameter = Nothing, Optional ByVal isColseConn As Boolean = True) As Integer
Dim result As Integer = 0
Dim cmd As New SqlCommand
Try
cmd.Connection = cn
cmd.CommandText = sql
If Not param Is Nothing Then
cmd.Parameters.AddRange(param)
End If
cmd.CommandTimeout = 36000000
If cn.State = ConnectionState.Closed Then cn.Open()
result = cmd.ExecuteNonQuery()
Catch ex As Exception
cmd.Parameters.Clear()
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
Throw ex
Finally
cmd.Parameters.Clear()
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
End Try
Return result
End Function


'''

''' 取得單一資料
'''

''' 連線
''' 查詢內容
''' 回傳受影響的資料筆數
''' 執行命令並回傳受影響的資料筆數
Public Shared Function doScalar(ByRef cn As SqlConnection, ByRef sql As String, Optional ByRef param() As SqlParameter = Nothing, Optional ByVal isColseConn As Boolean = True) As Object
Dim result As Object = Nothing
Dim cmd As New SqlCommand
Try
cmd.Connection = cn
cmd.CommandText = sql
If Not param Is Nothing Then
cmd.Parameters.AddRange(param)
End If
cmd.CommandTimeout = 36000000
If cn.State = ConnectionState.Closed Then cn.Open()
result = cmd.ExecuteScalar()
Catch ex As Exception
cmd.Parameters.Clear()
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
Throw ex
Finally
cmd.Parameters.Clear()
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
End Try
Return result
End Function

Public Function executeStoredProcedure(ByRef cn As SqlConnection, ByVal procedure As String, Optional ByRef param As SqlParameter() = Nothing, Optional ByRef output As SqlParameter() = Nothing, Optional ByVal isColseConn As Boolean = True) As Integer
Dim result As Integer = 0
Dim cmd As New SqlCommand(procedure, cn)
Try

cmd.CommandText = procedure
cmd.CommandTimeout = 36000000
cmd.CommandType = CommandType.StoredProcedure
If Not param Is Nothing Then cmd.Parameters.AddRange(param)
If Not output Is Nothing Then
For Each o As SqlParameter In output
o.Direction = ParameterDirection.Output
Next
cmd.Parameters.AddRange(output)
End If

'開啟資料庫連線
If cn.State = ConnectionState.Closed Then cn.Open()
'設定變數儲存受影響資料列
result = cmd.ExecuteNonQuery()
Catch ex As Exception
cmd.Parameters.Clear()
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
Throw ex
Finally
cmd.Parameters.Clear()
If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
End Try
Return result
End Function
End Class

使用範例程式:
Dim connStr1 As String = DBGenFunc.getConnStr("MSSQLDB1")
Dim sourceTable As String() = {"[" & DBGenFunc.getDBName("MSSQLDB1") & "].[dbo].[" & "MyTestTab" & "]", _
"[" & DBGenFunc.getDBName("MSSQLDB1") & "].[dbo].[" & "MyContentTab" & "]"}
Dim param() As SqlClient.SqlParameter = {New SqlClient.SqlParameter("P0", SqlDbType.VarChar)}
Dim sql As String

Dim title As String

Console.WriteLine("doCmdScalar Start:")
sql = "SELECT Title" & vbCrLf & _
" FROM " & sourceTable(0) & "" & vbCrLf & _
" WHERE SN=@P0"
param(0).Value = 1
title = DBGenFunc.doCmdScalar(connStr1, sql, param)
Console.WriteLine("doCmdScalar" & vbTab & title)

Console.WriteLine("getTable Start:")
sql = "SELECT Title" & vbCrLf & _
" FROM " & sourceTable(0) & "" & vbCrLf
Dim dt As DataTable = DBGenFunc.getTable(connStr1, sql)
For Each r As DataRow In dt.Rows
title = r.Item(0)
Console.WriteLine("getTable" & vbTab & title)
Next

Console.WriteLine("getTableToArray Start:")
Dim data As String() = DBGenFunc.dataTableToArray(Of String)(dt, 0)
For Each r As String In data
title = r
Console.WriteLine("getTable" & vbTab & title)
Next

Console.WriteLine("doCmd Start:")
sql = "INSERT INTO " & sourceTable(0) & "" & vbCrLf & _
"VALUES(@P0,@P1)"
param = New SqlClient.SqlParameter() {New SqlClient.SqlParameter("P0", SqlDbType.VarChar), _
New SqlClient.SqlParameter("P1", SqlDbType.VarChar)}
param(0).Value = "TNew"
param(1).Value = "SNew"
title = DBGenFunc.doCmd(connStr1, sql, param)
Console.WriteLine("doCmd" & vbTab & title)

Console.WriteLine("getReader Start:")
sql = "SELECT Title, Subject" & vbCrLf & _
" FROM " & sourceTable(0) & "" & vbCrLf
Dim dr As SqlClient.SqlDataReader = DBGenFunc.getReader(connStr1, sql)
While dr.Read()
title = dr.Item(0)
Console.WriteLine("getReader" & vbTab & title & vbTab & dr.Item(1))
End While
DBGenFunc.closeReader()

執行結果:
doCmdScalar Start:
doCmdScalar T1
getTable Start:
getTable T1
getTable T2
getTable T3
getTable TNew
getTableToArray Start:
getTable T1
getTable T2
getTable T3
getTable TNew
doCmd Start:
doCmd 1
getReader Start:
getReader T1 S1
getReader T2 S2
getReader T3 S3
getReader TNew SNew
getReader TNew SNew