Making Design Tradeoffs | Applied ADO.NET: Building Data-Driven Solutions

There are three main tradeoffs to look at between good and fast design. Depending on the requirements for your application you'll have to make decisions on each of these items and evaluate the kinds of risks doing it the fast way pose to the design of your system.

The seemingly innocent code in Listing 18-9 has a number of implications you must be aware of when working with OR mapping in a system such as the video store application.

Listing 18-9: SelectVideosCheckedOutToUser

 CREATE PROCEDURE dbo.SelectVideosCheckedOutToUser @UserID decimal AS BEGIN SELECT VideoTapeID, Title, Description FROM VideoTape WHERE VideoTapeID IN ( SELECT VideoTapeID FROM VideoCheckOut  as vco WHERE CheckedOutToUserID=@UserID AND VideoTapeID NOT IN (SELECT VideoTapeID From VideoCheckIn as vci WHERE vco.VideoCheckOutID=vci.VideoCheckOutID) ) END

Understanding Logic in Stored Procedures

The first possible tradeoff is having anything resembling logic in your stored procedures. In the SelectVideosCheckedOutToUser stored procedure, a nested subquery finds out what VideoCheckOut entries don't have VideoCheckIn entries. An alternate approach to this is to load all VideoCheckOut entries and all VideoCheckIn entries using simple stored procedures, then make this comparison in your VB .NET code.

The advantage of using the stored procedure in Listing 18-9 is a much faster response time with only a small amount of data traveling over your network. Because the results are being filtered by the database server, only the small chunk of data you're looking for will be returned.

The disadvantage of placing this type of logic in a stored procedure is the maintainability of your code and the cost/scalability of your database server. When another programmer comes along and wants to alter the behavior of what the definition of CheckedOut is within your system, they'll have to trace your objects all the way back to the stored procedures to find out how you did this.

Another disadvantage is that your database is a finite resource. Running Microsoft SQL Server 2000 on .NET Server, you can cluster a maximum of eight machines together at an unbelievable hardware/software cost. Every bit of logic you have your stored procedures apply takes more resources from these machines. On the other hand, you can add Web servers almost indefinitely using a number of solutions available in the marketplace for a minimal cost (in comparison to expanding the database).

The other point of balance is large chunks of data. If the data to be returned is potentially large, it could be more of a burden on your database servers overall than having just sorted through it on its own. It'll take some time and thinking as to whether it's more database intensive one way or another. In the video store application, for example, most people in the system of an average video store have checked out more than 100 tapes but only have two checked out at a time. Some people have checked out more than 1,000, and returning all 2,000 (1,000 from the VideoCheckIn table and 1,000 from the VideoCheckOut table) could cause serious stress on the database server and the network.

Understanding Component Isolation

The next problem with the stored procedure in Listing 18-9 is the co-mingling of components. Notice how the stored procedure returns not only the VideoTapeID, but the Title and Description as well? This means that if an alteration is made to the VideoTape component's data model, the VideoCheckInCheckOut object will require reworking.

The only advantage of returning the data in line is speed. If only the was returned, then the calling program would likely make a separate call to the VideoTapeDataAccess object's GetVideoTapeByID, each in turn calling a stored procedure on its own to get the VideoTape referenced by the returned ID. By returning the data inline you're removing the need for the calling program to make separate round trips to the database.

The disadvantage of returning the data in line is that changes in your VideoTape component will likely break the VideoCheckInCheckOut component. If you add a field to the VideoTape object such as YearReleased, you must alter the VideoCheckInCheckOut object and stored procedure as well. If these types of interdependencies are not well documented, you're well on your way to creating an unmaintainable system.

The good design way to do it is to have the stored procedure only return VideoTapeIDs and then leave the job of turning these IDs into objects in the VideoTape component's court.

If you employ a technique like this in your system, make sure that it's well documented within the VideoTape and the VideoCheckInCheckOut components. Both sides that are engaging in naughty behavior need to have a line drawn between them not only to let future programmers that come after you know what you've done, but also to help you keep track of what you've done.

Creating a Workaround on Part of the Problem

The component isolation part of the speed vs. design problem has a partial solution. You can take the specifics of turning the results of your VideoCheckInCheckOut stored procedure from data into objects and place it squarely on the shoulders of your VideoTape component. By designing the VideoTape component to include public methods for transforming VideoTapeData objects into VideoTape objects, you can bank on ADO.NET's SqlDataAdapter's ability to map the results of a stored procedure to fields in an ADO.NET DataSet. Listing 18-10 shows the VideoCheckInCheckOut object, found in ObjectRelationalMappingVB/VideoStoreDataModel/VideoCheckInCheckOut.vb.

