dcsimg
Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: VB7
Expertise: Intermediate
Jan 27, 2003

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


DbObject - A base data class for common DB operations

' A base data class that makes it much easier for derived classes to run a 
' stored procedure to retrieve data or execute a SQL command. (See usage 
' examples at the bottom)

Imports System
Imports System.Data
Imports System.Data.SqlClient


' DbObject is the class from which all classes in the Data Services    Tier
' inherit. The core functionality of establishing a connection with the
' database and executing simple stored procedures is also provided by
' this base class.
' ---
Public MustInherit Class DbObject

    ' Private Variables
    '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    Protected myConnection As SqlConnection
    Private myConnectionString As String

    ' Constructors
    '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    ' A parameterized constructor, it allows us to take a connection string
    ' as a constructor argument, automatically instantiating a new connection
    ' ---
    Public Sub New(ByVal newConnectionString As String)
        myConnectionString = newConnectionString
        myConnection = New SqlConnection(myConnectionString)
    End Sub


    ' Methods
    '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    ' Runs a stored procedure, can only be called by those classes deriving
    ' from this base. It returns an integer indicating the return value of the
    ' stored procedure, and also returns the value of the RowsAffected aspect
    ' of the stored procedure that is returned by the ExecuteNonQuery method.
    Protected Overloads Function RunProcedure(ByVal storedProcName As String, _
        ByVal parameters As IDataParameter(), ByRef rowsAffected As Integer) As _
        Integer
        Dim result As Integer

        myConnection.Open()
        Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters)
        rowsAffected = command.ExecuteNonQuery()
        result = CInt(command.Parameters("ReturnValue").Value)
        myConnection.Close()

        Return result
    End Function


    ' Will run a stored procedure, can only be called by those classes
    ' deriving from this base. It returns a SqlDataReader containing the
    ' result of the stored procedure.
    Protected Overloads Function RunProcedure(ByVal storedProcName As String, _
        ByVal parameters As IDataParameter()) As SqlDataReader
        Dim returnReader As SqlDataReader

        myConnection.Open()
        Dim command As SqlCommand = BuildQueryCommand(storedProcName, _
            parameters)
        command.CommandType = CommandType.StoredProcedure

        returnReader = command.ExecuteReader(CommandBehavior.CloseConnection)
        ' Connection will be closed automatically
 
        Return returnReader
      End Function


    ' Creates a DataSet by running the stored procedure and placing
    ' the results of the query/proc into the given tablename.
    Protected Overloads Function RunProcedure(ByVal storedProcName As String, _
        ByVal parameters As IDataParameter(), ByVal tableName As String) As _
        DataSet 
        Dim dataSet As New DataSet()
  
        myConnection.Open()
        Dim sqlDA As New SqlDataAdapter()
        sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters)
        sqlDA.Fill(dataSet, tableName)
        myConnection.Close()
  
        Return dataSet
    End Function
  
  
    ' Takes an -existing- dataset and fills the given table name
    ' with the results of the stored procedure.
    Protected Overloads Sub RunProcedure(ByVal storedProcName As String, _
        ByVal parameters As IDataParameter(), ByVal dataSet As DataSet, _
        ByVal tableName As String)
        myConnection.Open()
        Dim sqlDA As New SqlDataAdapter()
        sqlDA.SelectCommand = BuildIntCommand(storedProcName, parameters)
        sqlDA.Fill(dataSet, tableName)
        myConnection.Close()

    End Sub



    ' Properties
    '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    ' Protected property that exposes the connection string to inheriting
    ' classes. Read-Only.
    Protected ReadOnly Property ConnectionString() As String
        Get
            Return myConnectionString
        End Get
    End Property



    ' Private Code
    '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    ' Private routine allowed only by this base class, it automates the task
    ' of building a SqlCommand object designed to obtain a return value from
    ' the stored procedure.
    Private Function BuildIntCommand(ByVal storedProcName As String, _
        ByVal parameters As IDataParameter()) As SqlCommand
        Dim command As SqlCommand = BuildQueryCommand(storedProcName, _
            parameters)
        Dim parameter As New SqlParameter()

        With parameter
            .ParameterName = "ReturnValue"
            .DbType = SqlDbType.Int
            .Size = 4
            .Direction = ParameterDirection.ReturnValue
            .IsNullable = False
            .Precision = 0
            .Scale = 0
            .SourceColumn = String.Empty
            .SourceVersion = DataRowVersion.Default
            .Value = Nothing
        End With
        command.Parameters.Add(parameter)

        Return command
    End Function


    ' Builds a SqlCommand designed to return a SqlDataReader,
    ' and not an actual integer value.
    Private Function BuildQueryCommand(ByVal storedProcName As String, _
        ByVal parameters As IDataParameter()) As SqlCommand
        Dim command As New SqlCommand(storedProcName, myConnection)
        command.CommandType = CommandType.StoredProcedure
 
        Dim parameter As SqlParameter
        For Each parameter In parameters
            command.Parameters.Add(parameter)
        Next

        Return command
    End Function

End Class



' *** USAGE ***

Public Class Categories
    Inherits Wrox.WebModules.Data.DbObject


    Public Sub New(ByVal newConnectionString As String)
        MyBase.New(newConnectionString)
    End Sub

    ' Return all the Categories
    Public Function GetCategories() As DataSet
        Return RunProcedure("sp_Forums_GetCategories", New IDataParameter() {}, _
            "Categories")
    End Function


    ' Add a new record
    Public Function Add(ByVal categoryName As String, ByVal categoryImageUrl As _
        String, ByVal categoryPosition As Integer) As Integer
        Dim rowsAffected As Integer

        ' Create the parameters
        Dim parameters As SqlParameter() = { New SqlParameter("@CategoryName", _
            SqlDbType.VarChar, 100), New SqlParameter("@CategoryImageUrl", _
            SqlDbType.VarChar, 100), New SqlParameter("@CategoryPosition", _
            SqlDbType.Int, 4), New SqlParameter("@CategoryID", SqlDbType.Int, _
            4)}

        ' Set the values
        parameters(0).Value = categoryName.Trim()
        parameters(1).Value = categoryImageUrl.Trim()
        parameters(2).Value = categoryPosition
        parameters(3).Direction = ParameterDirection.Output

        ' Run the procedure
        RunProcedure("sp_Forums_InsertCategory", parameters, rowsAffected)

        Return CInt(parameters(3).Value)
    End Function

End Class




' This code is taken from Marco Bellinaso's and Kevin Hoffman's "ASP.NET 
' Website Programming - VB.NET edition" (Wrox Press). You can read two entire 
' sample chapters of the C# edition from our Book Bank:
' Chapter 4: Mantaining the site: http://www.vb2themax.com/Htmldoc.asp?File=/
' Books/AspnetWebsite/AspNetWebSite_04.htm
' Chapter 11: Deploying the Site: http://www.vb2themax.com/HtmlDoc.asp?Table=
' Books&ID=7800
Kevin Hoffman
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date