Data Access Primer

There seems to be as many approaches to data access and consumption as there are developers. Some are good, some not so good. Our goals with data access are:

1. Abstract all data access logic out of the page and business objects into something that is reusable
2. Provide a single pattern that can expose data in the two ways it is consumed: bound to a control and iterated for use in business logic
3. Force users to connect and interact with the database properly
4. Make it extremely easy

To accomplish this, we use what we call Domain objects. What we need in the end are objects that represent real-world things just like most database tables do. These objects can then be loaded with data from the database or page and pass it through to the other side.

Before I get too far, I’ll state that we use this approach 100% of the time. We never embed any logic in pages or business classes that interact directly with the database. All interaction is always done through stored procedures and Domain objects.

Let’s break down the Domain class for the Chamber module. You can find it at /App_Code/Domain/Chamber.vb.

Namespace UnifiedASP.Domain

	Public Class Chamber

		Inherits UnifiedASP.Base.DomainSQL

This class inherits from our DomainSQL base class used when interacting with a SQL Server database. If you open the class /App_Code/Base/DomainSQL.vb, you’ll see the following definition.

Namespace UnifiedASP.Base

	Public MustInherit Class DomainSQL

		Inherits System.Data.DataTable

The DomainSQL class in turn inherits from System.Data.DataTable so all derived classes, including our Chamber class is a DataTable that can be directly bound to a control that supports binding.

Looking again at the Chamber.vb class, the first thing you’ll find in the class are properties that match the columns for this table in the database. We use public variables as opposed to public/private properties because most of the time, you don’t gain anything with properties except a lot of code.

		Public ChamberId As Integer
		Public ChamberName As String
		Public Address As String
		Public City As String
		Public StateId As Integer
		Public StateAbbr As String
		Public ZipCode As String
		Public PhoneNumber As String
		Public FaxNumber As String
		Public WebsiteUrl As String
		Public EmailAddress As String

To load data into this class and its underlying DataTable, we have to execute a stored procedure. This is very simple as is shown in the LoadMe method in Image 4.

		Public Sub LoadMe()

			InitCommand("chChamber_Get")

			AddParameter("ChamberId", ChamberId)

			ExecCommand()

		End Sub

InitCommand, AddParameter, and ExecCommand are methods of the DomainSQL class that abstract all of the activities required to execute the stored procedure. Each is documented below.

After calling this method, the DataTable will contain data and the class’ properties will be populated with the first row of data returned from the database. All the page has to do to use this class is instantiate it, set the ChamberId property, and call the LoadMe method as is shown in Image 5.

Dim myChamber As New UnifiedASP.Domain.Chamber
myChamber.ChamberId = myChamberId
myChamber.LoadMe()

The page can then set labels in the page to equal values in the properties.

lblChamberName.Text = myChamber.ChamberName
lblAddress.Text = myChamber.Address
lblCity.Text = myChamber.City
lblStateAbbr.Text = myChamber.StateAbbr
lblZipCode.Text = myChamber.ZipCode
lblPhoneNumber.Text = myChamber.PhoneNumber
lblFaxNumber.Text = myChamber.FaxNumber
lnkWebsiteUrl.NavigateUrl = myChamber.WebsiteUrl
lnkWebsiteUrl.Text = myChamber.WebsiteUrl

If you want to load the class with a collection of objects and bind it to a DataGrid, it is just as easy as is shown in Image 7.

Dim myChamber As New UnifiedASP.Domain.Chamber
myChamber.City = myCity
myChamber.StateId = myStateId
myChamber.LoadAll()

grdChamber.DataSource = myChamber
grdChamber.DataBind()

That’s the basics. We use our code generator to create the domain classes and the core CRUD stored procedures. When we need to add new stored procedures, we first create the stored procedure in the database, add any additional properties to the class, add the method that calls the stored procedure, and we’re done. That’s it!

So let’s dig a bit deeper into the DomainSQL base class.

If you look again at the LoadMe method above, you will see there are three things it needs to do. First, it needs to tell DomainSQL what stored procedure to call, and through optional properties, whether it should expect results. It then must add the appropriate parameters for the stored procedure. And finally it must tell DomainSQL to execute the stored procedure.

Let’s dig into each.

InitCommand()
Init Command has three main jobs:

1. Assign the stored procedure to the connecting object
2. Instantiate either a SqlDataAdapter or a SqlCommand based on whether the stored procedure will return data that needs to be populated to the DataTable and properties
3. Retrieve the correct Connection string

You tell InitCommand what to do through the properties you pass it.

When calling InitCommand from the Domain object, you must specify the name of the stored procedure.

By default, it will assume the stored procedure will return data, but you can specifiy UnifedASP.Types.SqlBehavior.NoQuery as the second parameter and it will bypass populating the DataTable and class properties.

Connection Strings are stored in the connectionStrings node of web.config. Some applications interact with multiple databases so we need an easy way to tell InitCommand which connection string to use. In the appSettings node of web.config, we specify a default connection string key as DefaultConnectionStringKey.

If you do not pass a ConnectionStringKey to InitCommand, it will use the DefaultConnectionString key specified in appSettings.

AddParameter()

Now that you have the proper command instantiated and have told it what stored procedure to call, whether it will return data, and what connection string to use, you can add the necessary parameters to the object. This is done through a series of overloaded methods in DomainSQL named AddParameter.

The AddParameter methods require a Parameter Name and a Parameter Value, and sometimes a Parameter Length.

Based on the DataType of Parameter Value, it will filter to the correct AddParameter method and package that parameter correctly.

For example, if you pass Parameter Value a variable that was declared as an Integer, it will use AddParameter written just for integers. That method will set the appropriate size, and will reset the value to DBNull.Value if the integer is 0.

If you pass Parameter Value a variable that was declared as string, it will use AddParameter written just for strings.

ExecCommand()

So now you’ve told DomainSQL the stored procedure name, whether it will return data, and added the appropriate parameters. Now you need to execute it.

This is done by calling the ExecCommand method. Its only optional parameter is command timeout which is set to 60 seconds by default.

If your stored procedures use return values (ours’ don’t by default), ExecCommand will return you the value returned by the stored procedures.

If the query returns data, it will populate it into its DataTable, and call a series of methods that expose that data through the properties of the inheriting class, Chamber.vb in this case.

How DomainSQL Sets the Properties

At this point, your stored procedure has been executed and any returned data is available in its DataTable. That’s great if all you’re going to do is bind it to a control that accepts a DataTable, but in scenarios where the page needs to use the data in logic, interacting with a DataTable is a real pain and requires too much code. To simplify things for the consumers of these classes, we wanted to take the approach of loading the column values into properties, and having the ability to move from one row to the next and have those property values change.

For example, in a page, we want to be able to do something like:

Dim myChamber as New UnifiedASP.Domain.Chamber
myChamber.LoadAll()

While Not myChamber.EOF
	ExtractAndSaveMetaData(myChamber.WebsiteUrl)
	myChamber.MoveNext()
End While

The challenge to this is to provide a mechanism for the base class to tell the derived class (Chamber in this example) that it needs to load or clear its properties. This is done through two over-ride-able methods of DomainSQL that are also found in each derived class: Populate and ClearValues.

If you look at the bottom of the Chamber.vb class, you will see the following two subs.

		Public Overrides Sub Populate()

			ChamberId = GetValue("ChamberId")
			ChamberName = GetValue("ChamberName")
			Address = GetValue("Address")
			City = GetValue("City")
			StateId = GetValue("StateId")
			StateAbbr = GetValue("StateAbbr")
			ZipCode = GetValue("ZipCode")
			PhoneNumber = GetValue("PhoneNumber")
			FaxNumber = GetValue("FaxNumber")
			WebsiteUrl = GetValue("WebsiteUrl")

		End Sub

		Public Overrides Sub ClearValues()

			ChamberId = Nothing
			ChamberName = Nothing
			Address = Nothing
			City = Nothing
			StateId = Nothing
			StateAbbr = Nothing
			ZipCode = Nothing
			PhoneNumber = Nothing
			FaxNumber = Nothing
			WebsiteUrl = Nothing

		End Sub

Populate makes use of a method of DomainSQL named GetValue. DomainSQL keeps track of the active row as you iterate through it, and is passed the ColumnName of the column whose value you want to retrieve. When data is first loaded into the object from a stored procedure, these subs are called to populate the values. Anytime you use MoveNext or MovePrevious, these methods are called again to load the current row’s data into the deriving class’ properties.

These ColumnNames are established in the stored procedure, so it’s important that every stored procedure called by the Chamber.vb class refers to each column name in the same way. If ncChamber_Get aliases ChamberName as Chamber and ncChamber_GetAll does not use the same alias, the property will not populate correctly. So don’t use aliases unless you have to.

That’s the basics of Data Access in UnifiedASP. There are a number of advanced things you can do such as reshaping data which will be addressed in the blog and tagged as “Data Access”.