序言
在VS2005建立VB專案(或Web網站)後，若要進行資料庫存取，我個人比較習慣將連線字串寫在設定檔app.config(或web.config)下。
不過VB專案預設是不會產生這個檔，而且在程式中若要取得連線字串的設定也要對專案的參考進行設定。
以下記錄下我做的設定，並把資料庫存取程式簡化為函式。
開發環境
- VB.Net 2.0 (VS2005)
- MSSQL2005 Express
專案設定
- 建立專案後預設的專案屬性下，參考的元件如下圖：
- 下面的程式中會用到【ConfigurationManager】這個物件來取得設定檔的連線字串，所以我們需要加入【System.Configuration】這個元件：
點選加入，在.Net分頁下找到【System.Configuration】這個元件
- 再來要在專案加入新項目【應用程式組態】，名稱用預設的【app.config】就可以了：
-
編輯app.config，在【configuration】標籤內加入連線字串的設定如：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
...
<connectionStrings>
<add name="MSSQLDB1"
connectionString="Data Source=localhost;Initial Catalog=MyTestDB;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="MSSQLDB2"
connectionString="Data Source=127.0.0.1;Initial Catalog=MyTestDB;User ID=sa;Password=1234"
providerName="System.Data.SqlClient" />
</connectionStrings>
...
通用存取函式類別
Imports System.Data.SqlClient
Imports System.Configuration
Public Class DBAccessFunc
''' <summary>
''' 從ConfigurationManager中的ConnectionStrings找出Initial Catalog
''' </summary>
''' <param name="ConnName">ConnectionStrings名稱(String)</param>
''' <returns>回傳資料庫名稱</returns>
''' <remarks>從ConfigurationManager中的ConnectionStrings找出Initial Catalog</remarks>
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
''' <summary>
''' 查詢資料庫
''' </summary>
''' <param name="cn">連線</param>
''' <param name="sql">查詢內容</param>
''' <returns>回傳查詢結果的DataTable</returns>
''' <remarks>查詢資料庫並回傳查詢結果的DataTable</remarks>
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
''' <summary>
''' 執行命令
''' </summary>
''' <param name="cn">連線</param>
''' <param name="sql">查詢內容</param>
''' <returns>回傳受影響的資料筆數</returns>
''' <remarks>執行命令並回傳受影響的資料筆數</remarks>
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
''' <summary>
''' 取得單一資料
''' </summary>
''' <param name="cn">連線</param>
''' <param name="sql">查詢內容</param>
''' <returns>回傳受影響的資料筆數</returns>
''' <remarks>執行命令並回傳受影響的資料筆數</remarks>
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
