Month: June 2009

We found whilst programming that on many occasions we need to return a recordset in an similar fashion to what we would have done in Classic ASP – and whilst there are many ways to do this, we found that creating the following functions in a .vb file that we can then call is the fastest way. If we are only returning one row, we have a different function that simply returns a single row as a DataRow, as opposed to the collection of rows. Additionally we have removed all connection string detail out of the individual pages, and into a single place – the web.config file.

To call function (one possible usage)

SQLString = “SELECT id, firstnames, lastname FROM TestTable”

For each dr as DataRow in new dbrecordhelper().GetDataRows(SQLString)

‘Do something here with each item

‘e.g. Create a list of firstnames in a label called lblFirstNames

Me.lblFirstNames.Text += dr(“firstnames”).ToString & “<br />”

Next

——————— APP_Code\dbrecordhelper.vb ————————–

‘ – The functions below (we stored these in a file called “DBRecordHelper.vb” in the App_Code folder)

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Configuration

Public Class DBRecordHelper

Public Function GetDataRows(ByVal SQLString As String) As DataRowCollection

‘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

GetDataRows = dt.Rows

Else

GetDataRows = Nothing

End If

‘Close connection
sqlConn.Close()

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

End Function

‘ Constructors
Public Sub New()
Me.connString = ConfigurationManager.ConnectionStrings("DataConnectionString").ConnectionString
End Sub
Public Sub New(ByVal ConnectionString As String)
Me.connString = ConnectionString
End Sub

‘ Connection string variable and property
Private m_connString As String
Public Property connString() As String
Get
Return Me.m_connString
End Get
Set(ByVal value As String)
Me.m_connString = value
End Set
End Property

End Class

——————— Web.config ————————–

..and the SQLConnection is specified ONCE in the Web.config file

<connectionStrings>
<add name="DataConnectionString" connectionString="Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=SQLUSERNAME;Password=SQLPASSWORD" providerName="System.Data.SqlClient" />
</connectionStrings>

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!