Click here to Skip to main content
Click here to Skip to main content

Data Accessor Class for VB.NET and ASP.NET

, 19 Mar 2003
Rate this:
Please Sign up or sign in to vote.
Wrap up Routine Data Accessing Code into a class

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

David Bernett
Web Developer
United States United States
Dept. of Veterans Affairs
Senior Application Developer

Comments and Discussions

 
QuestionPass Through? Pinmembereddddy26-Apr-06 11:26 
GeneralMSFT Foundation Blocks PinmemberTopper Price20-Mar-03 19:47 
GeneralRe: MSFT Foundation Blocks PinmemberDavid Bernett21-Mar-03 4:09 
GeneralRe: MSFT Foundation Blocks PinmemberRay Cassick21-Mar-03 8:06 
GeneralRe: MSFT Foundation Blocks PinmemberSteven Campbell9-Mar-04 9:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 20 Mar 2003
Article Copyright 2003 by David Bernett
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid