Using Data in the Middle Tier

So far, we've identified the presentation layer as basically what the client, or user, sees. We've also identified the middle layer, or tier, as the plumbing that makes the application work. Now it's time to show the final tier of the infamous "n-tier" architecture the data tier. Usually, the data tier consists of a database, relational or otherwise. It could be an XML file, .INI file, or any other data repository. For these examples, though, we use SQL Server 2000 as the data repository.

In Chapter 11, Listing 11.1, we showed a simple example of using the middle tier to connect to the database, execute a query, and return an object presented in the form of an HTML table. None of the code in that listing affects any of the data returned; it simply displays it. The actual execution of the query even happens in the database. Listing 12.4 repeats the code from Chapter 11, Listing 11.5, and we use it here to show some different points (discussed following the listing).

Listing 12.4 ShowCustomers subroutine

 Private Sub ShowCustomers()      'This is just a simple function to get      'things started, showing the collection      'and displaying tblCustomer.      'Initialize the connection object with      'the connection string.      Dim conn As New SqlConnection(connString)      'Also, initialize the command object with      'the SQL to be executed.      Dim cmd As New SqlCommand("exec sp_GetCustomersOrders", conn)      conn.Open()      Dim dReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)      While dReader.Read        Response.Write(dReader.GetString(0))        Response.Write("&nbsp;" & dReader.GetString(1))        Response.Write("&nbsp;" & dReader.GetDateTime(2))        Response.Write("<BR>")      End While      dReader.Close()      conn.Close()   End Sub 

Note in Listing 12.4 the use of a stored procedure, which is an excellent example of placing application logic in the data tier of an application. The reason for this placement is quite simple: It is based on the complex scientific fact that the shortest distance between two points is a straight line. If the code exists on the database server, is compiled to run efficiently on the database server, and is executed against a database on that same server, it will execute faster than if the code in the stored procedure resides in the middle tier.

However, this is not a perfect world, and use of stored procedures isn't allowed in all circumstances. This restriction brings into play another complex scientific principle that of the path of least resistance. Least resistance in this case means that the next tier up from the data tier is used to execute the application logic. Because there is a requirement that limits use of the client to display only meaning no client-side scripting it's time to look at how we can implement functionality that might be better served by the data tier being placed in the middle tier.

Creating Reusable Middle-Tier Components

In this section, we take a simple query that could be a stored procedure and place it in a reusable object. The goal is to show how creating functionality in one place can be used in multiple applications. To begin, within an existing or new Visual Basic.NET project, right-click on the project name in the Solution Explorer. Select Add and then Add Component. A dialog will prompt you to enter a filename. Enter GetRowCount.vb, which will be the name of the class that you can access from any application when you've finished.

Note

All code samples from Chapter 11 and this chapter are based on a solution called Novelty1. It is the namespace used for every file created in these chapters.

Once you have created the empty component file, all that is needed is a little code. For this example, create a function, GetRowCount, that returns an integer value. The code in Listing 12.5 comprises the complete file.

Listing 12.5 GetRowCount.vb

 Imports System.Data Imports System.Data.SqlClient Public Class GetRowCount   Inherits System.ComponentModel.Component   Public Function GetRowCount() As Integer     Try     Dim connString As String     'Recall from Chapter 11 the discussion on     'String.Intern. If it already exists and     'has the same value, its memory location     'will be used instead of creating a new     'instance.     connString =server=(local);database=Novelty;TRUSTED_CONNECTION=Yes"     Dim conn As New SqlConnection(connString)     Dim cmd As New SqlCommand("select count(*) from tblCustomer", conn)     conn.Open()     Dim dReader As SqlDataReader =cmd.ExecuteReader (CommandBehavior.CloseConnection)     While dReader.Read        'Get what should be the first and        'only row in our result set.        GetRowCount = dReader.GetValue(0)     End While     dReader.Close()     conn.Close()   Catch     System.Console.WriteLine ("An error has occured " & Err.Description)   End Try End Function #Region "Component Designer generated code " Public Sub New(ByVal Container As System.ComponentModel.IContainer)      MyClass.New()      'Required for Windows.Forms Class Composition Designer support     Container.Add(Me)   End Sub   Public Sub New()     MyBase.New()     'This call is required by the Component Designer.     InitializeComponent()     'Add any initialization after the InitializeComponent() call.   End Sub   'Component overrides dispose to clean up the component list.   Protected Overloads Overrides Sub Dispose(ByVal disposing As   Boolean).     If disposing Then        If Not (components Is Nothing) Then        components.Dispose()        End If     End If     MyBase.Dispose(disposing)   End Sub   'Required by the Component Designer   Private components As System.ComponentModel.IContainer   'NOTE: The following procedure is required by the Component   'Designer.   'It can be modified using the Component Designer.   'Do not modify it with the code editor.   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()     components = New System.ComponentModel.Container()   End Sub #End Region End Class 

