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

---

Скачать книгу