ASP.NET 2.0抽離資料存取 & 連線字串-VB.NET (修正版)

2008-08-04

5367
0
ASP.NET
2008-09-21

這篇文章 ...是吃撐了 ... 把資料存取和連線字串寫成類別，供以後的程式使用 ...
>>ASP.NET 2.0動態產生TreeView樹狀結構-C# & VB.NET 混合
首先 ... 我寫了一個萬用?!!!的objData 類別 ...如下(這是用VB.NET寫的)

這篇文章 ...是吃撐了 ... 把資料存取和連線字串寫成類別，供以後的程式使用 ...

>>ASP.NET 2.0動態產生TreeView樹狀結構-C# & VB.NET 混合

首先 ... 我寫了一個萬用?!!!的objData 類別 ...如下(這是用VB.NET寫的)

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

Public Class objData
005

Inherits objConn
006

''' <summary>
007

''' 執行SQL Select
008

''' </summary>
009

''' <param name="SQL">SQL查詢字串</param>
010

''' <param name="ds">DataSet</param>
011

''' <returns>成功/失敗</returns>
012

Public Function SqlSelectQuery(ByRef SQL As String, ByRef ds As DataSet) As Boolean
013

If SQL.Contains("@") Then
014

Return False
015

Exit Function
016

End If
017

Try
018

Using SQLConn As New SqlConnection(ConnStr)
019

Dim SqlCmd As New SqlCommand(SQL, SQLConn)
020

Dim da As New SqlDataAdapter(SqlCmd)
021

da.Fill(ds)
022

End Using
023

Return True
024

Catch ex As Exception
025

Return False
026

Finally
027

SQL = Nothing
028

End Try
029

End Function
030

''' <param name="SQL">SQL查詢字串</param>
031

''' <param name="dt">DataTable</param>
032

''' <returns>成功/失敗</returns>
033

Public Function SqlSelectQuery(ByRef SQL As String, ByRef dt As DataTable) As Boolean
034

If SQL.Contains("@") Then
035

Return False
036

Exit Function
037

End If
038

Try
039

Using SQLConn As New SqlConnection(ConnStr)
040

Dim SqlCmd As New SqlCommand(SQL, SQLConn)
041

SQLConn.Open()
042

dt.Load(SqlCmd.ExecuteReader)
043

End Using
044

Return True
045

Catch ex As Exception
046

Return False
047

Finally
048

SQL = Nothing
049

End Try
050

End Function
051

''' <param name="SQL">SQL查詢字串</param>
052

''' <param name="Parameter">單一SQL預存程序</param>
053

''' <param name="ds">DataSet</param>
054

''' <returns>成功/失敗</returns>
055

Public Function SqlSelectQuery(ByRef SQL As String, ByRef Parameter As SqlParameter, ByRef ds As DataSet) As Boolean
056

Try
057

Using SQLConn As New SqlConnection(ConnStr)
058

Dim SqlCmd As New SqlCommand(SQL, SQLConn)
059

SqlCmd.Parameters.Add(Parameter)
060

Dim da As New SqlDataAdapter(SqlCmd)
061

da.Fill(ds)
062

End Using
063

Return True
064

Catch ex As Exception
065

Return False
066

Finally
067

SQL = Nothing
068

Parameter = Nothing
069

End Try
070

End Function
071

''' <param name="SQL">SQL查詢字串</param>
072

''' <param name="Parameter">單一SQL預存程序</param>
073

''' <param name="dt">DataTable</param>
074

''' <returns>成功/失敗</returns>
075

''' <remarks></remarks>
076

Public Function SqlSelectQuery(ByRef SQL As String, ByRef Parameter As SqlParameter, ByRef dt As DataTable) As Boolean
077

Try
078

Using SQLConn As New SqlConnection(ConnStr)
079

Dim SqlCmd As New SqlCommand(SQL, SQLConn)
080

SqlCmd.Parameters.Add(Parameter)
081

SQLConn.Open()
082

dt.Load(SqlCmd.ExecuteReader)
083

End Using
084

Return True
085

Catch ex As Exception
086

Return False
087

Finally
088

SQL = Nothing
089

Parameter = Nothing
090

End Try
091

End Function
092

''' <param name="SQL">SQL查詢字串</param>
093

''' <param name="Parameters">SQL預存程序集合</param>
094

''' <param name="ds">DataSet</param>
095

''' <returns>成功/失敗</returns>
096

Public Function SqlSelectQuery(ByRef SQL As String, ByRef Parameters As SqlParameter(), ByRef ds As DataSet) As Boolean
097

Try
098

Using SQLConn As New SqlConnection(ConnStr)
099

Dim SqlCmd As New SqlCommand(SQL, SQLConn)
100

SqlCmd.Parameters.AddRange(Parameters)
101

Dim da As New SqlDataAdapter(SqlCmd)
102

da.Fill(ds)
103

End Using
104

Return True
105

Catch ex As Exception
106

Return False
107

Finally
108

SQL = Nothing
109

Parameters = Nothing
110

End Try
111

End Function
112

''' <param name="SQL">SQL查詢字串</param>
113

''' <param name="Parameters">SQL預存程序集合</param>
114

''' <param name="dt">DataTable</param>
115

''' <returns>成功/失敗</returns>
116

Public Function SqlSelectQuery(ByRef SQL As String, ByRef Parameters As SqlParameter(), ByRef dt As DataTable) As Boolean
117

Try
118

Using SQLConn As New SqlConnection(ConnStr)
119

Dim SqlCmd As New SqlCommand(SQL, SQLConn)
120

SqlCmd.Parameters.AddRange(Parameters)
121

SQLConn.Open()
122

