devxlogo

DbObject – A base data class for common DB operations

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

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist