Data Accessor Class for VB.NET and ASP.NET

Introduction

Below are some examples of wrapping up a lot of common Data Accessing Code into a class for use in your .NET Project. This particular class allows an optional constructor when created to specify a Database connection other then the default we use in the web.config file.

1. First add a new class to your project. Add…Add New Item…Select Class and name it DAOObject (or whatever you like)
2. Use the code example below to import System Namespaces and create your constructor

Code

Imports System.Data
Imports 
System.Data.SqlClient

Namespace DBAccess

 Public Class 
DAOObject

 Public Sub New()
 End Sub

 Private 
mCN As String
 Public Sub New(ByVal ConnectionString As 
String) 
   'Allows us to use a CN String Other then the 
Default
    mCN = ConnectionString
 End 
Sub

 Protected Function GetConnection() As 
SqlConnection

   Dim ret_conn As 
SqlConnection

   If mCN = String.Empty 
Then
     ret_conn = New 
SqlConnection(ConfigurationSettings.AppSettings("cn")) 'Use 
Default
   Else
     ret_conn = New 
SqlConnection(mCN)
   End If

   
ret_conn.Open()
   GetConnection = ret_conn

  End 
Function

  Protected Sub CloseConnection(ByVal conn As 
SqlConnection)
     
conn.Close()
     conn = Nothing
  End 
Sub

These 2 overloaded Functions below will allow you to pass a Stored Procedure Name with or without parameters and returns a DataReader. Add these to your class.

Public Overloads 
Function RunSPReturnRS(ByVal strSP As String, ByVal ParamArray 
commandParameters() As SqlParameter) As SqlDataReader

   Dim cn 
As SqlConnection = GetConnection()
   Dim rdr As 
SqlDataReader

   Dim cmd As New SqlCommand(strSP, 
cn)
   cmd.CommandType = 
CommandType.StoredProcedure

   Dim p As 
SqlParameter
   For Each p In 
commandParameters
     p = 
cmd.Parameters.Add(p)
     p.Direction = 
ParameterDirection.Input
   Next

   rdr = 
cmd.ExecuteReader(CommandBehavior.CloseConnection)
   
cmd.Dispose()

   Return rdr

End 
Function



Public Overloads Function RunSPReturnRS(ByVal strSP As 
String) As SqlDataReader

   Dim cn As SqlConnection = 
GetConnection()
   Dim rdr As SqlDataReader

   Dim 
cmd As New SqlCommand(strSP, cn)
   cmd.CommandType = 
CommandType.StoredProcedure

   rdr = 
cmd.ExecuteReader(CommandBehavior.CloseConnection)

   
cmd.Dispose()

   Return rdr

End 
Function

