Completing Your Own SQL Server Based Data Access Helper using COM+ and VB.NET - Adding a few more helper methods
(Page 4 of 6 )
To retrieve a single value from the database based on SELECT that we provide, the following template will help you:
PublicFunction getRowValue(ByVal sqlSELECT As String) As String
Dim Conn As SqlConnection
Dim cmd As SqlCommand
Dim value As String = ""
Try
Conn = New SqlConnection(_ConnectionString)
cmd = New SqlCommand(sqlSELECT, Conn)
With cmd
.Connection.Open()
value = .ExecuteScalar() & "" 'concatenating an empty string..to eliminate null or nothing
.Connection.Close()
.Dispose()
End With
Return value
Catch ex As Exception
Try
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
cmd.Dispose()
End If
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: " & sqlSELECT)
End Try
End Function
To execute any SQL statement given by the user, the following will help you:
PublicOverloads Sub SQLExecute(ByVal strSQL As String)
Dim Conn As SqlConnection
Dim cmd As SqlCommand
Try
Conn = New SqlConnection(_ConnectionString)
cmd = New SqlCommand(strSQL, Conn)
With cmd
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
Catch ex As Exception
Try
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
cmd.Dispose()
End If
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: " & strSQL)
End Try
End Sub
Next: Executing SQL statements along with BLOB values >>
More MS SQL Server Articles
More By Jagadish Chaterjee
