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