Completing Your Own SQL Server Based Data Access Helper using COM+ and VB.NET - Getting a row of data from the database using a single method
(Page 3 of 6 )
The following code gives you an idea of how to simply return a “datarow” based on the SQL SELECT statement provided as a parameter to it.
PublicFunction getDataRow(ByVal sqlSELECT As String) As System.Data.DataRow
Dim da As SqlDataAdapter
Try
Dim dt As New DataTable
da = New SqlDataAdapter(sqlSELECT, _ConnectionString)
da.Fill(dt)
da.Dispose()
If dt.Rows.Count = 0 Then Return Nothing Else Return dt.Rows(0) 'return only first row
Catch ex As Exception
Try
da.Dispose()
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: " & sqlSELECT)
End Try
End Function
Before proceeding further you may need to understand the issue of the “ConnectingString.” I made it simple to provide the “ConnectionString” as follows:
Private_ConnectionString As String
PublicWriteOnly Property ConnectionString() As String
Set(ByVal Value As String)
_ConnectionString = Value
End Set
End Property
Retrieving more than one row (multiple rows)
The following template will help you to retrieve more than one row in the form of a data table.
PublicFunction getDataTable(ByVal sqlSELECT As String) As System.Data.DataTable
Dim Conn As SqlConnection
Dim da As SqlDataAdapter
Try
Conn = New SqlConnection(_ConnectionString)
Dim dt As New DataTable
da = New SqlDataAdapter(sqlSELECT, Conn)
da.Fill(dt)
da.Dispose()
Return dt
Catch ex As Exception
Try
da.Dispose()
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: " & sqlSELECT)
End Try
End Function
You can also retrieve a “DataView” object as follows:
PublicFunction getDataView(ByVal sqlSELECT As String) As System.Data.DataView
Return New DataView(getDataTable(sqlSELECT))
End Function
Next: Adding a few more helper methods >>
More MS SQL Server Articles
More By Jagadish Chaterjee
