ObjectDataSouce簡介Part 2：範例程式

2008-06-30

18431
0
(.NET)ADO.NET
2017-03-28

承繼上篇【ObjectDataSouce簡介Part1：兼具ADO.NET的自由與DataSouce的方便】，我們這篇就來拿個實際範例，告訴各位如何撰寫相關的程式。

首先介紹上次提到的這樣的方式

先示範如何撰寫類別的部分。

首先，小喵建立一個物件用來讀取Connection String，未來無論Connection String用什麼方式來存放(存放的方式很多，可以直接放在物件中，也可放在Web.Config，小喵自己是存在沒有Web分享的硬碟裡)，都可以透過這個物件來取得Connection String。

在專案中新增一個類別，取名為【objConnS.vb】，內容如下：


Imports Microsoft.VisualBasic

Public Class objConnS
    Private m_ConnStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;User Instance=True"

    Public ReadOnly Property ConnStr() As String
        Get
            Return m_ConnStr
        End Get
    End Property
End Class

接著開始撰寫資料存取的類別(我們以北風資料庫中的Customers當作示範，示範時只取其中四個欄位當範例)，小喵取名為【daoCustomer.vb】，裡面寫了5個副程式，其中兩個Select用的用Function，剩下3個新增修改刪除不需要傳回值，就用Sub即可(當然如果想傳回值用Function也可)。程式內容如下：

GetAllCusts()：用DataTable傳回所有的Customers
GetCustByID(ByVal CustomerID As String)：用參數傳入條件CustomerID，傳回符合條件的一筆，這可以用來當作是單筆維護的處理
UpdateCustomers,InsertCustomers,DeleteCustomers這三個則是透過參數傳遞要新增修改刪除的相關資料，然後透過ADO.NET維護資料。


Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic

Public Class daoCustomer

    Private oConnS As New objConnS
    Private ConnStr As String = oConnS.ConnStr


    Public Function GetAllCusts() As DataTable
        Try
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " SELECT  CustomerID,  CompanyName,  ContactName,  City  "
                SqlTxt += " FROM Customers "
                SqlTxt += "  "

                Dim Dt As New DataTable
                Using Cmmd As New SqlCommand(SqlTxt, Conn)
                    Dt.Load(Cmmd.ExecuteReader)
                End Using
                Return Dt

            End Using
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function

    Public Function GetCustByID(ByVal CustomerID As String) As DataTable
        Try
            If CustomerID Is Nothing Then
                CustomerID = ""
            End If
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " SELECT CustomerID,  CompanyName,  ContactName,  City "
                SqlTxt += " FROM Customers "
                If CustomerID <> "" Then
                    SqlTxt += " WHERE CustomerID = @CustomerID "
                End If
                SqlTxt += "  "
                Dim Dt As New DataTable
                Using Cmmd As New SqlCommand(SqlTxt, Conn)
                    If CustomerID <> "" Then
                        Cmmd.Parameters.AddWithValue("@CustomerID", CustomerID)
                    End If
                    Dt.Load(Cmmd.ExecuteReader)
                End Using
                Return Dt

            End Using
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try

    End Function

    Public Sub UpdateCustomers(ByVal CustomerID As String, ByVal CompanyName As String, ByVal ContactName As String, ByVal City As String)
        Try
            If CustomerID Is Nothing Then
                CustomerID = ""
            End If
            If CompanyName Is Nothing Then
                CompanyName = ""
            End If
            If ContactName Is Nothing Then
                ContactName = ""
            End If
            If City Is Nothing Then
                City = ""
            End If
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " UPDATE Customers "
                SqlTxt += " SET CompanyName = @CompanyName "
                SqlTxt += "     ,ContactName = @ContactName "
                SqlTxt += "     ,City = @City "
                SqlTxt += " WHERE CustomerID = @CustomerID "
                SqlTxt += "  "
                Using Cmmd As New SqlCommand(SqlTxt, Conn)
                    Cmmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                    Cmmd.Parameters.AddWithValue("@ContactName", ContactName)
                    Cmmd.Parameters.AddWithValue("@City", City)
                    Cmmd.Parameters.AddWithValue("@CustomerID", CustomerID)
                    Cmmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub InsertCustomers(ByVal CustomerID As String, ByVal CompanyName As String, ByVal ContactName As String, ByVal City As String)
        Try
            If CustomerID Is Nothing Then
                CustomerID = ""
            End If
            If CompanyName Is Nothing Then
                CompanyName = ""
            End If
            If ContactName Is Nothing Then
                ContactName = ""
            End If
            If City Is Nothing Then
                City = ""
            End If

            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " INSERT INTO Customers "
                SqlTxt += "     (CustomerID, CompanyName, ContactName, City) "
                SqlTxt += " VALUES (@CustomerID, @CompanyName, @ContactName, @City) "
                SqlTxt += "  "
                SqlTxt += "  "
                Using Cmmd As New SqlCommand(SqlTxt, Conn)
                    Cmmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                    Cmmd.Parameters.AddWithValue("@ContactName", ContactName)
                    Cmmd.Parameters.AddWithValue("@City", City)
                    Cmmd.Parameters.AddWithValue("@CustomerID", CustomerID)
                    Cmmd.ExecuteNonQuery()

                End Using
            End Using
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub DeleteCustomers(ByVal CustomerID As String)
        Try
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " DELETE Customers "
                SqlTxt += " WHERE CustomerID = @CustomerID "
                SqlTxt += "  "
                Using Cmmd As New SqlCommand(SqlTxt, Conn)
                    Cmmd.Parameters.AddWithValue("@CustomerID", CustomerID)
                    Cmmd.ExecuteNonQuery()

                End Using
            End Using
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub
End Class

其中為了避免程式一開始，傳遞的參數是Nothing造成程式的錯誤，因此在程式中判斷如果傳入的參數是Nothing，就給預設值。

寫好了物件後，接著就來看怎麼編輯畫面，怎麼設定ObjectDataSouce，怎麼讓物件控制相透過我們寫好的程式動起來。這部分請參考以下的錄影說明。(包含畫面設定、錯誤處理等)

而如果是結合商用物件的方式