Example of use. (Don't forget to import system.data and system.sqlclient namespaces)

Dim objDB as New 
DBAccess.DAOObject()

Dim rdrDBAccess as SqlDataReader = 
objDB.RunSPReturnRS("spRptsDBSelect", _
   New 
SqlParameter("@roleID", Session("Role")), _
   New 
SqlParameter("@db", intDbId), _
   New SqlParameter("@userID", 
Session("UserID")))

While rdrDBAccess.Read()
...Code Here
End 
While
rdrDBAccess.close

Two Functions below simply let you run "Pass Through" SQL Queries and Action Queries. Add these to your class

Public Function RunPassSQL(ByVal strSQL As String) As 
SqlDataReader

   Dim cn As SqlConnection = 
GetConnection()
   Dim rdr As 
SqlDataReader

   Dim cmd As New SqlCommand(strSQL, 
cn)
   rdr = 
cmd.ExecuteReader(CommandBehavior.CloseConnection)
   
cmd.Dispose()

   Return rdr

End Function

Public 
Sub RunActionQuery(ByVal strSQL As String)

  Dim cn As SqlConnection 
= GetConnection()
  Dim cmd As New SqlCommand(strSQL, cn)

  
Try
    cmd.ExecuteNonQuery()
    
cmd.Dispose()
  Finally
    
CloseConnection(cn)
  End Try

End Sub

Example of use:

Dim objDB as 
New DBAccess.DAOObject()

Dim dr As SqlDataReader = 
objDB.RunPassSQL("select * from tbl_User WHERE usrEmail = '" & strUser & 
"'")

If dr.Read then
…Code Here
End 
If
dr.close


objDB.RunActionQuery("Delete from tbl_Users where 
Fac_ID = " & intFacID)

For our application we often have a return value in our Stored Procedures that returns 0 for success or an integer other then 0 for failure. We always use an output variable named @RetVal for this purpose. Add this Function below to your class.

Public Function RunSPReturnInteger(ByVal strSP 
As String, ByVal ParamArray commandParameters() As SqlParameter) As 
Integer

  Dim cn As SqlConnection = GetConnection()
  Dim 
retVal As Integer

  Try

  Dim cmd As New 
SqlCommand(strSP, cn)
  cmd.CommandType = 
CommandType.StoredProcedure

  Dim p As SqlParameter
  For 
Each p In commandParameters
   p = 
cmd.Parameters.Add(p)
   p.Direction = 
ParameterDirection.Input
  Next

  p = cmd.Parameters.Add(New 
SqlParameter("@RetVal", SqlDbType.Int))
  p.Direction = 
ParameterDirection.Output

  cmd.ExecuteNonQuery()
  retVal = 
cmd.Parameters("@RetVal").Value
  cmd.Dispose()

  
Finally
  CloseConnection(cn)
  End Try

  Return 
retVal

End Function

Example of use:

Dim objDB as New DBAccess.DAOObject()

If 
(objDB.RunSPReturnInteger("sptbl_CertUpdate", _
 New SqlParameter("@id", 
intAccID), _
 New SqlParameter("@fac", intFacID), _
 New 
SqlParameter("@Aff", intSponFac), _
 New SqlParameter("@spec", 
intSpecID), _
 New SqlParameter("@AccrdID", intAccredID), _
 New 
SqlParameter("@faRotate", intVARotate), _
 New SqlParameter("@dteMRAA", 
dteMRA), _
 New SqlParameter("@dteNST", dteNS), _
 New 
SqlParameter("@User", Session("UserID"))) = 0) 
Then

 Response.Redirect("NextPage.aspx")

Else

 strError = "Record Update 
FAILED, try again"

End If

Below is a Function that takes a stored procedure with parameters, and Datatable name of your choosing and returns a Dataset. Add this code to your class

Public 
Function RunSPReturnDataSet(ByVal strSP As String, ByVal DataTableName As 
String, ByVal ParamArray commandParameters() As SqlParameter) As 
DataSet

  Dim cn As SqlConnection = GetConnection()

  
Dim ds As New DataSet()

  Dim da As New SqlDataAdapter(strSP, 
cn)
  da.SelectCommand.CommandType 
= CommandType.StoredProcedure

  Dim p As SqlParameter
  
For Each p In commandParameters
   
da.SelectCommand.Parameters.Add(p)
   p.Direction = 
ParameterDirection.Input
  Next

  da.Fill(ds, 
DataTableName)

  CloseConnection(cn)
  
da.Dispose()

  Return ds

End 
Function

Example of use:

Dim DS As DataSet
Dim 
objDB As New DBAccess.DAOObject()
DS = New DataSet()
DS = 
objDB.RunSPReturnDataSet("spWRpt01", "Report", New SqlParameter("@SrvID", 1), 
New SqlParameter("@FacID", FacID))

If DS.Tables("Report").Rows.Count = 0 
Then

 strHTML = "<tr><td>There were no 
records</tr></td>"

else

  Dim drFullRpt As 
DataRow

  For Each drFullRpt In 
DS.Tables("Report").Rows
     ..Code Here
  
Next

End If

These are just a few examples I use. I have many other routines including adding datatables to datasets, etc. Using some creativity, you can write your own or better.