燃燒吧~~ObjectDataSouce配合物件導向的設計方式連結資料 VB.NET

2008-08-05

3884
0
ADO.NET
2008-09-21

這一篇的靈感來自於Topcat -->ObjectDataSouce結合物件設計方式

進化版！

當然我們先假設

1.資料表越來越多 ... 能夠再把重複的地方抽離嗎？

2.依照Topcat的方式 ... 每產生一個 objEmployee 物件就要Select一次資料 ...好像對資料庫有點負擔 ..

基於以上兩點，作為開發的動力！

這一篇的靈感來自於Topcat -->ObjectDataSouce結合物件設計方式

進化版！

當然我們先假設

1.資料表越來越多 ... 能夠再把重複的地方抽離嗎？

2.依照Topcat的方式 ... 每產生一個 objEmployee 物件就要Select一次資料 ...好像對資料庫有點負擔 ..

基於以上兩點，作為開發的動力！

首先 ... 我們把增刪改三種方法抽離

寫成一個objADU 的類別(恩= =、我是取開頭 ... Add Del Update)

當然以下所使用到的objData 物件在之前的文章有提到過

參考如下-->ASP.NET 2.0抽離資料存取 & 連線字串-VB.NET

01 ''' <summary>
02 ''' 增刪改核心元件，資料存取層元件。 By Phoehix - 2008
03 ''' </summary>
04 ''' <remarks></remarks>
05

Public Class objADU
06

Private oData As New objData()
07

Protected Sub Add(ByRef SqlTxt As String, ByVal Parameter As SqlParameter)
09

Try
10

oData.SqlExecuteNonQuery(SqlTxt, Parameter)
11

Catch ex As Exception
12

Throw
13

End Try
14

End Sub
15

Protected Sub Add(ByRef SqlTxt As String, ByVal Parameters As SqlParameter())
16

Try
17

oData.SqlExecuteNonQuery(SqlTxt, Parameters)
18

Catch ex As Exception
19

Throw
20

End Try
21

End Sub
22

Protected Sub Del(ByRef SqlTxt As String, ByRef Parameter As SqlParameter)
23

Try
24

oData.SqlExecuteNonQuery(SqlTxt, Parameter)
25

Catch ex As Exception
26

Throw
27

End Try
28

End Sub
29

Protected Sub Del(ByRef SqlTxt As String, ByRef Parameters As SqlParameter())
30

Try
31

oData.SqlExecuteNonQuery(SqlTxt, Parameters)
32

Catch ex As Exception
33

Throw
34

End Try
35

End Sub
36

Protected Sub Update(ByRef SqlTxt As String, ByRef Parameter As SqlParameter)
37

Try
38

oData.SqlExecuteNonQuery(SqlTxt, Parameter)
39

Catch ex As Exception
40

Throw
41

End Try
42

End Sub
43

Protected Sub Update(ByRef SqlTxt As String, ByRef Parameters As SqlParameter())
44

Try
45

oData.SqlExecuteNonQuery(SqlTxt, Parameters)
46

Catch ex As Exception
47

Throw
48

End Try
49

End Sub
50

Public Sub New()
52

End Sub
54

Protected Overrides Sub Finalize()
56

MyBase.Finalize()
57

End Sub
58

End Class

6-宣告一個 oData 物件為objData 順便做初始化

當然永續發展為考量 ... 增刪改這三種方法都用多載的方式 ...(傳入一個參數 OR 傳入多個參數)

8~21 執行 Add 傳入SQL字串和預存程序

22~35 執行 Del 傳入SQL字串和預存程序

36~49 執行 Update傳入SQL字串和預存程序

51~57 請自動省略 ...

再來我們以一個資料表做為範例 News 新聞公告，其他的靠自己~

資料表的設計如下：

N_ID 是新聞的編號自動遞增

N_Date 是發布日期

N_Subject 是新聞標題

N_Content 是新聞內容

以下是 objNews 的"物件模型"

001 ''' <summary>
002 ''' News 資料庫物件，資料存取層物件。 By Phoehix - 2008
003 ''' </summary>
004

Public Class objNews
005

Inherits objADU
006

''' <summary>
007

