Binaryworld - DbObject - A base data class for common DB operations ... [ VB.net -> ADO.net ]

' 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