小喵以前撰寫ObjectDataSouce都是透過DataSet,DataTable,DataReader的方式傳回值，而要進行維護的動作，也是一個一個的參數慢慢傳遞，後來小喵看到Jeff大大的這篇文章【ObjectDataSoruce 繫結 BusinessObject 控制項】，原來可以用物件的方式來傳遞，於是小喵今天開始著手看看是否能夠改用物件的方式處理。
小喵以前撰寫ObjectDataSouce都是透過DataSet,DataTable,DataReader的方式傳回值，而要進行維護的動作，也是一個一個的參數慢慢傳遞，後來小喵看到Jeff大大的這篇文章【ObjectDataSoruce 繫結 BusinessObject 控制項】，原來可以用物件的方式來傳遞，於是小喵今天開始著手看看是否能夠改用物件的方式處理。
首先小喵先設計一個測試的資料表，資料表(Employees)欄位如下圖
接著就針對這個資料表，建立一個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的搭配物件時，需要撰寫一堆傳遞的參數。
以下是簽名：
- 歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
- 小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考
| Microsoft MVP Visual Studio and Development Technologies (2005~2019/6) | topcat Blog:http://www.dotblogs.com.tw/topcat |
