' 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