小喵以前撰写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的搭配对象时,需要撰写一堆传递的参数。