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

序言

在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

這裡是關於技術的手札~

也歡迎大家到

倫與貓的足跡



到噗浪來

關心一下我唷!
by 倫
 
Copyright 2009 倫倫3號Beta-Log All rights reserved.
Blogger Templates created by Deluxe Templates
Wordpress Theme by EZwpthemes