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

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as