Introduction

Database Tier - Code

It's now time to roll up our sleeves and get dirty with some code. The first thing after adding an empty class declaration file is to pull in the proper .NET Framework libraries listed below.

Imports System
Imports System.Data
Imports System.Data.OleDb

The System Library is standard for most applications, and I make it a habit to include it in almost all my code modules. The System.Data library is necessary for almost all database access applications. The System.Data.OleDb is used specifically for OLEDB Database Providers to which Microsoft Access belongs to. If we were using SQL Server we'd include the custom SQL provider System.Data.SqlClient.

Then next line of code starts the definition of the Class:

Public Class DBTier

Here we've named the Class DBTier and have given it a modifier of Public, thus making it very accessible from other code modules. After the class is defined all properties are declared:

Shared connectionString As String = _
    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program " _
    & "Files\Microsoft Office\Office10\Samples\Northwind.mdb"

Only one property is declared here as a string variable, connectionString. This variable holds the connection string for the Northwind Access Database. Declaring the variable as Shared defines it as "Class Variable". A class variable is associated with the class, not each object instantiated from the class.

After the connection string declaration you'll find there are three subroutines and one function. The function returns a dataset with a listing of all products. It calls the stored procedure procProductsList, created in part one of this article.

Next you'll find the three subroutines. There is one for each stored procedure; add, update and deletion of products. They're all similarly structured; each with a command, connection and required parameter(s) declared. As a sample, let's dissect the ProductsDeleteItem subroutine. After understanding how this subroutine works the others should be easy to digest.

To start off the routine takes in one parameter, ProductID, which is an Integer representing the Product to be deleted.

Sub ProductsDeleteItem(ByVal ProductID As Integer)

Next, all variables are declared. One for the connection, command and a parameter to be passed into the stored procedure. This parameter is the ProductID to be deleted.

Dim con As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand()
Dim paramProductID As New OleDbParameter()

Command and connection objects are initialized:

con = New OleDbConnection(connectionString)
cmd.Connection = con

The paramProductID parameter properties are configured. Then the parameter is added to the command object. In this case the parameter name in the stored procedure is inProductID, it's an integer and the value is set to the ProductID passed into this subroutine.

With paramProductID
    .ParameterName = "inProductID"
    .OleDbType = OleDbType.Integer
    .Size = 4
    .Value = ProductID
End With
cmd.Parameters.Add(paramProductID)

The last part actually calls the stored procedure.

cmd.CommandText = "EXECUTE procProductsDeleteItem"
con.Open()
cmd.ExecuteNonQuery()
con.Close()

Notice that the connection object only stays open long enough to carry out the stored procedure and then closes immediately. This reduces any possible contention.

While the DBTier class included in this article clearly describes how to access the stored procedures, it would need some enhancements to become quality production code since no error handling has been added. There may also be the need to further enhance performance here.

The downloaded source code associated with this article includes the DBTier.vb file along with some very basic forms to test the actual implementation of the class.

In conclusion, I hope you have gained at least two things from these articles. One being that stored procedures are alive and well in Microsoft Access, although not without their limitations. The second thing to walk away with here is understanding the need to break down an application's data access into separate classes, subroutines and functions. This makes maintenance and upgrades much easier to implement.

Entire DBTier.vb source code:

Imports System
Imports System.Data
Imports System.Data.OleDb
' Functions and subroutines for executing Stored Procedures in Access.
Public Class DBTier
    ' Change Data Source to the location of Northwind.mdb on your local 
    ' system.
    Shared connectionString As String = _
        "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program " _
        & "Files\Microsoft Office\Office10\Samples\Northwind.mdb"
    ' This function returns a dataset containing all records in
    ' the Products Table.
    Function ProductsList() As DataSet
        Dim con As OleDbConnection
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim sSQL As String

        sSQL = "EXECUTE procProductsList"
        con = New OleDbConnection(connectionString)
        da = New OleDbDataAdapter(sSQL, con)
        ds = New DataSet()
        da.Fill(ds, "Products")
        Return ds
    End Function
    ' This Function adds one record to the Products table.
    Sub ProductsAddItem(ByVal ProductName As String, _
        ByVal SupplierID As Integer, ByVal CategoryID As Integer)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductName As New OleDbParameter()
        Dim paramSupplierID As New OleDbParameter()
        Dim paramCategoryID As New OleDbParameter()
        con = New OleDbConnection(connectionString)
        cmd.Connection = con
        With paramProductName
            .ParameterName = "inProductName"
            .OleDbType = OleDbType.VarChar
            .Size = 40
            .Value = ProductName
        End With
        cmd.Parameters.Add(paramProductName)
        With paramSupplierID
            .ParameterName = "inSupplierID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = SupplierID
        End With
        cmd.Parameters.Add(paramSupplierID)
        With paramCategoryID
            .ParameterName = "inCategoryID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = CategoryID
        End With
        cmd.Parameters.Add(paramCategoryID)
        cmd.CommandText = "EXECUTE procProductsAddItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
    ' This function Updates a specific JobTitle Record with new data.
    Sub ProductsUpdateItem(ByVal ProductID As Integer, _
        ByVal ProductName As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductName As New OleDbParameter()
        Dim paramProductID As New OleDbParameter()
        con = New OleDbConnection(connectionString)
        cmd.Connection = con
        With paramProductID
            .ParameterName = "inProductID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = ProductID
        End With
        cmd.Parameters.Add(paramProductID)
        With paramProductName
            .ParameterName = "inProductName"
            .OleDbType = OleDbType.VarChar
            .Size = 40
            .Value = ProductName
        End With
        cmd.Parameters.Add(paramProductName)
        cmd.CommandText = "EXECUTE procProductsUpdateItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
    ' This function deletes one record from the Products table.
    Sub ProductsDeleteItem(ByVal ProductID As Integer)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductID As New OleDbParameter()
        con = New OleDbConnection(connectionString)
        cmd.Connection = con
        With paramProductID
            .ParameterName = "inProductID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = ProductID
        End With
        cmd.Parameters.Add(paramProductID)
        cmd.CommandText = "EXECUTE procProductsDeleteItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
End Class