dt.Load(SqlCmd.ExecuteReader)
123

End Using
124

Return True
125

Catch ex As Exception
126

Return False
127

Finally
128

SQL = Nothing
129

Parameters = Nothing
130

End Try
131

End Function
132

''' <summary>
133

''' 執行Sql命令
134

''' </summary>
135

''' <param name="SQL">SQL查詢字串</param>
136

''' <returns>引響的行數</returns>
137

Public Function SqlExecuteNonQuery(ByRef SQL As String) As Integer
138

Dim result As Integer = 0
139

If SQL.Contains("@") Then
140

Return result
141

Exit Function
142

End If
143

Try
144

Using SQLConn As New SqlConnection(ConnStr)
145

Dim SqlCmd As New SqlCommand(SQL, SQLConn)
146

SQLConn.Open()
147

result = SqlCmd.ExecuteNonQuery()
148

End Using
149

Return result
150

Catch ex As Exception
151

Return result
152

Finally
153

SQL = Nothing
154

End Try
155

End Function
156

''' <param name="sql">SQL查詢字串<</param>
157

''' <param name="Parameter">SQL預存程序</param>
158

''' <returns>引響的行數</returns>
159

Public Function SqlExecuteNonQuery(ByRef sql As String, ByRef Parameter As SqlParameter) As Integer
160

Dim result As Integer = 0
161

Try
162

Using SQLConn As New SqlConnection(ConnStr)
163

Dim SqlCmd As New SqlCommand(sql, SQLConn)
164

SqlCmd.Parameters.Add(Parameter)
165

SQLConn.Open()
166

result = SqlCmd.ExecuteNonQuery()
167

End Using
168

Return result
169

Catch ex As Exception
170

Return result
171

Finally
172

sql = Nothing
173

Parameter = Nothing
174

End Try
175

End Function
176

''' <param name="sql">SQL查詢字串</param>
177

''' <param name="Parameters">SQL預存程序集合</param>
178

''' <returns>引響的行數</returns>
179

Public Function SqlExecuteNonQuery(ByRef sql As String, ByRef Parameters As SqlParameter()) As Integer
180

Dim result As Integer = 0
181

Try
182

Using SQLConn As New SqlConnection(ConnStr)
183

Dim SqlCmd As New SqlCommand(sql, SQLConn)
184

SqlCmd.Parameters.AddRange(Parameters)
185

SQLConn.Open()
186

result = SqlCmd.ExecuteNonQuery()
187

End Using
188

Return result
189

Catch ex As Exception
190

Return result
191

Finally
192

sql = Nothing
193

Parameters = Nothing
194

End Try
195

End Function
196

Public Sub New()
197

198

End Sub
199

200

Protected Overrides Sub Finalize()
201

MyBase.Finalize()
202

End Sub
203

End Class

恩 ...基本上是如此 ... 用多載(OverLoads)的方式寫成的 ...沒有什麼技巧可言= =、

稍微解釋一下好了 ...

第五行 Inherits objConn 表示這個類別是繼承自 objConn

12~29 執行Select 傳回成功/失敗用參考記憶體位址的方式寫入DataSet

33~50 如上參考記憶體位址改為 --> DataTable

55~70 同12~29 ~ 這邊參考一個預存 SqlParameter

76~91 同 33~50 ~這邊參考一個預存 SqlParameter

96~111 同55~70 只是參考預存程序改為預存程序的集合

116~131 同76~91 只是參考預存程序改為預存程序的集合

真的是吃撐了= =、哈哈

objConn是用來存取連線字串的類別(修正版)

(奇怪 ...當時不知道在想啥亂寫一通 Property 多載是啥鬼東西...明明NEW就指定好了)

01 ''' <summary>
02 ''' 資料存取層物件。 By Phoehix - 2008
03 ''' </summary>
04

Public Class objConn
05

Private _ConnStr As String
06

''' <summary>
08

''' 取得ConnectionString。
09

''' </summary>
10

''' <returns>ConnectionString</returns>
11

Public ReadOnly Property ConnStr() As String
12

Get
13

Return _ConnStr
14

End Get
15

End Property
16

''' <summary>
17

''' 初始化 objConn 類別的新執行個體
18

''' </summary>
19

Public Sub New()
20

GetConnStr()
21

End Sub
22

''' <param name="name">web.config中，連線字串的名稱。</param>
23

Public Sub New(ByVal name As String)
24

GetConnStr(name)
25

End Sub
26

''' <param name="index">web.config中，連線字串的索引。</param>
27

Public Sub New(ByVal index As Integer)
28

GetConnStr(index)
29

End Sub
30

Private Sub GetConnStr()
31

_ConnStr = WebConfigurationManager.ConnectionStrings(0).ConnectionString.ToString()
32

End Sub
33

Private Sub GetConnStr(ByVal name As String)
34

_ConnStr = WebConfigurationManager.ConnectionStrings(name).ConnectionString.ToString()
35

End Sub
36

Private Sub GetConnStr(ByVal index As Integer)
37

_ConnStr = WebConfigurationManager.ConnectionStrings(index).ConnectionString.ToString()
38

End Sub
39

Protected Overrides Sub Finalize()
41

MyBase.Finalize()
42

End Sub
43

End Class同樣的也是沒有什麼技巧可言 ... 用New的多載指定Web.Config內的連線字串值 ...

這兩個類別 ...是無聊之下的產物 ...

厄簡稱吃撐了= =、哈哈(哀~要想到永續發展阿 ...)

再來進入我們的主題 ... ->ASP.NET 2.0動態產生TreeView樹狀結構-C# & VB.NET 混合

Phoenix 8/4

ASP.NET

回首頁