''' 新聞公告編號
008

''' </summary>
009

Private _ID As Integer
010

''' <summary>
011

''' 發布時間
012

''' </summary>
013

''' <remarks></remarks>
014

Private _Date As Date
015

''' <summary>
016

''' 標題欄位
017

''' </summary>
018

Private _Subject As String
019

''' <summary>
020

''' 內容欄位
021

''' </summary>
022

Private _Content As String
023

024

''' <summary>
025

''' 取得或設定新聞公告的編號(_ID)
026

''' </summary>
027

Public Property NewsID() As Integer
028

Get
029

Return _ID
030

End Get
031

Set(ByVal value As Integer)
032

_ID = value
033

End Set
034

End Property
035

''' <summary>
036

''' 取得新聞公告發布時間(_Date)
037

''' </summary>
038

Public ReadOnly Property NewsDate() As Date
039

Get
040

Return _Date
041

End Get
042

End Property
043

''' <summary>
044

''' 取得或設定新聞公告的標題(_Subject)
045

''' </summary>
046

Public Property NewsSubject() As String
047

Get
048

Return _Subject
049

End Get
050

Set(ByVal value As String)
051

_Subject = value
052

End Set
053

End Property
054

''' <summary>
055

''' 取得或設定新聞公告的內容(_Content)
056

''' </summary>
057

Public Property NewsContent() As String
058

Get
059

Return _Content
060

End Get
061

Set(ByVal value As String)
062

_Content = value
063

End Set
064

End Property
065

''' <summary>
066

''' 初始化 objNews 類別的新執行個體
067

''' </summary>
068

Public Sub New()
069

070

End Sub
071

''' <param name="row"> DataRow。</param>
072

Public Sub New(ByRef row As DataRow)
073

GetNews(row)
074

End Sub
075

Private Sub GetNews(ByRef row As DataRow)
076

Try
077

_ID = Integer.Parse(row(0).ToString())
078

_Date = Date.Parse(row(1).ToString())
079

_Subject = row(2).ToString()
080

_Content = row(3).ToString()
081

Catch ex As Exception
082

Throw New Exception(ex.Message)
083

End Try
084

End Sub
085

''' <summary>
086

''' 新增一筆消息
087

''' </summary>
088

Public Overloads Sub Add()
089

Try
090

091

Dim SqlTxt As String = "INSERT INTO [News] ([N_Subject], [N_Content]) VALUES (@N_Subject, @N_Content)"
092

Dim Parameters(1) As SqlParameter
093

Parameters(0) = New SqlParameter("@N_Subject", _Subject)
094

Parameters(1) = New SqlParameter("@N_Content", _Content)
095

Add(SqlTxt, Parameters)
096

Catch ex As Exception
097

Throw New Exception(ex.Message)
098

End Try
099

End Sub
100

''' <summary>
101

''' 刪除一筆消息
102

''' </summary>
103

Public Overloads Sub Del()
104

Try
105

Dim SqlTxt As String = "Delete [News] WHERE ([N_ID] = @Original_N_ID)"
106

'Original
107

Dim Parameter As SqlParameter = New SqlParameter("@Original_N_ID", _ID)
108

Del(SqlTxt, Parameter)
109

Catch ex As Exception
110

Throw New Exception(ex.Message)
111

End Try
112

113

End Sub
114

''' <summary>
115

''' 修改單筆消息
116

