Creating the Basic Design of the Video Store Application

The OR mapping technique used in this chapter was inspired by a demo application from Microsoft intended to show best practices in .NET called Duwamish7. Downloading Duwamish7, reading through the source code, and truly understanding how it works can really help you become a much better .NET programmer. It covers topics such as statelessness, OR mapping, logical three-tier structures, and more. Download it from MSDN (http://msdn.microsoft.com) by searching for Duwamish7.

Understanding the Basic Architecture

Three objects make up a mapped object. These are the data object, the mapped object, and the Data Access Component (DAC). The DAC creates and fills the data object with data, then passes a single row of that data to create an instance of the mapped object.

The data object is simply an in-code representation of the database table you plan on using for an object. In .NET specifically, it's a subclassed DataTable or DataSet where you have defined what the columns names are as well as the data types. The application outside of the mapping never uses this object directly.

The mapped object uses your data object to point to a particular row of the DataTable found in the data object and maps the column names from the data object to easy-to-use property names. The mapped object is the primary interface between the rest of your application and your data.

The DAC is the glue that holds the data object and the mapped object together and persists the changes to the database. The DAC defines what stored procedures or database queries are necessary to load, insert, update, or delete your object's data from the database. Figure 18-1 shows the basic concept of OR mapping.

Figure 18-1: Basic concept of OR mapping

Creating the Database Design of the Video Store

The example for this chapter builds all of the data objects required for a simple video store application. It's probably too much to consider it a complete video store because you're not actually tracking money and late fees, but it'll do quite well to help you track your own video collection. It'll also come in handy if you have friends who borrow videos from you and you need to remember whom you let borrow what.

The design of the video store database is simple. At the core of this application is the VideoTape table, as shown in Table 18-1. The VideoTape table tracks only the title and description of the title in question. You could have quite a bit more information about a particular title and even expand the application to track DVDs as well as video tapes, but for this application, let's keep it simple. You can always expand this later to create a complete application.

Table 18-1: The VideoTape Table

COLUMN NAME	SQL SERVER DATA TYPE	.NET DATA TYPE
VideoTapeID	Numeric	Decimal
Title	Char (50)	String
Description	Text	String

The VideoTape table relates to the VideoCheckIn table (shown in Table 18-2) and the VideoCheckOut table (shown in Table 18-3) to model what is checked in and out. It also relates to the Users table to track who participated in the transaction. The information tracked in each table is the VideoTapeID, the date it was checked in or out on, the user who performed the check in or out, and the user to whom it was checked in or out.

Table 18-2: The VideoCheckIn Table

COLUMN NAME	SQL SERVER DATA TYPE	.NET DATA TYPE
VideoTapeID	Numeric	Decimal
CheckedInOn	Smalldatetime	DateTime
CheckedInByUserID	Numeric	Decimal
CheckedInFromUserID	Numeric	Decimal
VideoCheckOutID	Numeric	Decimal

Table 18-3: The VideoCheckOut Table

COLUMN NAME	SQL SERVER DATA TYPE	.NET DATA TYPE
VideoCheckOutID	Numeric	Decimal
VideoTapeID	Numeric	Decimal
CheckedOutOn	Smalldatetime	DateTime
CheckedOutByUserID	Numeric	Decimal
CheckedOutToUserID	Numeric	Decimal

The Users table tracks the people involved with the system (see Table 18-4). In a real-world situation, you would also provide a Login table so that the people involved with the system can log in and an access control system controls which users can actually check things in and out. Of course, to run a video store with this application, you'd also have to handle accounting and more, so don't get any ideas that this is a full-fledged application!

Table 18-4: The Users Table

COLUMN NAME	SQL SERVER DATA TYPE	.NET DATA TYPE
UserID	Numeric	Decimal
FirstName	Char (20)	String
LastName	Char (20)	String

We selected this particular application because it provides the opportunity to touch on many of the real-world issues you'll run in to when attempting to create a scalable Web application using .NET. Some of the topics ahead cover how to handle potentially large sets of data among dependant tables, how to manage the disconnects between the database's handling of null values, and how .NET handles null values. Additionally, you'll see some practical considerations to keep in mind when designing a scalable system with .NET.

Creating the Data Object

The data object represents what the table structure in the database is in your code. The data object is necessary for type safety and to provide a "bucket" for your data. You'll have to create a data object for each object in your database. You'll start by examining the VideoTapeData object, shown in Listing 18-1.

Listing 18-1: The VideoTapeData Object

 Public Class VideoTapeData     Inherits DataTable     Public Sub New()         MyBase.New("VideoTape")         Me.Columns.Add("VideoTapeID", Type.GetType("System.Decimal"))         Me.Columns.Add("Title", Type.GetType("System.String"))         Me.Columns.Add("Description", Type.GetType("System.String"))     End Sub End Class 

This code is fairly simple. You declare the VideoTapeData object as inheriting from the DataTable object. Basically what you're doing is taking the functionality of the existing DataTable object and turning it into a custom data type. Inside the constructor, you call the base class by calling MyBase.New to call the constructor of the DataTable object. The constructor for the DataTable object you're using in this example sets the table's name.

The rest of the constructor sets the names of each column as in the database and provides the .NET data types of each column. For example, the SQL Server data type Numeric is the Decimal object in .NET. The char and text fields both map to the same .NET type, String.

The reason for taking this extra step to create a "typed" DataTable instead of using a regular untyped table is to add one extra layer of data integrity. If a stored procedure returns more fields than this data object can handle, an error will be thrown immediately, highlighting that you've altered the data returned by a stored procedure without putting proper handling for it in your code.

Creating the Mapped Object

The mapped object is the front-end object that your application will use to interact with the database. The purpose of this object is to translate the data fields from the data object into easily managed fields in your object. This object is where you encapsulate the core logic of your object and what it's supposed to do.

Wherever possible, you should make your stored procedures as, well, "dumb" as possible. If a stored procedure has more logic than to save or get data, then there's likely a problem with your system design.

That said, sometimes it does make sense to put logic in your database where absolutely required for performance reasons. A common example is in the security management of your system. There are some situations where the security management part of your system needs to query across a large table and return only the results that the logged-in user has the access to see. In this case it may make sense to put a subset of your security system's logic in a subquery of your Select statement. Part of good design is knowing when to throw out an otherwise beautiful design for performance reasons. Any time you do this, always take a careful look at your design and try to come to a decision as to whether your design is flawed or this is just a sacrifice that has to be made for performance within the context of the situation (see Listing 18-2).

Listing 18-2: The VideoTape Object

 Public Class VideoTape     Protected data As DataTable     Protected index As Integer Protected Friend ReadOnly Property MyData() As DataRow()             Get                 Dim myRow() As DataRow = {Me.data.Rows(index)}                 Return myRow End Get End Property     Public Sub New(ByRef data As VideoTapeData, ByVal index As Integer)         Me.data = data         Me.index = index         End Sub         Public Function GetColumn(ByRef ColumnName As String) As Object             Return data.Rows(index)(ColumnName)         End Function         Public Function SetColumn(ByRef ColumnName _ As String, ByRef ColumnValue As Object)             data.Rows(index)(ColumnName) = ColumnValue         End Function         Public Property VideoTapeID() As Decimal             Get                 If (GetColumn("VideoTapeID").GetType() Is _ Type.GetType("System.DBNull")) Then                     Return -1                 End If                 Return CType(GetColumn("VideoTapeID"), Decimal)             End Get             Set(ByVal Value As Decimal)                 SetColumn("VideoTapeID", Value)             End Set         End Property         Public Property Title() As String             Get                 Return "" + GetColumn("Title")             End Get             Set(ByVal Value As String)                 SetColumn("Title", Value)             End Set         End Property         Public Property Description() As String             Get                 Return "" + GetColumn("Description")             End Get             Set(ByVal Value As String)                 SetColumn("Description", Value)             End Set         End Property End Class 

The constructor of the VideoTape object takes a VideoTapeData object and an integer as its arguments. The VideoTapeData object actually holds the data, and the integer index passed-in tells this object the row in the VideoTapeData DataTable for which this instance of the VideoTape object is mapping.

MyData is a read-only property that returns the DataRow that this object represents. The data access component uses MyData to get a reference to the VideoTape object's data. It was declared as a protected friend so that only other objects in this namespace can use it. The purpose of this is to hide this property from the rest of your program and remove the temptation other programmers might feel in trying to use the your object's data directly.

Each of the object's properties being mapped need the ability to get and set the values of the database field that corresponds with their values. To help with this task, we've included two functions, GetColumn and SetColumn, that allow the rest of the object's properties to avoid having to use the complete ADO.NET syntax for referencing a particular row and column. It's helpful to just think of the columns involved. By saving what row is being manipulated as plumbing that specifies the particular row of interest in this object, you can greatly simplify your code.

In the VideoTapeID property, do you notice the check to see if the value of the column is System.DBNull? This is an interesting disconnect between the database's view of values and .NET's. Because .NET's primitive types such as Integers, Decimals, Strings, DateTimes, and Guids are value types, they do not have a null state.

You can see an example of .NET's lack of null values for certain types with the DateTime object. If you declare a variable such as a DateTime and never initialize it with any given value, it's still given a value by .NET. The following statement prints 01/01/0001 even though myDate was never initialized:

 Dim myDate As DateTime Console.WriteLine(myDate.ToShortDateString()) 

In the VideoTape object, take a look at the checking it does to ensure that a null pointer exception doesn't occur. In the getter of the property, a check is made to see if the type of the column is System.DBNull. If it is, then it returns –1 to indicate that the value is not initialized to your .NET program. Otherwise, it casts the type of the column to a decimal and returns the result to the calling program:

         Public Property VideoTapeID() As Decimal             Get                 If (GetColumn("VideoTapeID").GetType() Is _ Type.GetType("System.DBNull")) Then                     Return -1                 End If                 Return CType(GetColumn("VideoTapeID"), Decimal)             End Get             Set(ByVal Value As Decimal)                 SetColumn("VideoTapeID", Value)             End Set         End Property 

The database meanwhile treats data that hasn't been given a value as null. This becomes a problem when you try to move the content from the database into .NET because .NET needs to be able to represent the database's null value to the rest of your application in a way that lets your application know that a column has not been initialized. This includes things such as using –1 as a value for integers and decimals, an empty string for strings, and so on.

The trip back to the database compounds this problem. When the data is stored, if you have written a value to your data object such as an uninitialized DateTime, an error will occur. By handing these problems in the mapped object, you'll make the rest of your program more reliable by preventing value checks throughout your code.

The two string fields being mapped in this object are easier to handle. If the database has a character field, such as a Char or Text, that's null, it'll return a System.DBNull object. The handling on the .NET side is easier than with other value types.

The following statement returns the value if there is one:

 Return "" + GetColumn("Description") 

If the column is null in the database, it returns an empty string because ""+ calls the ToString method on the System.DBNull object. Either way, an acceptable .NET value is returned to the calling program.

Creating the Data Access Component

The DAC's responsibility is to fill your data objects with data from the database and create instances of your mapped object. It does this by defining what stored procedures should be used to load and save your data and makes them accessible to simple front-end methods. Once the DAC is done, the rest of your program will be able to get and set your mapped objects just as easy as calling a simple method (see Listing 18-3).

Listing 18-3: The VideoTapeDataAccess Object

 Public Class VideoTapeDataAccess     Public connectionString As String         Protected adapter As SqlDataAdapter         Protected loadAll As SqlDataAdapter         Public Sub New()             connectionString ="Your Connection String"             adapter = New SqlDataAdapter()             adapter.SelectCommand = New SqlCommand("ap_VideoTapeLoadByID", _ New SqlConnection(connectionString))             adapter.SelectCommand.CommandType = CommandType.StoredProcedure             adapter.SelectCommand.CommandType = CommandType.StoredProcedure             adapter.SelectCommand.Parameters.Add _             ("@VideoTapeID", SqlDbType.Decimal, 0, "VideoTapeID")             adapter.InsertCommand = New SqlCommand("ap_VideoTapeInsert", _ New SqlConnection(connectionString))             adapter.InsertCommand.CommandType = CommandType.StoredProcedure             adapter.InsertCommand.Parameters.Add("@VideoTapeID", _ SqlDbType.Decimal, 0, "VideoTapeID")             adapter.InsertCommand.Parameters("@VideoTapeID").Direction = _ ParameterDirection.Output             adapter.InsertCommand.Parameters.Add("@Title", _ SqlDbType.Char, 50, "Title")             adapter.InsertCommand.Parameters.Add("@Description", _ SqlDbType.Text, 0, "Description")             adapter.UpdateCommand = New SqlCommand("ap_VideoTapeUpdate", _ New SqlConnection(connectionString))             adapter.UpdateCommand.CommandType = CommandType.StoredProcedure             adapter.UpdateCommand.Parameters.Add _             ("@VideoTapeID", SqlDbType.Decimal, 0, "VideoTapeID")             adapter.UpdateCommand.Parameters.Add _             ("@Title", SqlDbType.Char, 50, "Title")             adapter.UpdateCommand.Parameters.Add _             ("@Description", SqlDbType.Text, 0, "Description")             adapter.DeleteCommand = New SqlCommand("ap_VideoTapeDelete", _ New SqlConnection(connectionString))             adapter.SelectCommand.CommandType = CommandType.StoredProcedure             adapter.DeleteCommand.Parameters.Add("@VideoTapeID", _ SqlDbType.Decimal, 0, "VideoTapeID")             loadAll = New SqlDataAdapter()             loadAll.SelectCommand = New SqlCommand("ap_VideoTapeLoadAll", _ New SqlConnection(connectionString))             loadAll.SelectCommand.CommandType = _ CommandType.StoredProcedure         End Sub         Public Function GetVideoTapeByID(ByVal vtID As Decimal) As VideoTape             Dim data As New VideoTapeData()             adapter.SelectCommand.Parameters("@VideoTapeID").Value = vtID             adapter.Fill(data)             If (data.Rows.Count < 1) Then                 Return Nothing             End If             Dim vt As New VideoTape(data, 0)             Return vt         End Function         Public Function GetAllVideoTapes() As VideoTape()             Dim data As New VideoTapeData()             loadAll.Fill(data)             Return GetVideoTapeArrayFromData(data)         End Function     Public Shared Function GetVideoTapeArrayFromData(ByRef data As VideoTapeData) _ As VideoTape()              Dim vArray(data.Rows.Count - 1) As VideoTape              Dim i As Integer              For i = 0 To (data.Rows.Count - 1)                  vArray(i) = New VideoTape(data, i)              Next i              Return vArray          End Function          Public Function SetVideoTape(ByRef vTape As VideoTape)              adapter.Update(vTape.MyData)          End Function          Public Function RemoveVideoTape(ByRef vTape As VideoTape)              adapter.DeleteCommand.Parameters("@VideoTapeID").Value = _ vTape.VideoTapeID             adapter.DeleteCommand.Connection.Open()             adapter.DeleteCommand.ExecuteNonQuery()             adapter.DeleteCommand.Connection.Close()         End Function End Class 

The constructor of the VideoTapeDataAccess object initializes the SqlDataAdapters to use the stored procedures that handle the actual database access. The constructor initializes the Select, Insert, Update, and Delete stored procedures on the instance called adapter, but only the Select statement on the loadAll instance.

The reason for this is there are multiple ways you want to choose what data to get, but in the end there's only one way to update it in that database. Both adapters could have been fully initialized, but that would produce a large amount of redundant code. By only initializing one adapter for updates, inserts, and deletes, you're avoiding additional code that would needlessly slow down your program.

The GetVideoTapeByID method gets one VideoTape object out of the database. It sets the @VideoTapeID parameter on the stored procedure to the value passed in and fills the VideoTapeData object with data. If the database didn't find any records with the VideoTapeID passed in, then it returns Nothing, a null value. If there's at least one record returned, it creates an instance of the VideoTape object and initializes it with the data it loaded into the VideoTapeData object and sets the index to 0 so that the first record retuned is used by the mapped object. Because the Video-TapeID column is a unique identity column in the database, this stored procedure should never return more than one row.

The GetAllVideoTapes method is much simpler. It creates an instance of the VideoTapeData object and then uses the loadAll SqlDataAdapter to fill it. It then calls the GetVideoTapeArrayFromData method to turn the DataTable into an array of VideoTape objects.

The GetVideoTapeArrayFromData method creates an array of VideoTape objects the length of the number of results returned from the database and loops through the results, creating an instance of the VideoTape object for each row. As it goes through, it sets the index of each VideoTape object to a different row in the same result set. In the end it provides the array of VideoTape objects to the calling program.

The purpose of making the GetVideoTapeArrayFromData a separate method instead of simply putting this code inline with the GetAllVideoTapes method is to provide this method to other objects that may be attempting to create instances of VideoTape objects. You'll see this put to work later in the VideoCheckInCheckOut object.

If there are no video tapes in the database, it returns an array with no elements in it, not a null. This is, in general, good behavior for a DAC to have when it's expected to return an array of objects. In most situations the calling program will want to loop through however many elements are in the array, even if there are no elements. If the calling program wants to know if a null was returned, it can always check the length of the array to see if it's zero. In the end, returning a zerolength array should simplify your application's code.

The SetVideoTape object is amazingly short. It simply calls to update the row of the VideoTape object to the database. It automatically selects which stored procedure to call and then executes it with the corresponding data. If it was an insert call, the identity column will be automatically updated in the instance of the VideoTape object. This comes in handy when writing the front-end code of the application. There are many cases where the user will define the data for several related tables on one page, and you'll need a way to know the IDs of the related columns in order to allow the user to edit the data properly.

The one-line update call really highlights one of the core ideas of .NET: Hide as much complexity as possible. Your object builds on this principle to hide the rest of the database access functionality in your OR mapped component.

The RemoveVideoTape method actually uses the Delete command of the adapter directly. You could just remove the row in question from the VideoTapeData object and then call Update, but doing so could lose the isolation of the task to be performed. You would have to update the entire VideoTapeData set, which may include changes that the calling program doesn't want updated. This ensures that encapsulation is enforced and only the row you want to delete is deleted.

In most cases, it's a bad idea to actually delete rows from the database to remove an item from your application. What you would normally do is have a deleted field on the table that's flagged so you don't have to worry about maintaining your database's referential integrity or actually losing data. You would also modify your stored procedures that load data into your adapters to not return rows that are flagged as deleted unless it's referred to directly by its primary key.

Building the VideoTape Test Case

It's important to build a test case for an object relationally mapped system. A test case ensures that your component does what it's supposed to do and ensures that none of your changes have broken existing code. Each step of your test case should say what the expected output is and then show the output so that it's well marked in terms of what it's supposed to do in order to compare the output. It's even possible to build automated test cases that compare these types of things automatically, but that's not required for this particular system.

The VideoTapeTestCase object will do the following:

1. Load a VideoTape object from the database and display what its data is

2. Change the title and description

3. Store the object in the database

4. Reload the object and display what its data is

5. Restore the original VideoTape data and store it

6. Load all of the VideoTape objects from the database and show what each one is

Listing 18-4 shows the code for the test case.

Listing 18-4: The Test Case for the VideoTape Component

 Imports VideoStoreDataModel.EnterpriseVB.VideoStore.Data Module VideoTapeTestCase     Sub Main()         Dim vDAC As New VideoTapeDataAccess()         Dim tVTape As VideoTape         Console.WriteLine("Loading VideoTape")         tVTape` = vDAC.GetVideoTapeByID(1)         Console.WriteLine("VideoTape Loaded:" + tVTape.GetType().ToString())         DisplayVideoTape(tVTape)         Dim origTitle = tVTape.Title         Dim origDescription = tVTape.Description         tVTape.Title = "TestTitle"         tVTape.Description = "TestDescription"         vDAC.SetVideoTape(tVTape)         tVTape = vDAC.GetVideoTapeByID(1)         Console.WriteLine _ ("After alteration. Title=TestTitle Description=TestDescription")         DisplayVideoTape(tVTape)         tVTape.Title = origTitle         tVTape.Description = origDescription         vDAC.SetVideoTape(tVTape)         tVTape = vDAC.GetVideoTapeByID(1)         Console.WriteLine _ ("Restored to origional, should look like the first displayed VideoTape.")         DisplayVideoTape(tVTape)         Console.WriteLine("List all VideoTapes")         Dim tVideos() As VideoTape         tVideos = vDAC.GetAllVideoTapes()         Dim i As Integer         For i = 0 To tVideos.Length - 1             DisplayVideoTape(tVideos(i))         Next         Console.WriteLine("Press Any Key To Quit...")         Console.ReadLine()     End Sub     Sub DisplayVideoTape(ByRef tVTape As VideoTape)         Console.WriteLine("---------------------------")         Console.WriteLine("VideoTapeID:" + tVTape.VideoTapeID.ToString())         Console.WriteLine("Title:" + tVTape.Title)         Console.WriteLine("Description:" + tVTape.Description)     End Sub End Module 

Figure 18-2 shows the VideoTape test case output.

Figure 18-2: The VideoTape test case output

Using the User Component

The User component and the VideoTape component are virtually identical from the design point of view. The core requirements are the same—you want to be able to load one user record or all of them—and the basic data structure of both objects is the same. The purpose of the User component is to model the people involved with the application. The User is the one who can check in and out videos (see Listing 18-5).

Listing 18-5: The User Object in the User Component

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.Common Namespace EnterpriseVB.VideoStore.Data     Public Class User         Protected data As DataTable         Protected index As Integer         Protected Friend ReadOnly Property MyData() As DataRow()             Get                 Dim myRow() As DataRow = {Me.data.Rows(index)}                 Return myRow             End Get         End Property         Public Sub New(ByRef data As UserData, ByVal index As Integer)             Me.data = data             Me.index = index         End Sub         Public Function GetColumn(ByRef ColumnName As String) As Object             Return data.Rows(index)(ColumnName)         End Function         Public Function SetColumn(ByRef ColumnName As String, _ ByRef ColumnValue As Object)             data.Rows(index)(ColumnName) = ColumnValue         End Function         Public Property UserID() As Decimal             Get                 If (GetColumn("UserID").GetType() Is _ Type.GetType("System.DBNull")) Then                     Return -1                 End If                 Return CType(GetColumn("UserID"), Decimal)             End Get             Set(ByVal Value As Decimal)                 SetColumn("UserID", Value)         End Set     End Property     Public Property FirstName() As String         Get             Return "" + GetColumn("FirstName")         End Get         Set(ByVal Value As String)             SetColumn("FirstName", Value)         End Set     End Property     Public Property LastName() As String         Get             Return "" + GetColumn("LastName")         End Get         Set(ByVal Value As String)             SetColumn("LastName", Value)         End Set     End Property End Class 

The User object is specifically designed to have as little logic as possible in it. As such, it has simple handling for null values and the ability to use values from the data object.

The "verb" types of actions concerning the user in relation to the rest of the system belong in the business objects, which you'll see later in the VideoCheckInCheckOut component.

Listing 18-6 shows the UserData object in the User component.

Listing 18-6: The UserData Object in the User Component

 Public Class UserData     Inherits DataTable     Public Sub New()         MyBase.New("Users")         Me.Columns.Add("UserID", Type.GetType("System.Decimal"))         Me.Columns.Add("FirstName", Type.GetType("System.String"))         Me.Columns.Add("LastName", Type.GetType("System.String"))     End Sub End Class 

The UserData object defines only three fields in the DataTable: UserID, FirstName, and LastName. Although much more data could be tracked on a user such as addresses and telephone numbers, let's keep it simple.

If you want to add this additional functionality, make sure you model it in separate tables. A common mistake beginners make is modeling all of a user's data in one giant table. Some systems go so far as having the person's name, address, phone numbers, credit card information, company, title, and more all in one table. This is bad both from a clean design sense and a performance sense. If a table has too many columns, SQL server handles it slowly.

Listing 18-7 shows the UserDataAccess object.

Listing 18-7: The UserDataAccess Object in the User Component

     Public Class UserDataAccess         Public connectionString As String         Protected adapter As SqlDataAdapter         Protected loadAll As SqlDataAdapter         Public Sub New()             connectionString = "Password=1deadrat;User ID=sa;" + _     "Initial Catalog=VideoStore;Data Source=grimsaado2k;" + _     "Workstation ID=GRIMSAADO2K;"             adapter = New SqlDataAdapter()             adapter.SelectCommand = New _ SqlCommand("ap_UsersLoadByID", New SqlConnection(connectionString))             adapter.SelectCommand.CommandType = _ CommandType.StoredProcedure             adapter.SelectCommand.CommandType = _ CommandType.StoredProcedure             adapter.SelectCommand.Parameters.Add _             ("@UserID", SqlDbType.Decimal, 0, "UserID")             adapter.InsertCommand = New SqlCommand("ap_UsersInsert", _ New SqlConnection(connectionString))             adapter.InsertCommand.CommandType = _ CommandType.StoredProcedure             adapter.InsertCommand.Parameters.Add("@UserID", _ SqlDbType.Decimal, 0, "UserID")             adapter.InsertCommand.Parameters("@UserID").Direction = _ ParameterDirection.Output             adapter.InsertCommand.Parameters.Add("@FirstName", _ SqlDbType.Char, 50, "FirstName")             adapter.InsertCommand.Parameters.Add("@LastName", _ SqlDbType.Text, 0, "LastName")             adapter.UpdateCommand = New SqlCommand("ap_UsersUpdate", _ New SqlConnection(connectionString))             adapter.UpdateCommand.CommandType = CommandType.StoredProcedure             adapter.UpdateCommand.Parameters.Add("@UserID", _ SqlDbType.Decimal, 0, "UserID")             adapter.UpdateCommand.Parameters.Add("@FirstName", _ SqlDbType.Char, 50, "FirstName")             adapter.UpdateCommand.Parameters.Add("@LastName", _ SqlDbType.Text, 0, "LastName")             adapter.DeleteCommand = New SqlCommand("ap_UsersDelete", _ New SqlConnection(connectionString))             adapter.SelectCommand.CommandType = CommandType.StoredProcedure             adapter.DeleteCommand.Parameters.Add("@UserID", _ SqlDbType.Decimal, 0, "UserID")             loadAll = New SqlDataAdapter()             loadAll.SelectCommand = New SqlCommand("ap_UsersLoadAll", _ New SqlConnection(connectionString))             loadAll.SelectCommand.CommandType = _ CommandType.StoredProcedure         End Sub         Public Function GetUserByID(ByVal userID As Decimal) As User             Dim data As New UserData()             adapter.SelectCommand.Parameters("@UserID").Value = userID             adapter.Fill(data)             If (data.Rows.Count < 1) Then                 Return Nothing             End If             Dim vt As New User(data, 0)             Return vt         End Function         Public Function GetAllUsers() As User()             Dim data As New UserData()             loadAll.Fill(data)             Dim uArray(data.Rows.Count) As User             Dim i As Integer             For i = 0 To data.Rows.Count                 uArray(i) = New User(data, i)             Next i             Return uArray         End Function         Public Function SetUser(ByRef user As User)             adapter.Update(user.MyData)         End Function         Public Function RemoveUser(ByRef user As User)             adapter.DeleteCommand.Parameters("@UserID").Value = user.UserID             adapter.DeleteCommand.Connection.Open()             adapter.DeleteCommand.ExecuteNonQuery()             adapter.DeleteCommand.Connection.Close()         End Function     End Class End Namespace 

The DAC for the User component allows the calling program to look up a single user, list all of the users, update or insert a user, or delete a user. Once again the stored procedures are kept simple and the DAC itself is simple.

Creating the User Test Case

The User test case will verify that the User component works as expected. It will test your User component by doing the following:

1. Load a user from the database and display what its data is

2. Change the first name and last name

3. Store the object in the database

4. Reload the object and display what its data is

5. Restore the original user data and store it

6. Load all of the users from the database and show what each one is

Listing 18-8 shows the code for the UserTestCase object.

Listing 18-8: The UserTestCase Object

 Imports VideoStoreDataModel.EnterpriseVB.VideoStore.Data Module UserTestCase     Sub Main()         Dim uDAC As New UserDataAccess()         Dim tUser As User         Console.WriteLine("Loading User")         tUser = uDAC.GetUserByID(1)         Console.WriteLine("User Loaded:" + tUser.GetType().ToString())         DisplayUser(tUser)         Dim origFirstName = tUser.FirstName         Dim origLastName = tUser.LastName         tUser.FirstName = "TestFirst"         tUser.LastName = "TestLast"         uDAC.SetUser(tUser)         tUser = uDAC.GetUserByID(1)         Console.WriteLine _ ("After alteration. FirstName=TestFirst LastName=TestLast")         DisplayUser(tUser)         Console.WriteLine _ ("Restored to origional, should look like the first displayed user.")         tUser.FirstName = origFirstName         tUser.LastName = origLastName         uDAC.SetUser(tUser)         tUser = uDAC.GetUserByID(1)         DisplayUser(tUser)         Console.WriteLine("List all users")         Dim tUsers() As User         tUsers = uDAC.GetAllUsers()         Dim i As Integer         For i = 0 To tUsers.Length - 1             DisplayUser(tUsers(i))         Next         Console.WriteLine("Press Any Key To Quit...")         Console.ReadLine()     End Sub     Sub DisplayUser(ByRef tUser As User)         Console.WriteLine("UserID:" + tUser.UserID.ToString())         Console.WriteLine("FirstName" + tUser.FirstName)         Console.WriteLine("LastName:" + tUser.LastName)     End Sub End Module 

This program ensures that the component works as expected. You should run this test case any time you've made changes to the User component to make sure that it still works as expected. Figure 18-3 shows the output of the UserTestCase program.

Figure 18-3: The output of the UserTestCase program

Managing Dependencies

Although the User component is similar to the VideoTape component, it's important to talk about what isn't in the User object. Components that are at the center of your system and relate to a number of other tables should wherever possible avoid referencing other components. The other components can reference your core component, but by avoiding the core component referencing narrowly scoped entities such as Check In/Check Out, you can keep your code portable to be used in other projects that don't use Check In Check Out.

Figure 18-4 shows the dependencies within the video store application.

Figure 18-4: The dependencies in the video store application

In the design of this application, the Check In/Check Out component has all of the dependencies. You could easily move the VideoTape or User components to their own DLL and use them in other projects with or without each other, and nothing would be broken. The Check In/Check Out component, on the other hand, requires both the User component and VideoTape component.

When you're designing your next project, do some careful thinking about what components you plan on being able to use in other projects and think about what you need to do to make sure that core component is as independent as possible.

Paranoid Programming

In a perfect world, software has a specification that doesn't change, programmers produce the software, and it's done; there's no need for a second version. In reality, specifications change, goals change, and revisions happen faster than you intend. The only way to really manage this risk is what we like to call paranoid programming.

When designing your system, imagine what users might ask for next and try to anticipate it in your design and code. If the specification says that each user in the system has only one address, design the table schema and data objects to allow a one-to-many relationship because when users change their minds—and they will—it will be painful to make an alteration of this type if you did not allow for it in your original design.

The hardest part of being a professional programmer is managing expectations and exceeding them. A good design will help you manage the S.A.L.E.S. (Say Anything Let Engineering Sweat) part of the equation. Making sure your core components can easily be added to new projects will help this immensely.

To find out what specification for the next version of your product is, check the marketing brochures your sales team is showing to customers; this should give you a good idea of where the product is headed.

Creating the Check In/Check Out Component

The Check In/Check Out component allows the calling program to do the following:

1. List all of the video tapes currently checked out

2. Check out a video

3. Check in a video

4. Discover the status of a single video

This object is at the core of your system's functionality. The User and VideoTape components hold no real logic, but the Check In/Check Out component has enough to make up the rest of it. Although technically the VideoTape and User objects are business objects in that they hold some of the system's logic, the CheckInCheckOut object is where the vast majority of the job happens.

Death to N-Tier

Traditional thought is the only way to create a scalable system on the Web is the three-tier model (what academics call the four-tier model because they count the browser as a tier). The three-tier model is made up of a Web server, an application server, and a database server. The thinking goes that the code on your Web servers should only provide Hypertext Markup Language (HTML) generation and an interface back to the application server, which is actually running the code with the business logic in it. Of course, behind that is the database server, which should be treated as a dumb repository of data with no logic in it whatsoever (depending on the server you purchased, this may be a more truthful statement than with others).

The three-tier model has a number of problems, though. Every layer you add increases the likelihood of down time. If your application server cluster goes down, then your whole application will cease to function. Also, every incoming request goes from the Web server to the database server and on to the database server—and of course back again. The extra layer increases latency and adds to the complexity of your application. Complexity is inherently evil and must be destroyed; therefore, the physical two-tier model and logical three-tier model were born.

Under the 2/3 model, the database stays a dumb place where your data is stored, but the business logic objects live on the Web server. We're not saying your business logic should go in your code behind—far from it. What we're saying is that the data objects and logic objects you're creating in this chapter should live in nice, neat, compiled DLLs that your ASPX pages access.

This reduces latency as network usage goes down, this reduces complexity because you've cut a layer out of your application, and this significantly reduces the total cost of deploying your application.

The CheckInCheckOut object looks quite a bit like a function library and less like a real object. The reason is simple; this component defines the actions you're taking on the video tapes and users. By isolating the types of actions that can be taken with users and video tapes inside this object, the .aspx pages that use this business object layer have the ability to do amazing things in just a few lines of code:

 VideosCheckedOutDataGrid.DataSource = (New _ VideoCheckInCheckOut(conn)). _ GetVideoTapesCheckedOutToUser(user) 

This example shows how the VideoCheckInCheckOut component allows everything that's currently checked out to a single person to be listed in an .aspx page in one line of code. This truly comes in handy when you've got 20 different pages listing the videos checked out to a single user and the need arises to alter the actual logic behind it. One scenario where this might happen is if the checkout process allowed the user to choose how many days they wanted to pay for when they checked out the video. This would involve altering the data model and the logic concerning the data model, especially if a "list overdue" videos feature was added that would include even more dependant logic than just a simple check in/check out.

If you had embedded the call to a stored procedure directly into your page as you probably would have under the ASP model, you would have quite a task ahead of you! You would have to go to each page and add the handling for the new field now being returned from the stored procedure and take proper action on it to handle it. Obviously, this is a situation to avoid.

In the VideoCheckInCheckOut object, you'll notice a few things that are poorly coded in terms of good object-oriented principles, so be prepared. There's a conflict between good and fast under this model. You can have your code 90 percent well designed and easy to maintain, but sometimes you just have to break it a little to get the kind of performance you need.

Seeing How It Works

The VideoCheckInCheckOut component's workings are fairly simple. The design goal of this component is to provide the ability to check out a video tape to a user, check it back in, and be able to determine the state of this information in a few ways.

When a row is inserted into the VideoCheckOut table, a new VideoCheckOutID is "automagically" generated by SQL Server. The VideoCheckIn table has a VideoCheckOutID that references this ID. Anytime there's a VideoCheckOutID in the VideoCheckOut table that's not in the VideoCheckIn table, a video is currently checked out.

You can apply a few strategies on this data to determine if a video tape is checked out; you'll learn about this in the next section, which is about the design of the Check In/Check Out components.