business rules will be managed in a series of Business Object classes
•We will have a central data access layer (DAL) that can be called from any form, class, or module in Windows and ASPX applications
•All database interaction will employ stored procedures
With the Microsoft tools that are available to us, and a little ingenuity, we can design and develop a very robust Data Access Framework that can be used, not only for Coding Briefs, but for most other applications that we develop in the future
In the next section, we design the data access framework.
Designing the Data Access Framework
For a data access framework to be useful and effective, it must be easy for the developer to use, and flexible enough to accommodate all types of data access requests. The data access framework in Coding Briefs solves both those important issues.
Figure 1: Basic Data Access Flow, as shown below, depicts the basic flow of the data access framework.
Figure 1: Basic Data Access Flow
The DAL of Coding Briefs Data Access Framework is designed to perform two major tasks:
•Select records
•Execute actions against records
Selecting Records
A well designed DAL will allow the developer to select records and return them in a container, such as a DataSet, or a SqlDataReader. The DAL that we will develop for Coding Briefs is no exception. It will allow us to select records and return the selection as a DataSet or a SqlDataReader.
Executing Records
Coding Briefs’ DAL provides functionality to execute an action type stored procedure. Action stored procedures are those that insert records, update records, or delete records from a database. For execution, the number of affected row is returned. So now that we have a high-level view of the functionality of the DAL, it is time to jump in and get started developing it.
In the next section, we will get the sample applications up and running.
Quick Start Guide
We are ready to get the project running. And, we are in luck. This volume of Coding Briefs includes the fully developed data access framework, as well as, two sample applications; one for Windows and one for ASP .NET.
Running the Sample Applications
Follow these steps to get the Windows Sample Application (WSA) up and running:
1.Register for an account at www.runtimepublishing.com
2.Log into the site
3.Click the Register Publication menu option
4.Select the publication name from the dropdown
5.Enter the Unlock Code that is located in the section, Running the Windows Sample Application, later on in this brief
6.Click the Submit link to register your copy of Coding Briefs
7.Click on the Subscribers Lounge menu option
8.Select the edition of the publication that has been registered (Example: Visual Basic Coding Briefs for eBooks, or C# Coding Briefs Online, etc)
9. Click on the Download Code link for your volume and save the Zip file to a location on your local hard drive
10.Unzip the file and see all the projects that are available for your publication
11.Follow the steps in the section, Attaching the Contact Database, to attach the database
12.Open the Windows sample and the ASP .Net sample projects
13.Select Project →Coding Briefs volume Properties to open the Property Pages
14.Select the Settings tab and check the cbConn Connection String to make sure that it is setup properly for your server, then make any changes that are necessary
When ready, press F5 to start the Windows Sample Application, as shown in Figure 2.
In the next section, we will manage the database connections.
Figure 2: The Windows Sample Application
Managing the Database Connections
In order to manage the database connections, we must be able to make a connection to the database, and to disconnect from the database.
In Coding Briefs, we don’t want to keep a persistent connection to the database. Instead, we want to open the database, perform our select or execute action, and then close the database.
Opening a Database Connection
To perform an action against the database, we have to open a connection to the database. When we open a database connection, we can access the objects of the database by making calls to the database connection.
The following code is used to open a database connection.
Listing 1: Opening a Database Connection
Public Sub DBConnect(
ByVal connectionString As String,
ByRef errorParms() As Object)
Try
SqlConn =
New SqlConnection(connectionString)
SqlConn.Open()
Catch
' Return the error to the calling program.
errorParms(0) = 999
errorParms(1) = Err.Description
errorParms(2) =
System.Reflection.MethodBase.GetCurrentMethod.Name
End Try
End Sub