''' </summary>
117

Public Overloads Sub Update()
118

Try
119

Dim SqlTxt As String = "UPDATE News SET N_Subject = @N_Subject, N_Content = @N_Content WHERE N_ID = @Original_N_ID"
120

Dim Parameters(2) As SqlParameter
121

'New Set
122

Parameters(0) = New SqlParameter("@N_Subject", _Subject)
123

Parameters(1) = New SqlParameter("@N_Content", _Content)
124

'Original
125

Parameters(2) = New SqlParameter("@Original_N_ID", _ID)
126

Update(SqlTxt, Parameters)
127

Catch ex As Exception
128

Throw New Exception(ex.Message)
129

End Try
130

End Sub
131

132

Protected Overrides Sub Finalize()
133

MyBase.Finalize()
134

End Sub
135

End Class

首先

再第五行的地方繼承objADU (真的吃撐了= = 分離出來多寫好多哈)

再來前面的部份都跟TopCat設計的類似

只有在 72~74的地方本來是傳 ID過來換成傳遞row過來

然後再75~84的部份由傳入的row 提取資料

再來88~130的部份 Add,Del,Update 改為 Overloads 因為繼承自objADU 已經有相同名稱的方法了

裡面就是把 SqlTxt 和 Parameter(預存程序) 丟給objADU 執行相對應的 Add,Del,Update

再來是配合ObjectDataSouce的物件，進行新增、修改、刪除、查詢的動作. 也可以把它當作 BLL層 ...

01 ''' <summary>
02 ''' News 資料庫，商業邏輯層物件。 By Phoehix - 2008
03 ''' </summary>
04

Public Class daoNews
05

''' <summary>
06

''' 取得News物件集合
07

''' </summary>
08

''' <returns>List(Of DAL.objNews)</returns>
09

Public Function GetNews() As List(Of DAL.objNews)
10

Try
11

Dim rNews As New List(Of DAL.objNews)
12

rNews.Clear()
13

Dim oData As New DAL.objData()
14

Dim SqlTxt As String = "SELECT * From [News] ORDER BY [N_Date] DESC"
15

Dim dt As New DataTable
16

If oData.SqlSelectQuery(SqlTxt, dt) Then
17

For Each row As DataRow In dt.Rows
18

rNews.Add(New DAL.objNews(row))
19

Next
20

End If
21

Return rNews
22

Catch ex As Exception
23

Throw New Exception(ex.Message)
24

End Try
25

End Function
26

''' <param name="NID">新聞公告編號</param>
27

''' <returns></returns>
28

''' <remarks></remarks>
29

Public Function GetNews(ByVal NID As Integer) As List(Of DAL.objNews)
30

Try
31

Dim rNews As New List(Of DAL.objNews)
32

rNews.Clear()
33

Dim oData As New DAL.objData()
34

Dim SqlTxt As String = "SELECT * From [News] WHERE N_ID = @N_ID"
35

Dim dt As New DataTable
36

Dim Parameter As SqlParameter = New SqlParameter("@N_ID", NID)
37

If oData.SqlSelectQuery(SqlTxt, Parameter, dt) Then
38

rNews.Add(New DAL.objNews(dt.Rows(0)))
39

End If
40

Return rNews
41

Catch ex As Exception
42

Throw New Exception(ex.Message)
43

End Try
44

End Function
45

Public Sub NewsUpdate(ByVal oNews As DAL.objNews)
47

Try
48

oNews.Update()
49

Catch ex As Exception
50

Throw New Exception(ex.Message)
51

End Try
52

End Sub
53

Public Sub NewsDel(ByVal oNews As DAL.objNews)
55

Try
56

oNews.Del()
57

Catch ex As Exception
58

Throw New Exception(ex.Message)
59

End Try
60

End Sub
61

Public Sub NewsAddNew(ByVal oNews As DAL.objNews)
63

Try
64

oNews.add()
65

Catch ex As Exception
66

Throw New Exception(ex.Message)
67

End Try
68

End Sub
69

Public Sub New()
71

End Sub
73

Protected Overrides Sub Finalize()
75

MyBase.Finalize()
76

End Sub
77

End Class

和TopCat不同的地方在

16-把 SQL 和dt 傳過去給我們的objData類別把資料都填入dt 再傳回成功/失敗

17-For Each的方式把DT內的ROW 一一取出

18-把objNews 加入到物件集合中，把Row 傳遞過去這樣就可以減少對SQL Server的負擔了 ...

再來的程式碼都一樣 ... 比較值得一提的是

因為我把 objNews 寫成一個命名空間為DAL的類別庫(objConn,objData,objADU 也都是)

關於ObjectDataSource 在MSDN內有提到過這種方法 --> ObjectDataSource 強型別來源物件範例

以上

Phoenix 8/5

ASP.NET

回首頁