Reusable Data Access

One of the most useful things you can do to save time, coding and sanity is to create a generic data access class.  Any data-intensive application (aren't most of them?) utilizes data.  Usually, lots of data.  That being the case, you can create a reusable, generic data access class that you only have to code up once, but can utilize over and over again.

To get started, crack your project open VWD Express or Visual Studio.  Right-click on your project and select "Add New Item".  Select "Class".  Name it "Data".

This will, of course, add a new class file "Data.vb" to your "App_Code" folder.  Open this file.

Once open, add "Imports System.Data" and "Imports System.Data.SqlClient" to the top of the file.

Now that the boring stuff is done, we can add the two main methods:

Public Function ReturnDT(ByVal sp As String, Optional ByVal param1 As String = "", Optional ByVal paramvalue1 As String = "", Optional ByVal param2 As String = "", Optional ByVal paramvalue2 As String = "", Optional ByVal param3 As String = "", Optional ByVal paramvalue3 As String = "") As DataTable
Dim oConn As SqlConnection = New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim oCom As New SqlCommand()
Dim da As New SqlDataAdapter()

With oCom
.Connection = oConn
.CommandType = CommandType.StoredProcedure
.CommandText = sp
If Not param1 = "" Then
.Parameters.AddWithValue(param1, paramvalue1)
If Not param2 = "" Then
.Parameters.AddWithValue(param2, paramvalue2)
If Not param3 = "" Then
.Parameters.AddWithValue(param3, paramvalue3)
End If
End If
End If
End With

Dim dt As DataTable = New DataTable
da.SelectCommand = oCom
da.Fill(dt)
Return dt
da.Dispose()
da = Nothing
oConn.Dispose()
oConn = Nothing
End Function


Public Function ReturnDS(ByVal sp As String, Optional ByVal param1 As String = "", Optional ByVal paramvalue1 As String = "", Optional ByVal param2 As String = "", Optional ByVal paramvalue2 As String = "", Optional ByVal param3 As String = "", Optional ByVal paramvalue3 As String = "") As DataSet
Try
Dim oConn As SqlConnection = New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim oCom As New SqlCommand()
Dim da As New SqlDataAdapter()
oConn.Open()

With oCom
.Connection = oConn
.CommandType = CommandType.StoredProcedure
.CommandText = sp
If Not param1 = "" Then
.Parameters.AddWithValue(param1, paramvalue1)
If Not param2 = "" Then
.Parameters.AddWithValue(param2, paramvalue2)
If Not param3 = "" Then
.Parameters.AddWithValue(param3, paramvalue3)
End If
End If
End If
End With

Dim ds As System.Data.DataSet = New System.Data.DataSet()
da.SelectCommand = oCom
da.Fill(ds, "Generic")
Return ds
da.Dispose()
da = Nothing
oConn.Dispose()
oConn = Nothing
Catch ex As Exception
Return Nothing
End Try
End Function


Now that you have these methods in place, you can now call them anywhere else throughout your site.

For example, if you want to bind a GridView control, all you would need to do:


Protected odata as new data

'Populate with a Dataset
Protected Sub gvLoaderDataset()
with gridview1
'Pass stored procedure name, Pass parameter name, Pass Parameter value
.datasource = odata.ReturnDS("sp_ResultsByID", "@ID", "1")
.databind
end with
End Sub

'Populate with a Datatable
Protected Sub gvLoaderDataset()
with gridview1
'Pass stored procedure name, Pass parameter name, Pass Parameter value
.datasource = odata.ReturnDT("sp_ResultsByID", "@ID", "1")
.databind
end with
End Sub


Also note that you can send more than one parameter to "odata.ReturnDS" or "odata.ReturnDT".  The call is much the same:



'Pass stored procedure name, Pass parameter name, Pass Parameter value
odata.ReturnDT("sp_ResultsByIDAndName", "@ID", "1", "@Name", "John Dough")


As you can see, our methods can accept anywhere from 0-3 parameters (more if you modify them). Why? Because they are setup with optional parameters (can't do that in C#...) and evaluate how to proceed based upon whether or not the parameters are passed.

Of course, you can pass views, readers, arrays, etc. the same way that we are passing generic datasets and datatables. It's really up to you. Just thought I'd share.