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
The code shown in Listing 1 opens a connection to the database.
The procedure DBConnect accepts one parameter, the connection string, and thereby allows us to open different databases based on the connection string that is passed to DBConnect.
A SqlConnection object represents a unique session to a SQL Server data source, and it is used together with a SqlDataAdapter and a SqlCommand to increase performance when connecting to a Microsoft SQL Server database.
•First, we set the variable SQLConn to a SQLConnection, and use the connection string that is passed to the procedure
•Next, we open the connection with the Open command
Closing a Database Connection
When a SqlConnection goes out of scope, it won't be closed, so we have to explicitly close the connection by calling Close or Dispose. Close and Dispose will both close a connection to an open database;however, Close is the preferred method of closing any open connection.
The code shown below in Listing 2 can be used to close a database connection.
Listing 2: Closing a Database Connection
Public Sub DBDisconnect(ByRef errorParms As Object)
Try
SqlConn.Close()
SqlConn = Nothing
SqlCmd = Nothing
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