ObjectDataSouce結合物件設計方式

小喵以前撰寫ObjectDataSouce都是透過DataSet,DataTable,DataReader的方式傳回值,而要進行維護的動作,也是一個一個的參數慢慢傳遞,後來小喵看到Jeff大大的這篇文章【ObjectDataSoruce 繫結 BusinessObject 控制項】,原來可以用物件的方式來傳遞,於是小喵今天開始著手看看是否能夠改用物件的方式處理。

小喵以前撰寫ObjectDataSouce都是透過DataSet,DataTable,DataReader的方式傳回值,而要進行維護的動作,也是一個一個的參數慢慢傳遞,後來小喵看到Jeff大大的這篇文章【ObjectDataSoruce 繫結 BusinessObject 控制項】,原來可以用物件的方式來傳遞,於是小喵今天開始著手看看是否能夠改用物件的方式處理。

首先小喵先設計一個測試的資料表,資料表(Employees)欄位如下圖

ODS001

接著就針對這個資料表,建立一個Employee的類別,未來這個物件類別可以抽出來當作是商業邏輯層或者資料層的元件,並撰寫相關的新增、修改、刪除、查詢等功能,相關程式碼如下:

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

Public Class ObjEmployee
    
    ''' <summary>
    ''' 員工編號欄位
    ''' </summary>
    Private m_EmployeeID As Integer
    ''' <summary>
    ''' 員工姓名欄位
    ''' </summary>
    Private m_EmpName As String
    ''' <summary>
    ''' 員工電話欄位
    ''' </summary>
    Private m_EmpTel As String

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

    ''' <summary>
    ''' 員工編號屬性
    ''' </summary>
    Public Property EmployeeID() As Integer
        Get
            Return m_EmployeeID
        End Get
        Set(ByVal value As Integer)
            m_EmployeeID = value
        End Set
    End Property

    ''' <summary>
    ''' 員工姓名屬性
    ''' </summary>
    Public Property EmpName() As String
        Get
            Return m_EmpName
        End Get
        Set(ByVal value As String)
            m_EmpName = value
        End Set
    End Property

    ''' <summary>
    ''' 員工電話屬性
    ''' </summary>
    Public Property EmpTel() As String
        Get
            Return m_EmpTel
        End Get
        Set(ByVal value As String)
            m_EmpTel = value
        End Set
    End Property

    '建構函數
    Public Sub New()

    End Sub

    Public Sub New(ByVal myEmpID As Integer)
        GetEmp(myEmpID)
    End Sub

    Private Sub GetEmp(ByVal myEmpID As Integer)
        Try
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " SELECT * "
                SqlTxt += " FROM Employees "
                SqlTxt += " WHERE EmployeeID = @EmployeeID "
                Dim Cmmd As New SqlCommand(SqlTxt, Conn)
                Cmmd.Parameters.AddWithValue("@EmployeeID", myEmpID)
                Dim dr As SqlDataReader = Cmmd.ExecuteReader
                If dr.HasRows Then
                    While dr.Read
                        m_EmployeeID = myEmpID
                        m_EmpName = dr.Item("EmpName")
                        m_EmpTel = dr.Item("EmpTel")
                    End While
                End If
                dr.Close()
            End Using

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

    ''' <summary>
    ''' 新增一筆Employee
    ''' </summary>
    Public Sub Add()
        Try
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " INSERT INTO Employees "
                SqlTxt += "     (EmpName, EmpTel) "
                SqlTxt += " VALUES (@EmpName, @EmpTel) "
                SqlTxt += "  "
                Dim Cmmd As New SqlCommand(SqlTxt, Conn)
                Cmmd.Parameters.AddWithValue("@EmpName", m_EmpName)
                Cmmd.Parameters.AddWithValue("@EmpTel", m_EmpTel)

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

        End Try
    End Sub

    ''' <summary>
    ''' 刪除一筆Employee
    ''' </summary>
    Public Sub Del()
        Try
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " Delete Employees "
                SqlTxt += " WHERE EmployeeID=@EmployeeID "
                SqlTxt += "  "
                Dim Cmmd As New SqlCommand(SqlTxt, Conn)
                Cmmd.Parameters.AddWithValue("@EmployeeID", m_EmployeeID)

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

        End Try

    End Sub

    ''' <summary>
    ''' 修改單筆Employee
    ''' </summary>
    Public Sub Update()
        Try
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " UPDATE Employees "
                SqlTxt += " SET EmpName=@EmpName "
                SqlTxt += "     , EmpTel=@EmpTel  "
                SqlTxt += " WHERE EmployeeID=@EmployeeID "
                SqlTxt += "  "
                Dim Cmmd As New SqlCommand(SqlTxt, Conn)
                Cmmd.Parameters.AddWithValue("@EmployeeID", m_EmployeeID)
                Cmmd.Parameters.AddWithValue("@EmpName", m_EmpName)
                Cmmd.Parameters.AddWithValue("@EmpTel", m_EmpTel)

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

        End Try

    End Sub
End Class

另外為了方便處理Connection String,也寫了個小類別來存放

Imports Microsoft.VisualBasic

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

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

End Class

再來設計一個配合ObjectDataSouce的物件,進行新增、修改、刪除、查詢的動作

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

Public Class daoEmployee
    Private oConnS As New objConnS
    Private ConnStr As String = oConnS.ConnStr

    Public Function GetAllEmployee() As List(Of ObjEmployee)
        Try
            Dim tEmps As New List(Of ObjEmployee)
            tEmps.Clear()
            Using Conn As New SqlConnection(ConnStr)
                Conn.Open()
                Dim SqlTxt As String = ""
                SqlTxt += " SELECT * "
                SqlTxt += " FROM Employees "
                Dim Cmmd As New SqlCommand(SqlTxt, Conn)
                Dim Dr As SqlDataReader = Cmmd.ExecuteReader
                If Dr.HasRows Then
                    Dim tEmp As ObjEmployee
                    While Dr.Read
                        tEmp = New ObjEmployee(Dr.Item("EmployeeID"))
                        tEmps.Add(tEmp)
                    End While
                End If
            End Using
            Return tEmps

        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Sub EmpUpdate(ByVal oEmp As ObjEmployee)
        Try
            oEmp.Update()
        Catch ex As Exception
            Throw
        End Try
    End Sub

    Public Sub EmpDel(ByVal oEmp As ObjEmployee)
        Try
            oEmp.Del()
        Catch ex As Exception
            Throw
        End Try
    End Sub

    Public Sub EmpAddNew(ByVal oEmp As ObjEmployee)
        Try
            oEmp.Add()
        Catch ex As Exception
            Throw
        End Try
    End Sub

End Class

有趣的地方有看到嗎,GetAllEmployee傳回的不再是DataSet,DataTable,DataRead,而是objEmployee的物件集合,另外,新增修改刪除的程式碼精減到一個不行。傳遞的參數沒有其他的,就是物件,而運作的,就是物件提供的新增修改刪除。

接著設計一下測試的畫面

        <asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="False" 
            DataSourceID="odsEmployees" DataKeyNames="EmployeeID">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
                    SortExpression="EmployeeID" />
                <asp:BoundField DataField="EmpName" HeaderText="EmpName" 
                    SortExpression="EmpName" />
                <asp:BoundField DataField="EmpTel" HeaderText="EmpTel" 
                    SortExpression="EmpTel" />
            </Columns>
        </asp:GridView>
        <asp:ObjectDataSource ID="odsEmployees" runat="server" 
            DataObjectTypeName="ObjEmployee" DeleteMethod="EmpDel" 
            SelectMethod="GetAllEmployee" TypeName="daoEmployee" 
            UpdateMethod="EmpUpdate" InsertMethod="EmpAddNew">
        </asp:ObjectDataSource>
        <asp:DetailsView ID="dvEmployee" runat="server" AutoGenerateRows="False" 
            DataSourceID="odsEmployees" DefaultMode="Insert" Height="50px" 
            Width="125px" DataKeyNames="EmployeeID">
            <Fields>
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
                    InsertVisible="False" SortExpression="EmployeeID" />
                <asp:BoundField DataField="EmpName" HeaderText="EmpName" 
                    SortExpression="EmpName" />
                <asp:BoundField DataField="EmpTel" HeaderText="EmpTel" 
                    SortExpression="EmpTel" />
                <asp:CommandField ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
        <br />

唯一畫面要寫的程式是,在DetailView的資料新增之後,要讓GridView重新整理一次

    Protected Sub dvEmployee_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles dvEmployee.ItemInserted
        Me.gvEmployees.DataBind()
    End Sub

這樣就能夠使用物件的方式來處理並且方便把物件抽離出去,並且不用寫ObjectDataSouce的搭配物件時,需要撰寫一堆傳遞的參數。

 


以下是簽名:


Microsoft MVP
Visual Studio and Development Technologies
(2005~2019/6)