Diving deeply into inheritance is beyond the scope of this book, but we can show a good example of what it is. Although there is no direct code in the component for a ToString method, there is one once the component has been compiled. It is due to the line of code

 Inherits System.ComponentModel.Component  

It brings in functionality that exists in the class

 System.ComponentModel.Component  

which inherits functionality from System.Object. Thus inheritance brings functionality into the class without our having to do any work.

To complete the component, we simply right-click on the solution name in the Solution Explorer and select Build. The component is now ready to use, but how can we use it? We create a WebForm named GetRowCountTest.aspx. The code behind for this page is shown in Listing 12.6. Note that, at the beginning of the page, an Imports statement brings in the functionality of the GetRowCount component.

Listing 12.6 GetRowCountTest.aspx.vb

 Imports Novelty1.GetRowCount Public Class GetRowCountTest   Inherits System.Web.UI.Page #Region "Web Form Designer Generated Code"   'This call is required by the Web Form Designer.   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()   End Sub   Private Sub Page_Init (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init     'CODEGEN: This method call is required by the Web Form Designer.     'Do not modify it with the code editor.     InitializeComponent()   End Sub #End Region   Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load      Dim GRC As New GetRowCount()      Response.Write(GRC.GetRowCount.ToString)      GRC.Dispose()   End Sub End Class 

Calling the inherited Dispose method isn't necessary, but it does expedite clearing this object from memory. Once the page has been created and code pasted or typed, you can use the Build and Browse functionality from the Solution Explorer to preview the page. Using the Novelty database built throughout this book, and accompanying scripts, you should get a Web page that simply has the number 2000 at the top of it.

Using the Component from Another Application

Now that we have this gem of code, it's time to share it. To do so, we create a new application of type Visual Basic.NET Windows Application and set a reference to the Novelty1.dll file located in the \bin/ directory of the Novelty1 application. If you're not working in the Novelty1 namespace or have created your own Web application the .dll file that you will need to reference is located in the \bin/ directory of that Web application (or a Windows application, as the case may be). It is typically inetpub\wwwroot\<Application Name>\bin.

To set a reference, right-click on the References element in the Solution Explorer and select Add Reference. When the References dialog appears, click on Browse to locate the .dll just described. Then click on OK on the References Dialog. For this example, the file is located in

C:\Inetpub\Wwwroot\Novelty1\bin

and is named Novelty1.dll.

In the code for your Windows application previously described, note that, if you start an Imports statement, the namespace Novelty1 is available to you. From here, the rest of the code looks almost like that in Listing 12.6. Listing 12.7 represents the code for the default form object created whenever you have created a new Windows application in VS.NET.

Listing 12.7 Form1.vb

 Imports Novelty1.GetRowCount Public Class Form1     Inherits System.Windows.Forms.Form #Region "Windows Form Designer generated code"   Public Sub New()     MyBase.New()     'This call is required by the Windows Form Designer.     InitializeComponent()     'Add any initialization after the InitializeComponent() call.   End Sub   'Form overrides dispose to clean up the component list.   Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)     If disposing Then       If Not (components Is Nothing) Then         components.Dispose()       End If     End If     MyBase.Dispose(disposing)   End Sub   'Required by the Windows Form Designer   Private components As System.ComponentModel.IContainer   'NOTE: The following procedure is required by the Windows Form   'Designer.   'It can be modified with the Windows Form Designer.   'Do not modify it with the code editor.   Friend WithEvents Label1 As System.Windows.Forms.Label   Friend WithEvents Button1 As System.Windows.Forms.Button   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()     Me.Label1 = New System.Windows.Forms.Label()     Me.Button1 = New System.Windows.Forms.Button()     Me.SuspendLayout()     '     'Label1     '     Me.Label1.Location = New System.Drawing.Point(8, 16)     Me.Label1.Name = "Label1"     Me.Label1.Size = New System.Drawing.Size(248, 16)     Me.Label1.TabIndex = 0     Me.Label1.Text = "Label1"     '     'Button1     '     Me.Button1.Location = New System.Drawing.Point(264, 8)     Me.Button1.Name = "Button1"     Me.Button1.Size = New System.Drawing.Size(72, 24)     Me.Button1.TabIndex = 1     Me.Button1.Text = "Test It!"     '     'Form1     '     Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)     Me.ClientSize = New System.Drawing.Size(344, 54)     Me.Controls.AddRange(New System.Windows.Forms.Control() Me.Button1, Me.Label1)     Me.Name = "Form1"     Me.Text = "Form1"     Me.ResumeLayout(False)   End Sub #End Region   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)  Handles Button1.Click     Dim GRC As New Novelty1.GetRowCount()     Label1.Text = "There are" & GRC.GetRowCount.ToString & "rows in the table."     GRC.Dispose()   End Sub End Class 

The only modifications we made here were to add a Label object and a Button object. The label's text isn't set until the code from our object has been executed. The next step from here is Web Services.