1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
|
Public Class clsSqlServerAccess
Implements IDisposable
Private _mstrName As String = "clsSqlServerAccess"
Private _connectionString As String = ""
Private _dbSqlConnection As SqlClient.SqlConnection
Public Sub New(ByVal strServerName As String, _
ByVal strDatabaseName As String, _
ByVal strUserName As String, _
ByVal strPassword As String)
_connectionString = GetConnectionString(strServerName, strDatabaseName, strUserName, strPassword)
_dbSqlConnection = New SqlClient.SqlConnection(_connectionString)
End Sub
Public Function GetConnectionString(ByVal strServerName As String, _
ByVal strDatabaseName As String, _
ByVal strUserName As String, _
ByVal strPassword As String)
GetConnectionString = "Data Source=" + strServerName + ";" + _
"Initial Catalog=" + strDatabaseName + ";" + _
"User ID=" + strUserName + ";" + _
"Password=" + strPassword
End Function 'GetConnectionString As String
Public Function OpenConnection() As Boolean
OpenConnection = False
Try
_dbSqlConnection.Open()
OpenConnection = True
Catch ex As Exception
Throw New GenericException(ex.Message, ex.StackTrace, Me._mstrName + ".OpenConnection", "Generic Exception")
End Try
End Function 'OpenConnection As Boolean
Public Function CloseConnection() As Boolean
Try
If _dbSqlConnection.State <> ConnectionState.Closed Then
_dbSqlConnection.Close()
End If
Catch ex As Exception
Throw New GenericException(ex.Message, ex.StackTrace, Me._mstrName + ".CloseConnection", "Generic Exception")
End Try
End Function
Public Function UpdateSqlRecord(ByVal requestedQuery As String, _
ByVal commandType As CommandType, _
ByVal parameterList As Collection) As SqlClient.SqlParameterCollection
Dim sqlCmd As New SqlClient.SqlCommand() 'Action will be executed
Dim sqlTransact As SqlClient.SqlTransaction = _dbSqlConnection.BeginTransaction
Try
With sqlCmd
.Transaction = sqlTransact
.Connection = _dbSqlConnection
.CommandText = requestedQuery
.CommandType = commandType
For Each param As SqlClient.SqlParameter In parameterList
.Parameters.Add(param)
Next
.ExecuteNonQuery()
End With
sqlTransact.Commit()
Return sqlCmd.Parameters
Catch ex As Exception
Throw New GenericException(ex.Message, ex.StackTrace, Me._mstrName + ".UpdateSqlRecord", "Generic Exception")
Finally
sqlTransact.Dispose()
sqlTransact = Nothing
End Try
End Function 'UpdateSqlRecord with parameter
Public Sub UpdateSqlRecord(ByVal requestedQuery As String, _
ByVal commandType As CommandType)
Dim sqlCmd As New SqlClient.SqlCommand() 'Action will be executed
Dim sqlTransact As SqlClient.SqlTransaction = _dbSqlConnection.BeginTransaction
Try
With sqlCmd
.Transaction = sqlTransact
.Connection = _dbSqlConnection
.CommandText = requestedQuery
.CommandType = commandType
.ExecuteNonQuery()
End With
sqlTransact.Commit()
Catch ex As Exception
Throw New GenericException(ex.Message, ex.StackTrace, Me._mstrName + ".UpdateSqlRecord", "Generic Exception")
Finally
sqlTransact.Dispose()
sqlTransact = Nothing
End Try
End Sub 'UpdateSqlRecord without parameter
Public Function SelectSqlRecord(ByVal command As String) As DataSet
Dim ds As DataSet = Nothing
Dim dr As SqlClient.SqlDataAdapter = Nothing
Try
dr = New SqlClient.SqlDataAdapter(command, Me.DbSqlConnection)
ds = New DataSet
dr.Fill(ds)
Return ds
Catch ex As Exception
Throw New GenericException(ex.Message, ex.StackTrace, Me._mstrName + ".UpdateSqlRecord", "Generic Exception")
Finally
End Try
End Function
Public Function SelectSqlRecord(ByVal command As String, _
ByVal commandType As CommandType, _
ByVal parameterList As Collection) As DataSet
Dim cmd As New SqlClient.SqlCommand()
Dim sqlTransact As SqlClient.SqlTransaction = _dbSqlConnection.BeginTransaction
Dim sda As SqlClient.SqlDataAdapter = Nothing
Dim ds As New DataSet()
With cmd
.Transaction = sqlTransact
.Connection = _dbSqlConnection
.CommandText = command
.CommandType = commandType
If Not parameterList Is Nothing Then
For Each param As SqlClient.SqlParameter In parameterList
.Parameters.Add(param)
Next
End If
End With
Try
sda = New SqlClient.SqlDataAdapter(cmd)
sda.Fill(ds)
Return ds
Catch ex As Exception
Return Nothing
End Try
End Function
#Region "Property"
Public ReadOnly Property ConnectionString() As String
Get
Return _connectionString
End Get
End Property
Public ReadOnly Property DbSqlConnection() As SqlClient.SqlConnection
Get
Return _dbSqlConnection
End Get
End Property
#End Region
#Region " IDisposable Support "
' IDisposable
Private disposedValue As Boolean = False ' To detect redundant calls
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
End If
End If
Me.disposedValue = True
End Sub
' This code added by Visual Basic to correctly implement the disposable pattern.
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(ByVal disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class |
