Phoenix.net 迷之殿

多層式架構就是 (UI <--> BLL <--> DAL <--> DataBase)

繼承於之前的文章--> ASP.NET 2.0抽離資料存取 & 連線字串-VB.NET

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

其實 這個都不算是完全的四層式架構 ... 因為 SQL 指令 在兩層都有(BLL DAL)

厄 其實應該是在 都要在 同一層才對 ...(書上是說 要寫在資料存取層)

不過 ... 我的作法是把SQL 指令 都放在商業邏輯層 再傳過去給 資料存取層 ...

希望大家可以交流一下 ^^

首先 先新增一個自訂的 Enum.vb 列舉資料型態

1 ''' <summary>
2 ''' 列舉資料庫類型  By Phoehix - 2008
3 ''' </summary>
4 Public Enum dbType
5     MSSQL = 0
6     OleDB = 1
7 End Enum

再來是修改objADU的部份

01 ''' <summary>
02 ''' 增刪改核心元件，資料存取層元件。  By Phoehix - 2008
03 ''' </summary>
04 ''' <remarks></remarks>
05 Public Class objADU
06     Private oData As New objData() oData As New objData()
07     Public Sub ADU() Sub ADU(ByRef SqlTxt As String, ByRef Parameter As DbParameter, ByVal Type As dbType)
08         Select Case Type
09             Case dbType.MSSQL
10                 MSSQL(SqlTxt, CType(Parameter, SqlParameter))
11
12         End Select
13     End Sub
14     Public Sub ADU() Sub ADU(ByRef SqlTxt As String, ByRef Parameters As DbParameter(), ByVal Type As dbType)
15         Select Case Type
16             Case dbType.MSSQL
17                 Dim SP(Parameters.Length - 1) As SqlParameter
18                 For i As Integer = 0 To Parameters.Length - 1
19                     SP(i) = CType(Parameters(i), SqlParameter)
20                 Next
21                 MSSQL(SqlTxt, SP)
22
23         End Select
24     End Sub
25
26     Private Sub MSSQL() Sub MSSQL(ByRef SqlTxt As String, ByRef Parameter As SqlParameter)
27         Try
28             oData.SqlExecuteNonQuery(SqlTxt, Parameter)
29         Catch ex As Exception
30             Throw
31         End Try
32     End Sub
33     Private Sub MSSQL() Sub MSSQL(ByRef SqlTxt As String, ByRef Parameters As SqlParameter())
34         Try
35             oData.SqlExecuteNonQuery(SqlTxt, Parameters)
36         Catch ex As Exception
37             Throw
38         End Try
39     End Sub
40
41     Public Sub New() Sub New()
42
43     End Sub
44
45     Protected Overrides Sub Finalize() Overrides Sub Finalize()
46         MyBase.Finalize()
47     End Sub
48 End Class 

這邊寫的更精簡了，因為其實 Add,Del,Update 在做的事情都是一樣 把SQL命令 和 預存 傳給objData類別 

7~13 用Public 的 ADU方法(記得要Public 才呼叫的到哦) 傳入 Sql 命令、單一Parameter (這邊使用的是DbParameter，所有類型的Parameter都是繼承於他，所以可以用多型的方式)、資料庫列舉型態

有關於 DbParameter 參考MSDN -> DbParameter 類別

按照列舉型態 用SelectCase的方式呼叫該方法，先把Parameter做轉換，再把值都傳過去

14~24 跟7~13不同的地方在傳入的是DbParameter的集合

因為，DbParameter 的多型集合 要一個一個轉換 ，所以再這邊寫了一個小小的For迴圈

把值 轉換過後丟給SP(SqlParameter)然後再呼叫該方法

26~32 & 33~39 這邊就不再多做解釋了

當然 都是可以做擴充的~

objData 可以不用做變動

但是 我還是做了一些小修正 都加上了防止SQL Injection 和 未使用預存程序的 SQL命令

在Public Function SqlSelectQuery 內 加入

1         sql.Trim()
2         If sql.Contains("'") OrElse sql.Contains("--") Then
3             Return False
4             Exit Function
5         End If

在 Public Function SqlExecuteNonQuery 內 加入

1         sql.Trim()
2         If sql.Contains("'") OrElse sql.Contains("--") Then
3             Return result
4             Exit Function
5         End If

再來 我們以新聞公告這個類別 來做範例 (舉一反三囉！) 

objNews.vb

01 ''' <summary>
02 ''' News 資料庫物件，資料存取層物件。 By Phoehix - 2008
03 ''' </summary>
04 Public Class objNews
05     Inherits objADU
06     ''' <summary>
07     ''' 新聞公告編號
08     ''' </summary>
09     Private _ID As Integer
10     ''' <summary>
11     ''' 發布時間
12     ''' </summary>
13     ''' <remarks></remarks>
14     Private _Date As Date
15     ''' <summary>
16     ''' 標題欄位
17     ''' </summary>
18     Private _Subject As String
19     ''' <summary>
20     ''' 內容欄位
21     ''' </summary>
22     Private _Content As String
23
24     ''' <summary>
25     ''' 取得或設定新聞公告的編號(_ID)
26     ''' </summary>
27     Public Property NewsID() Property NewsID() As Integer
28         Get
29             Return _ID
30         End Get
31         Set(ByVal value As Integer)
32             _ID = value
33         End Set
34     End Property
35     ''' <summary>
36     ''' 取得新聞公告發布時間(_Date)
37     ''' </summary>
38     Public ReadOnly Property NewsDate() ReadOnly Property NewsDate() As Date
39         Get
40             Return _Date
41         End Get
42     End Property
43     ''' <summary>
44     ''' 取得或設定新聞公告的標題(_Subject)
45     ''' </summary>
46     Public Property NewsSubject() Property NewsSubject() As String
47         Get
48             Return _Subject
49         End Get
50         Set(ByVal value As String)
51             _Subject = value
52         End Set
53     End Property
54     ''' <summary>
55     ''' 取得或設定新聞公告的內容(_Content)
56     ''' </summary>
57     Public Property NewsContent() Property NewsContent() As String
58         Get
59             Return _Content
60         End Get
61         Set(ByVal value As String)
62             _Content = value
63         End Set
64     End Property
65     ''' <summary>
66     ''' 初始化 objNews 類別的新執行個體
67     ''' </summary>
68     Public Sub New() Sub New()
69
70     End Sub
71     ''' <param name="row"> DataRow。</param>
72     Public Sub New() Sub New(ByRef row As DataRow)
73         GetNews(row)
74     End Sub
75     Private Sub GetNews() Sub GetNews(ByRef row As DataRow)
76         Try
77             _ID = Integer.Parse(row(0).ToString())
78             _Date = Date.Parse(row(1).ToString())
79             _Subject = row(2).ToString()
80             _Content = row(3).ToString()
81         Catch ex As Exception
82             Throw New Exception(ex.Message)
83         End Try
84     End Sub
85     Protected Overrides Sub Finalize() Overrides Sub Finalize()
86         MyBase.Finalize()
87     End Sub
88 End Class

把 Add Del Update 砍掉了~ 全部移動到 daoNews裡面了

daoNews.vb

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() 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() 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
46     Public Sub NewsUpdate() Sub NewsUpdate(ByVal oNews As DAL.objNews)
47         Try
48             Dim SqlTxt As String = "UPDATE News SET N_Subject = @N_Subject, N_Content = @N_Content WHERE N_ID = @Original_N_ID"
49             Dim Parameters(2) As DbParameter
50             'New Set
51             Parameters(0) = New SqlParameter("@N_Subject", oNews.NewsSubject)
52             Parameters(1) = New SqlParameter("@N_Content", oNews.NewsContent)
53             'Original
54             Parameters(2) = New SqlParameter("@Original_N_ID", oNews.NewsID)
55
56             oNews.ADU(SqlTxt, Parameters, DAL.dbType.MSSQL)
57         Catch ex As Exception
58             Throw New Exception(ex.Message)
59         End Try
60     End Sub
61
62     Public Sub NewsDel() Sub NewsDel(ByVal oNews As DAL.objNews)
63         Try
64             Dim SqlTxt As String = "Delete [News] WHERE ([N_ID] = @Original_N_ID)"
65             'Original
66             Dim Parameter As DbParameter = New SqlParameter("@Original_N_ID", oNews.NewsID)
67             oNews.ADU(SqlTxt, Parameter, DAL.dbType.MSSQL)
68         Catch ex As Exception
69             Throw New Exception(ex.Message)
70         End Try
71     End Sub
72
73     Public Sub NewsAddNew() Sub NewsAddNew(ByVal oNews As DAL.objNews)
74         Try
75             Dim SqlTxt As String = "INSERT INTO [News] ([N_Subject], [N_Content]) VALUES (@N_Subject, @N_Content)"
76             Dim Parameters(1) As DbParameter
77             Parameters(0) = New SqlParameter("@N_Subject", oNews.NewsSubject)
78             Parameters(1) = New SqlParameter("@N_Content", oNews.NewsContent)
79             oNews.ADU(SqlTxt, Parameters, DAL.dbType.MSSQL)
80         Catch ex As Exception
81             Throw New Exception(ex.Message)
82         End Try
83     End Sub
84
85     Public Sub New() Sub New()
86
87     End Sub
88
89     Protected Overrides Sub Finalize() Overrides Sub Finalize()
90         MyBase.Finalize()
91     End Sub
92 End Class

在46-Update 62-Del 73-Insert

分別就是 把值 填入預存程序(也是要用 DbParameter) 因為要傳給 objADU.ADU 的是 DbParameter的型態

這邊應該不用多做解釋了 ... 因為 目前是要用MS SQL Server 所以 把值 用 New SqlParameter 填入 DbParameter

基本上就是如此 ... 這是我的作法啦 ~ 如果還有更讚的作法 歡迎大家交流討論看看 

其實 我也是一邊做 一邊學 ...關於架構的部份，其實也是剛認識沒多久 ~

(UI <--> daoNews <--> objNews & objADU <--> objData & objConn)

以上

Phoenix 8/6