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 SystemImports System.DataImports 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 FunctionEnd 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 FunctionEnd 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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may