ASP.NET – Returning a single row from the database as a DataRow

This is a slightly altered function (very similar to the previous post on DataRowCollection) but returns only one item of data – sometimes very useful if doing something like:

Dim SQLString As String = “SELECT FirstNames, LastName FROM StaffList WHERE StaffID=1

Dim dr As DataRow = new dbrecordhelper().GetDataRow(SQLString)

‘Check if a valid row is returned

if NOT dr is Nothing then

Me.lblFirstNames.Text = dr(“FirstNames”).ToString

Me.lblLastName.Text = dr(“LastName”).ToString

end if

dr = Nothing

SQLString = Nothing

----------------- in dbrecordhelper.vb file in App_Code ---------------

Public Function GetDataRow(ByVal SQLString As String) As DataRow

'Declare variables
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand
Dim dataAdapt As SqlDataAdapter

Dim ds As DataSet
'Declare the dataset
ds = New DataSet

'Connect to SQL database
sqlConn = New SqlConnection(m_connString)
sqlConn.Open()

'Build up SqlCommand
sqlComm = New SqlCommand(SQLString, sqlConn)
sqlComm.CommandType = CommandType.Text

'Create the DataAdapter
dataAdapt = New SqlDataAdapter(sqlComm)
dataAdapt.Fill(ds)

Dim dt As DataTable = ds.Tables(0)
If dt.Rows.Count > 0 Then

GetDataRow = dt.Rows(0)

Else

GetDataRow = Nothing

End If

'Close connection
sqlConn.Close()

ds = Nothing
dt = Nothing
sqlConn = Nothing
sqlComm = Nothing
dataAdapt = Nothing

End Function

This method means that we can do simple database interactions with minimal code on each aspx page. Any feedback appreciated – particularly if there is a better way of doing this!