Listing 18-10: The VideoCheckInCheckOut Object

         Public Function GetVideoTapesCheckedOutToUser(ByRef usr As User) _ As VideoTape()             Dim cmd As New SqlDataAdapter("ap_SelectVideosCheckedOutToUser", _ New SqlConnection(connectionString))             cmd.SelectCommand.CommandType = CommandType.StoredProcedure             cmd.SelectCommand.Parameters.Add("@UserID", _ SqlDbType.Decimal)             cmd.SelectCommand.Parameters("@UserID").Value = usr.UserID             Dim data As VideoTapeData             cmd.Fill(data)             Return VideoTapeDataAccess.GetVideoTapeArrayFromData(data)         End Function

In the GetVideoTapesCheckedOutToUser method, you use the VideoTapeData object to fill the result set and use the GetVideoTapeArrayFromData method on the VideoTapeDataAccess object to turn this data into an array of VideoTape objects. This means that any alterations made to the structure of the VideoTape table in the database will require fixing only the VideoTape component's code, not the VideoCheckInCheckOut component's code. The stored procedure that the VideoCheckInCheckOut component is using will still have to be altered to ensure it includes the new fields, but if you're careful in documenting your dependencies this should make it manageable (see Listing 18-11).

Listing 18-11: The VideoCheckInCheckOut Object

     Public Class VideoCheckInCheckOut         Private connectionString As String         Public Sub New(ByVal connStr As String)             Me.connectionString = connStr         End Sub         Public Function GetVideoTapesCheckedOutToUser(ByRef usr As User) _ As VideoTape()             Dim cmd As New SqlDataAdapter("ap_SelectVideosCheckedOutToUser", _ New SqlConnection(connectionString))             cmd.SelectCommand.CommandType = CommandType.StoredProcedure             cmd.SelectCommand.Parameters.Add("@UserID", _ SqlDbType.Decimal)             cmd.SelectCommand.Parameters("@UserID").Value = _ usr.UserID             Dim data As New VideoTapeData()             cmd.Fill(data)             Return VideoTapeDataAccess.GetVideoTapeArrayFromData(data)         End Function         Public Function GetVideoTapesCheckedIn() As VideoTape()             Dim cmd As New SqlDataAdapter("ap_SelectVideosCheckedIn", _ New SqlConnection(connectionString))             cmd.SelectCommand.CommandType = CommandType.StoredProcedure             Dim data As New VideoTapeData()             cmd.Fill(data)             Return VideoTapeDataAccess.GetVideoTapeArrayFromData(data)         End Function         Public Function CheckOutVideoToUser(ByRef usr As User, _ ByRef toUsr As User, ByRef vt As VideoTape)             If (Me.IsVideoCheckedOut(vt)) Then    Throw New Exception("Video is already checked out to another user.")             End If             Dim cmd As New SqlCommand("ap_CheckOutVideo", _ New SqlConnection(connectionString             cmd.CommandType = CommandType.StoredProcedure             cmd.Parameters.Add("@CheckedOutByUserID", _ SqlDbType.Decimal)             cmd.Parameters.Add("@CheckedOutToUserID", _ SqlDbType.Decimal)             cmd.Parameters.Add("@VideoTapeID", _ SqlDbType.Decimal)             cmd.Parameters("@CheckedOutToUserID").Value = _ usr.UserID             cmd.Parameters("@CheckedOutByUserID").Value = _ toUsr.UserID             cmd.Parameters("@VideoTapeID").Value = _ vt.VideoTapeID             cmd.Connection.Open()             cmd.ExecuteNonQuery()             cmd.Connection.Close()         End Function         Public Function CheckInVideoFromUser(ByRef usr As User, _ ByRef vt As VideoTape)             Dim cmd As New SqlCommand("ap_CheckInVideo", _ New SqlConnection(connectionString))             cmd.CommandType = CommandType.StoredProcedure             cmd.Parameters.Add("@UserID", _ SqlDbType.Decimal)             cmd.Parameters.Add("@VideoTapeID", _ SqlDbType.Decimal)             cmd.Parameters("@UserID").Value = usr.UserID             cmd.Parameters("@VideoTapeID").Value = _ vt.VideoTapeID             cmd.Connection.Open()             cmd.ExecuteNonQuery()             cmd.Connection.Close()         End Function         Public Function IsVideoCheckedOut(ByRef vt As VideoTape) As Boolean             Dim cmd As New SqlCommand("ap_IsVideoCheckedOut", _ New SqlConnection(connectionString))             cmd.CommandType = CommandType.StoredProcedure             cmd.Parameters.Add("@VideoTapeID", _ SqlDbType.Decimal)             cmd.Parameters("@VideoTapeID").Value = _ vt.VideoTapeID             cmd.Connection.Open()             Dim isCheckedOut As Boolean             isCheckedOut = cmd.ExecuteScalar()             cmd.Connection.Close()             Return isCheckedOut         End Function     End Class

In the VideoCheckInCheckOut object you're modeling all of your "verbs." Each method of this object actually performs an action or describes a state as opposed to just returning data.

The two workhorses of this object are CheckInVideoFromUser and CheckOutVideoToUser. Each is a thin wrapper around calling a stored procedure but provides an important spot where future business logic can be added. If, for example, you chose to add accounting functionality to the system, it would be a simple matter of adding calls that code at this location.

Under that scenario, you would not need to change any of your front-end code to work with these enhancements to your system.

Creating the VideoCheckInCheckOut Test Case

To properly test the VideoCheckInCheckOut component, you'll need to do the following:

Check a video to see if it's checked out
Check out a video
Check the video's status
List all checked out videos for the user
List all the videos that are not checked out to anyone
Check in the video
Check the status of the video
List all the videos checked out to the user
List all the videos that are not checked out to anyone

As you can see, this test case is quite a bit more complicated than the test case for the User and VideoTape components! It's not enough to just test each method once; you have to verify that the listing methods actually get the right data when the video's state is displayed. Listing 18-12 shows the test case for the VideoCheckInCheckOut component.

Listing 18-12: The Test Case for the VideoCheckInCheckOut Component

 Imports VideoStoreDataModel.EnterpriseVB.VideoStore.Data Module VideoCheckInCheckOutTestCase     Sub Main()         Dim vDAC As New VideoTapeDataAccess()         Dim uDAC As New UserDataAccess()         Dim vciDAC As New VideoCheckInCheckOut(vDac.connectionString)         Dim tTape As VideoTape         tTape = vDAC.GetVideoTapeByID(1)         Console.WriteLine("Is Tape Checked Out:" + _ vciDAC.IsVideoCheckedOut(tTape).ToString())         Dim tUser As User         tUser = uDAC.GetUserByID(1)         Console.WriteLine("Checking out video.")         vciDAC.CheckOutVideoToUser(tUser, tUser, tTape)         Console.WriteLine("Is Tape Checked Out:" + _ vciDAC.IsVideoCheckedOut(tTape).ToString())         Dim tapes() As VideoTape         tapes = vciDAC.GetVideoTapesCheckedOutToUser(tUser)         Console.WriteLine("Tapes Currently Checked Out To User:")         DisplayTapeArray(tapes)         Console.WriteLine("Tapes Not Currently Checked Out To Anyone")         tapes = vciDAC.GetVideoTapesCheckedIn()         DisplayTapeArray(tapes)         vciDAC.CheckInVideoFromUser(tUser, tTape)         Console.WriteLine("Video Checked In")         Console.WriteLine("Is Tape Checked Out:" + _ vciDAC.IsVideoCheckedOut(tTape).ToString())         tapes = vciDAC.GetVideoTapesCheckedOutToUser(tUser)         Console.WriteLine("Tapes Currently Checked Out To User:")         DisplayTapeArray(tapes)         Console.WriteLine("Tapes Not Currently Checked Out To Anyone")         tapes = vciDAC.GetVideoTapesCheckedIn()         DisplayTapeArray(tapes)         Console.WriteLine("Press Any Key To Quit...")         Console.ReadLine()     End Sub     Sub DisplayTapeArray(ByRef tapes() As VideoTape)         Dim i As Integer         For i = 0 To tapes.Length - 1             Console.WriteLine("TAPE:" + tapes(i).Title)         Next     End Sub End Module

The test case correctly exercises each of the methods provided by the VideoCheckInCheckOut component. There's a caveat here, though; if the video (VideoTapeID 1) is already checked out, an exception will be thrown. Figure 18-5 shows the output of CheckInCheckOut test case.

click to expand
Figure 18-5: The output of the CheckInCheckOut test case