Advanced ASP.NET : Data-Access Components (part 1) - A Simple Data-Access Component

Clearly, components are extremely useful. But if you're starting a large programming project, you may not be sure what features are the best candidates for being made into separate components. Learning how to break an application into components and classes is one of the great arts of programming, and it takes a good deal of practice and fine-tuning.

One of the most common types of components is a data-access component. Data-access components are an ideal application of component-based programming for several reasons:

Databases require extraneous details:

These details include connection strings, field names, and so on, all of which can distract from the application logic and can easily be encapsulated by a well-written component.

Databases evolve over time:

Even if the underlying table structure remains constant and additional information is never required (which is far from certain), queries may be replaced by stored procedures, and stored procedures may be redesigned.

Databases have special connection requirements:

You may even need to change the database access code for reasons unrelated to the application. For example, after profiling and testing a database, you might discover that you can replace a single query with two queries or a more efficient stored procedure. In either case, the returned data remains constant, but the data access code is dramatically different.

Databases are used repetitively in a finite set of ways:

In other words, a common database routine should be written once and is certain to be used many times.

1. A Simple Data-Access Component

To examine the best way to create a data-access component, you'll consider a simple application that provides a classifieds page that lists items that various individuals have for sale. The database uses two tables: one is an Items table that lists the description and price of a specific sale item, and the other is a Categories table that lists the different groups you can use to categorize an item. Figure 1 shows the relationship.

Figure 1. The AdBoard database relationships

In this example, you're connecting to a SQL Server database using ADO.NET. You can create this database yourself, or you can refer to the online samples, which include a SQL script that generates it automatically. To start, the Categories table is preloaded with a standard set of allowed categories.

The data-access component is simple. It's a single class (named DBUtil), which is placed in a namespace named DatabaseComponent (which is the root namespace for the project). The DBUtil class uses instance methods and retains some basic information (such as the connection string to use), but it doesn't allow the client to change this information. Therefore, it doesn't need any property procedures. Instead, it performs most of its work in methods such as GetCategories() and GetItems(). These methods return DataSets with the appropriate database records. This type of design creates a fairly thin layer over the database—it handles some details, but the client is still responsible for working with familiar ADO.NET classes such as the DataSet.

NOTE

To use this example as written, you need to add a reference to the System.Configuration.dll and System.Web.dll assemblies in the class library. Otherwise, you can't use the WebConfigurationManager to dig up the connection string you need. To add these references, select Project => Add Reference, and look in the .NET tab.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class DBUtil

    Private connectionString As String

    Public Sub New()
        connectionString = _
          WebConfigurationManager.ConnectionStrings( _
          "AdBoard").ConnectionString
    End Sub

    Public Function GetCategories() As DataSet
        Dim query As String = "SELECT * FROM Categories"
        Dim cmd As New SqlCommand(query)
        Return FillDataSet(cmd, "Categories")

End Function

    Public Function GetItems() As DataSet
        Dim query As String = "SELECT * FROM Items"
        Dim cmd As New SqlCommand(query)
        Return FillDataSet(cmd, "Items")
    End Function

    Public Function GetItems(ByVal categoryID As Integer) As DataSet
        ' Create the Command.
        Dim query As String = "SELECT * FROM Items WHERE Category_ID=@CategoryID"
        Dim cmd As New SqlCommand(query)
        cmd.Parameters.AddWithValue("@CategoryID", categoryID)

        ' Fill the DataSet.
        Return FillDataSet(cmd, "Items")
    End Function

    Public Sub AddCategory(ByVal name As String)
        Dim con As New SqlConnection(connectionString)

        ' Create the Command.
        Dim insertSQL As String = "INSERT INTO Categories "
        insertSQL &= "(Name) VALUES @Name"
        Dim cmd As New SqlCommand(insertSQL, con)
        cmd.Parameters.AddWithValue("@Name", name)

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Finally
            con.Close()
        End Try
    End Sub

    Public Sub AddItem(ByVal title As String, ByVal description As String, _
      ByVal price As Decimal, ByVal categoryID As Integer)
        Dim con As New SqlConnection(connectionString)

        ' Create the Command.
        Dim insertSQL As String = "INSERT INTO Items "
        insertSQL &= "(Title, Description, Price, Category_ID)"
        insertSQL &= "VALUES (@Title, @Description, @Price, @CategoryID)"
        Dim cmd As New SqlCommand(insertSQL, con)
        cmd.Parameters.AddWithValue("@Title", title)
        cmd.Parameters.AddWithValue("@Description", description)
        cmd.Parameters.AddWithValue("@Price", price)
        cmd.Parameters.AddWithValue("@CategoryID", categoryID)

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Finally
            con.Close()

					  

End Try

    End Sub

    Private Function FillDataSet(ByVal cmd As SqlCommand, ByVal tableName As String) _
      As DataSet
        Dim con As New SqlConnection(connectionString)
        cmd.Connection = con
        Dim adapter As New SqlDataAdapter(cmd)

        Dim ds As New DataSet()
        Try
            con.Open()
            adapter.Fill(ds, tableName)
        Finally
            con.Close()
        End Try
        return ds
    End Function

End Class

					  

1.1. Dissecting the Code . . .

• When a DBUtil object is created, the constructor automatically retrieves the connection string from the web.config file. However, it's important to note that this is the web.config file of the web application (as the component doesn't have a configuration file). This is a good design, because it allows a website to use the database component with any database server. However, if the client web application doesn't have the appropriate configuration setting, the database component won't work.

• The code includes methods for retrieving data (those methods that start with Get) and methods for updating data (those methods that start with Add).

• This class includes an overloaded method named GetItems(). This means the client can call GetItems() with no parameters to return the full list or with a parameter indicating the appropriate category.

• Each method that accesses the database opens and closes the connection. This is a far better approach than trying to hold a connection open over the lifetime of the class, which is sure to result in performance degradation in multiuser scenarios.

  Your web server can open and close connections frequently without causing any slowdown. That's because ADO.NET uses connection pooling to keep a small set of open connections ready to use. As long as you don't change the connection string and as long as there are available connections in the pool, when you call SqlConnection.Open(), you receive one of these connections, thereby avoiding the overhead of setting up a new connection.

  
  

• The code uses its own private FillDataSet() function to make the code more concise. This isn't made available to clients. Instead, the GetItems() and GetCategories() methods use the FillDataSet() function.