| The layers of a multi-tier application are usually specific to the application's domain. The data layer, for example, contains code to execute only the stored procedures used by that particular application. Layers can be reusable in other applications if the functions they perform are needed elsewhere. For example, the business tier in an Accounts Payable application could talk to the business tier in Personnel to validate employee names on expense reports. This type of reusability is one of the main advantages of object-oriented programming. However, across all your applications there will likely be some common data-layer functions. For example, you need code to connect to a database, execute a stored procedure by name, and so on, no matter what the application domain. Rather than duplicating these functions with each new data layer, you can build a data access module that will be used in each project. Some advantages of using a shared data access module are as follows: Developers can quickly access database functions. Connection strings are easy to maintain and change. Monitoring and tuning database activity can be performed in one place. Data access techniques are consistent across multiple projects.
In this section, we'll walk through developing a module that performs several common data access functions. You may want to use this code (or some version of it) in your own projects. Note The sample Data Access layer described here is designed for SQL Server, although it would not be hard to adapt it to create an OLEDB version. To do this, you will need to replace the SQL-prefixed classes with their OLEDB equivalents and modify the connection string appropriately.
Designing the Data Access Module Any application that uses a database has several basic data operations: queries, inserts, updates, and deletes. Each of these activities can be characterized as either returning rows or not returning rows. In addition, these tasks may be performed using dynamic SQL generated by your program or, more ideally, using stored procedures. Therefore, in designing a generic data access module, we need functions that perform at least the following operations: Execute a stored procedure, with or without returning data. Perform a dynamic SQL query, with or without returning data.
To meet these requirements, we will need to define several custom functions in our data layer, as described in the following sections. Managing Database Connections One benefit of a shared data access module is making connection management easier for the application developer. To achieve this goal in our data module, we will store connection strings in a common area, the Windows registry. Each data access function will have an AppID parameter, which stands for "Application ID." The data access module will use this application ID to find the appropriate connection string. The code in Listing 22.8 defines a function called GetConnectionString that retrieves connection information from the registry using a key based on the application ID. Listing 22.8 DATALAYER.ZIP Managing Connections Private Function GetConnectionString(ByVal strAppID As String) As String Const CompanyRegistryArea As String = "SOFTWARE\MyCompany\" Dim strRegPath As String = CompanyRegistryArea & strAppID Dim RegTemp As RegistryKey RegTemp = Registry.LocalMachine.OpenSubKey(strRegPath, False) Return RegTemp.GetValue("ConnectionString", "").ToString End Function The code in Listing 22.8 uses the Registry class (discussed in Chapter 24) to retrieve information from a common area of the registry.  For more on accessing the registry from VB.NET, p.667
Each application setting is stored in a key under a common area for the entire company. For example, connection information for the WEBSTORE application would be located under the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\MyCompany\WEBSTORE Storing connection information in the registry makes it easy to connect to different databases without recompiling your application. Simply set the ConnectionString value to a valid connection string using the REGEDIT utility. In addition, when the developer writes code, he only needs to be concerned with setting the correct application ID. Note Accessing the registry every time you make a database connection does require some overhead, but in the author's opinion, the overhead is outweighed by increased flexibility.
Managing Parameters As we mentioned earlier, one common function of every data access layer is the ability to execute stored procedures. Stored procedures usually have parameters. Although the names and data types of any given stored procedure's parameters are application-specific, the process of executing a parameterized stored procedure is not. Therefore, we should be able to include a generic function in our data access module to set up a SQLCommand object for any given stored procedure. In order to accomplish this, we need a way for the calling function to pass in a set of parameters, as well as a method of returning output parameters. This brings about an interesting dilemma. In order to write a function that accepts an arbitrary number of parameters, we'll need to make use of an array or collection. The Parameters collection would be ideal, but it is part of a command object and cannot be created independently. Therefore, we'll need to accept parameters in a variable array and then add them to the SQLCommand using the Add method. The code in Listing 22.9 defines the necessary functions so the data access module can covert the parameter arrays to and from the SQLCommand object. Listing 22.9 DATALAYER.ZIP Managing Parameters Private Sub PopulateCommandParms(ByVal arParms() As SqlParameter,_ ByRef cmd As SqlCommand) 'Adds each parameter in the array to the command object Dim i As Integer For i = LBound(arParms) To UBound(arParms) cmd.Parameters.Add(arParms(i)) Next End Sub Private Sub PopulateOutputParms(ByVal cmd As SqlCommand,_ ByRef arParms() As SqlParameter) 'Copies output parameter values back into the array 'Assumes parameter array is already populated Dim i As Integer For i = LBound(arParms) To UBound(arParms) If arParms(i).Direction = ParameterDirection.Output Then arParms(i).Value = cmd.Parameters(arParms(i).ParameterName).Value End If Next End Sub At first glance, the code in Listing 22.9 may seem like an unnecessary step, because adding parameters to a command is very easy. However, the benefit is that the calling function does not have to get involved with setting up the SQLCommand object at all. Creating the Public Functions Now that we have created all the necessary support functions for our data access module, we will define the Public functions available to the data layer. The functions in Listing 22.10 can execute arbitrary stored procedures and SQL statements. Listing 22.10 DATALAYER.ZIP Creating the ExecSP and ExecSQL Subroutines Public Sub ExecSP(ByVal strAppID As String, _ ByVal strProcName As String, _ ByRef intReturnVal As Integer, _ Optional ByRef ParmArray() As SqlParameter = Nothing, _ Optional ByRef dsResults As DataSet = Nothing) Dim cn As SqlConnection Dim cmd As SqlCommand Dim adt As SqlDataAdapter 'ESTABLISH THE CONNECTION cn = New SqlConnection() cn.ConnectionString = GetConnectionString(strAppID) cn.Open() 'SET UP THE SQLCOMMAND OBJECT cmd = New SqlCommand() cmd.CommandText = strProcName cmd.CommandType = CommandType.StoredProcedure cmd.Connection = cn 'SET UP PARAMETERS If Not (ParmArray Is Nothing) Then Call PopulateCommandParms(ParmArray, cmd) End If 'EXECUTE THE COMMAND If dsResults Is Nothing Then intReturnVal = cmd.ExecuteNonQuery() Else dsResults = New DataSet() adt = New SqlDataAdapter() adt.SelectCommand = cmd intReturnVal = adt.Fill(dsResults) End If 'CLOSE CONNECTION cn.Close() 'POPULATE OUTPUT PARAMETERS If Not (ParmArray Is Nothing) Then Call PopulateOutputParms(cmd, ParmArray) End If End Sub Public Sub ExecSQL(ByVal strAppID As String, _ ByVal strSQLCmd As String, _ ByRef intReturnVal As Integer, _ Optional ByRef dsResults As DataSet = Nothing) Dim cn As SqlConnection Dim cmd As SqlCommand Dim adt As SqlDataAdapter 'FIRST, ESTABLISH THE CONNECTION cn = New SqlConnection() cn.ConnectionString = GetConnectionString(strAppID) cn.Open() 'SET UP THE SQLCOMMAND OBJECT cmd = New SqlCommand(strSQLCmd, cn) 'EXECUTE THE COMMAND If dsResults Is Nothing Then intReturnVal = cmd.ExecuteNonQuery() Else dsResults = New DataSet() adt = New SqlDataAdapter() adt.SelectCommand = cmd intReturnVal = adt.Fill(dsResults) End If 'CLOSE CONNECTION cn.Close() End Sub The key to the versatility of the code in Listing 22.10 is the parameter list. By using optional parameters and the ByRef keyword, the ExecSP and ExecSQL subroutines are able to handle a variety of different SQL statements. For more on function parameters, p.201 For example, if the calling function does not pass a DataSet object, the ExecSP procedure uses the ExecuteNonQuery method and returns the number of rows affected. In addition, the PopulateCommandParms function is only called if necessary, allowing the execution of parameter-less stored procedures. Exercise: Using the Data Access Module Now that we have created a set of generic database functions, we'll put them to use in a sample project. To begin, create a new Windows Application project, called TestApp. Perform the following steps to set up all the necessary components of this project: -
Rename the form to frmUILayer. -
Add two new classes to the project, named clsBusinessLayer and clsDataLayer. -
Add a new module to the project, named DataFuncs.vb. -
Copy the code from Listings 22.8, 22.9, and 22.10 into the DataFuncs.vb module.
When you have completed these steps, open the Solution Explorer window and verify that it looks like the one in Figure 22.5. Figure 22.5. The sample project is a simple multi-tier application.  Now that you have set up the framework for the application, we can begin programming the individual layers. For the sake of expediency, we will continue to use the existing Person table. Planning the Requirements The motivation for any program starts with the requirements. For our sample project, let's assume the requirement is to determine the maximum age of a person in a particular state. (As you may recall from earlier in the chapter, we have already defined a stored procedure that will accomplish this task). From this basic requirement, we can determine the shell of some functions in the business layer: The user interface layer will call the preceding two functions in the business layer. To get started with the sample application, open the Code editor for the clsBusinessLayer class. Enter the following lines of code: Public Sub GetMaxAgeByState(ByVal strState As String, ByRef intMaxAge As Integer) End Sub Public Sub GetStateList(ByRef dsStates As DataSet) End Sub In a multi-tier environment, the user-interface layer only talks to the business layer. Although the code in our business layer does not actually do anything yet, defining the skeleton functions allows us to work on the business layer or the user interface independently. Building the User Interface To design the user interface for our sample application, open the designer window for the frmUILayer form. Perform the following steps to create the UI code that interacts with the clsBusinessLayer class. -
Add a Button control to the form. Set its Name property to btnGetAge and its Text property to Find Max Age. -
Add a ComboBox control to the form. Set its Name property to cmbStateList and its DropDownStyle property to DropDownList. -
Add the following lines of code to the end of the form's New subroutine. Dim BusObj As clsBusinessLayer Dim dsStates As DataSet DsStates = New DataSet() BusObj = New clsBusinessLayer() Call BusObj.GetStateList(dsStates) cmbStateList.DataSource = dsStates.Tables(0).DefaultView cmbStateList.DisplayMember = "State" -
Add the following lines of code to the Click event for btnGetAge. If cmbStateList.SelectedIndex = -1 Then messagebox.Show("Please select a state!") Else Dim BusObj As clsBusinessLayer Dim intMaxAge As Integer Dim strStateCode As String BusObj = New clsBusinessLayer() strStateCode = cmbStateList().Text Call BusObj.GetMaxAgeByState(strStateCode, intMaxAge) Messagebox.Show("Maximum age is " & intMaxAge.ToString) End If
After completing the previous steps, you have completed the user interface layer of the sample application. The next step is to complete the data layer. Coding the Data Layer The data layer of an application is based on a database, so you need to make sure the database contains the necessary stored procedures. If you have been following the examples throughout the chapter, you should have already created the spGetMaxAgeByState stored procedure. If you have not already done so, create the stored procedure now. In addition, you need to edit the registry using REGEDIT and add the connection string for your database, as described earlier. Finally, enter the following code in the clsDataLayer class to create the data layer: Public Sub GetStates(ByRef dsStates As DataSet) Dim strSQL As String Dim intRetval As Integer strSQL = "SELECT DISTINCT State FROM Person ORDER BY State" Call ExecSQL("MYAPP", strSQL, intRetval, dsStates) End Sub Public Sub GetMaxAgeByState(ByVal strState As String, ByRef intAge As Integer) Dim intRetVal As Integer Dim parmArray(1) As SqlParameter parmArray(0) = New SqlParameter("@strState", SqlDbType.Char) parmArray(0).Value = strState parmArray(1) = New SqlParameter("@intResult", SqlDbType.Int) parmArray(1).Direction = ParameterDirection.Output Call ExecSP("MYAPP", "spGetMaxAgeByState", intRetVal, parmArray) intAge = Convert.ToInt32(parmArray(1).Value) End Sub The GetStates and GetMaxAgeByState routines make use of our data access module defined earlier to execute a stored procedure and a dynamic SQL query. Completing the Business Object The final step in the sample application is to complete the business tier by finishing the two functions entered earlier. To accomplish this, open the clsBusinessLayer class and complete the functions as follows: Public Sub GetStateList(ByRef dsStates As DataSet) Dim objData As clsDataLayer objData = New clsDataLayer() Call objData.GetStates(dsStates) End Sub Public Sub GetMaxAgeByState(ByVal strState As String, ByRef intMaxAge As Integer) Dim objData As clsDataLayer objData = New clsDataLayer() Call objData.GetMaxAgeByState(strState, intMaxAge) End Sub The previous business layer functions don't do a lot other than pass parameters to the data layer. Although you might be tempted just to call the data layer directly from the UI, don't! Remember, the business layer knows about business logic, and in a real application some changes to the data or creation of other structures might be required. Executing the Program Finally the sample application is complete. To test the application, execute the program. The combo box will be populated with a list of state abbreviations. Select one and click the button to display the maximum age in the state. It may seem like a lot of work to create all the various classes and modules for a relatively simple task. However, look at all the advantages: By using the DataFuncs.vb module, you were able to execute database queries with fewer lines of code. The application was segmented so that only the data layer had to be concerned with connecting to the database. You can easily point the application to another SQL server by editing the registry. You can use an entirely different type of database by replacing the data layer, without having to rewrite the entire application.
Congratulations on successfully implementing a multi-tier data access application! Although this chapter focused on the data aspects of such an application, there is a lot more to learn about this application architecture. For more information on related topics, please see the following sections: Chapter 9, "Creating Code Components" contains information about remoting, which enables you to access business objects across a network. Chapter 18, "Web Applications and Services" shows you how to expose a business object's functionality as a Web service. Chapter 26, "Debugging and Performance Tuning" describes a way to add error handling and performance monitoring to your database applications.
| 
