ASP.NET – Returning multiple rows as a DataRowCollection easily

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>