The Data Layer

This solution requires three tables: Users, Sessions, and Tasks. Figure 18-3 shows the table relationships.

Figure 18-3. The SuperCompute tables

graphics/f18dp03.jpg

The Users table contains the logic information for all the users who will access the rendering service. The password is stored in plain text, and all password comparisons default to SQL's standard non-case-sensitive behavior. For increased security, this field could hold encrypted binary information. In addition, the Users table includes an optional NotifyQueue column, which is designed to hold the path to a Message Queuing private queue on the user's computer, which can be used for notification. This feature isn't currently implemented, but is designed for possible future enhancements. If you are installing this case study from the code download, you will find only a single defined user, with the user name testuser and the password secret.

The Sessions table is used for the ticket-based authentication. It stores a record for each session currently in progress. Sessions are identified by their unique GUID column, which is passed back to the client. The Sessions table also indicates the date the session was started, which is useful if you need to implement an expiration policy, and the client who is authorized to perform actions with this session ticket.

Finally, the Tasks table lists the jobs that have been submitted for rendering. The Tasks table includes file information, a numeric status that indicates whether the task is new (0), in progress (1), completed (3), or halted with an error (2), and the date the task was submitted and completed. The SourceFile corresponds to the original name of the source file, which is retained to help the client identify the task later. The RenderedFileURL provides a UNC or URL path to the rendered file after the rendering has been completed.

Stored Procedures

The basic implementation of the SuperCompute system requires 11 stored procedures, as described in Table 18-1. These stored procedures represent the tasks required by the rendering service and task processor. They don't include any additional functionality to add new user records or profile the system's overall performance.

Table 18-1. SuperCompute Stored Procedures 

Stored Procedure

Description

GetUser

Retrieves a user ID that matches the supplied login information. Used to authenticate a user.

AddSession

Creates a new session record for a logged-in user and returns the generated GUID session ticket.

GetSession

Retrieves the session for the specified user.

DeleteSession

Removes the session for the specified user. Used when the user logs out.

GetTasksByUser

Retrieves all the task records for a specified user.

GetCompletedTasksByUser

Retrieves all the completed task records for a specified user.

AddTask

Adds a new task record.

GetAvailableTasks

Retrieves the task records that are not in progress, completed, or halted with an error. The task processor will then begin to process these records.

UpdateTaskStatus

Modifies the status of a task.

CompleteTask

Modifies the status of a task, sets the complete date, and adds the UNC file path for the rendered file.

DeleteOldSessions

Enables the administrator to remove old session records that are no longer in use.

Listing 18-1 shows the stored procedure code required to validate a user. If a match is found, the unique numeric ID is returned.

Listing 18-1 Stored procedures for validating a user
 CREATE PROCEDURE GetUser  (     @UserName   varchar(25),     @Password   varchar(25)  )  AS     SELECT [ID] FROM Users     WHERE UserName = @UserName AND [Password]=@Password 

Listing 18-2 presents the four stored procedures used to manage session records. The GUID is generated using the NEWID function built into SQL Server.

Listing 18-2 Stored procedures for managing sessions
 CREATE Procedure AddSession  (     @UserID    int,     @GUID      uniqueidentifier OUTPUT  )  AS    SELECT        @GUID = NEWID()  INSERT INTO Sessions  (     [ID],     UserID,     CreateDate  )  VALUES  (     @Guid,     @UserID,     GETDATE()  ) CREATE PROCEDURE GetSession  (     @GUID    uniqueidentifier,     @UserID  int  )  AS     SELECT CreateDate FROM Sessions     WHERE [ID] = @GUID AND UserID=@UserID     ORDER BY CreateDate DESC CREATE PROCEDURE DeleteSession  (     @GUID    uniqueidentifier  )  AS     DELETE FROM Sessions     WHERE [ID] = @GUID CREATE PROCEDURE DeleteOldSessions  AS     DELETE FROM Sessions     WHERE DATEDIFF(day, CreateDate, GETDATE()) > 5 

The GetSession stored procedure sorts the retrieved rows so that the largest creation date (representing the most recent session) is at the top of the list. It does this because the XML Web service assumes that there is only a single session per user and reads the first record from the list.

Typically, there should be only one session record for a given user at a time, but it is possible for an orphaned session record to exist if a user disconnects without calling the Logout XML Web service method. In this case, the session remains until the database administrator executes the DeleteOldSessions stored procedure, which removes any session record created more than five days earlier.

The remainder of the stored procedures deal with tasks. The GetTasksByUser and AddTask stored procedures shown in Listing 18-3 are the two stored procedures indirectly available to the user through the render XML Web service.

Listing 18-3 User-specific stored procedures for tasks
 CREATE PROCEDURE GetTasksByUser  (     @UserID int  )  AS     SELECT * FROM Tasks     WHERE UserID = @UserID CREATE Procedure AddTask  (     @UserID     int,     @Status     int,     @SourceName varchar(50),     @GUID       uniqueidentifier OUTPUT  )  AS SELECT    @GUID = NEWID()  INSERT INTO Tasks  (     [ID],     UserID,     State,     SourceName,     SubmitDate  ) 
  VALUES  (     @Guid,     @UserID,     @Status,     @SourceName,       GETDATE()  ) 

The task processor uses the GetAvailableTasks, UpdateTaskStatus, and CompleteTask stored procedures to retrieve the tasks that should be processed, to manage the status for each task, and to update the record when the rendering process completes, as shown in Listing 18-4.

Listing 18-4 Processor-specific stored procedures for tasks
 CREATE PROCEDURE GetAvailableTasks  AS     SELECT * FROM Tasks     WHERE State = 0     ORDER BY SubmitDate CREATE Procedure UpdateTaskStatus  (     @GUID uniqueidentifier,     @Status int  )  AS      UPDATE Tasks      SET State = @Status      WHERE [ID] = @GUID CREATE Procedure CompleteTask  (     @GUID            uniqueidentifier,     @RenderedFileURL nvarchar(100),     @Status          int  )  AS     UPDATE Tasks     SET State = @Status,     RenderedFileURL = @RenderedFileURL,     CompleteDate = GETDATE()     WHERE [ID] = @GUID 

Note that the GetAvailableTasks stored procedure sorts the results so that the earliest dates are first. This ensures that tasks are processed in first-in-first-out (FIFO) order.

The Data Component

As with the previous case studies (and every well-designed enterprise application), database access takes place solely through a dedicated database component. Both the rendering XML Web service and the server-side task processor use this database component. Listing 18-5 shows the entity portion of the database component, which defines classes to represent the rows in each table.

Listing 18-5 Data entities
 Public Class UserDetails     Public UserID As Integer     Public UserName As String     Public Password As String     Public Queue As String End Class Public Class SessionDetails     Public CreateDate As DateTime     Public UserID As Integer     Public Ticket As Guid End Class Public Enum TaskStatus     NewAdded = 0     InProgress = 1     HaltedWithError = 2     Complete = 3 End Enum Public Class TaskDetails     Public SourceName As String     Public TaskGUID As Guid     Public Status As TaskStatus     Public SubmitDate As DateTime     Public RenderedFileUrl As String     Public UserID As Integer     Public CompleteDate As Date End Class 

Note

You can add property procedures to the entity classes, but they will not be usable in an XML Web service client. When an XML Web service client creates a proxy class, the generated code includes all the required entity classes but the property procedures are converted to public member variables. However, property procedures can still prove useful when you're executing server-side code.


The UserDB Class

The UserDB class, shown in Listing 18-6, is the simplest part of the database component. It exposes a single AuthenticateUser method, which accepts login information and returns the corresponding numeric user ID if a matching record can be found.

Listing 18-6 The UserDB class
 Public Class UserDB     Private ConnectionString As String     Friend Sub New(ByVal connectionString As String)         Me.ConnectionString = connectionString     End Sub     ' Verify that the specified information is correct.     ' If it is, return the unique UserID.     ' Otherwise, return 0.     Public Function AuthenticateUser(ByVal userName As String, _       ByVal password As String) As Integer         Dim con As New SqlConnection(ConnectionString)         Dim cmd As New SqlCommand("GetUser", con)         Dim r As SqlDataReader         cmd.CommandType = CommandType.StoredProcedure         Dim Param As SqlParameter         Param = cmd.Parameters.Add( _          "@UserName", SqlDbType.NVarChar, 25)         Param.Value = userName         Param = cmd.Parameters.Add( _          "@Password", SqlDbType.NVarChar, 25) 
 Param.Value = password         Dim UserID As Integer = 0         Try             con.Open()             r = cmd.ExecuteReader()             If r.Read() Then                 UserID = r("ID")             Else                 ' No matching user.                 UserID = 0             End If             r.Close()         Finally             con.Close()         End Try         Return UserID     End Function End Class 

Notice that the database classes use exception handling to ensure that the database connection is always closed, even if an error occurs. However, they do not catch the exception or rethrow a higher-level exception (such as an ApplicationException). This is because this component is consumed by server-side code (such as the XML Web service) that might need to be informed of lower-level errors. However, this also means that the XML Web service should handle these exceptions. Otherwise, they will be propagated back to the client.

The SessionDB Class

The SessionDB class wraps the logic for creating, verifying, and removing sessions, in the structure shown here:

 Public Class SessionDB     Private ConnectionString As String     Friend Sub New(ByVal connectionString As String)         Me.ConnectionString = connectionString     End Sub     Public Function CreateSession(ByVal userID As Integer) As Guid         ' (Code omitted)     End Function     ' Removes the indicated session.     Public Sub RemoveSession(ByVal ticket As Guid)         ' (Code omitted)     End Sub     ' Returns a null reference if no matching session is found.     Public Function GetSession(ByVal ticket As Guid, _       ByVal userID As Integer) As SessionDetails         ' (Code omitted)     End Function End Class 

The CreateSession generates a session record for the specified user and returns the GUID ticket. The RemoveSession method deletes a session record. Listings 18-7 and 18-8 show these methods.

Listing 18-7 The CreateSession method
 Public Function CreateSession(ByVal userID As Integer) As Guid     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("AddSession", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@UserID", SqlDbType.Int)     Param.Value = userID     ' Add the output parameter.     Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier)     Param.Direction = ParameterDirection.Output     Try         con.Open()         cmd.ExecuteNonQuery() 
     Finally         con.Close()     End Try     ' Return the unique identifier.     Return Param.Value End Function 
Listing 18-8 The RemoveSession method
 Public Sub RemoveSession(ByVal ticket As Guid)     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("DeleteSession", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier)     Param.Value = ticket     Try         con.Open()         cmd.ExecuteNonQuery()     Finally         con.Close()     End Try End Sub 

Finally, the GetSession method, shown in Listing 18-9, retrieves the session that matches the specified GUID and user ID. By requiring these two criteria, the code ensures that a ticket can't be used for an ID other than the one that was used to log in.

Listing 18-9 The GetSession method
  ' Returns a null reference if no matching session is found. Public Function GetSession(ByVal ticket As Guid, _   ByVal userID As Integer) As SessionDetails 
     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("GetSession", con)     Dim r As SqlDataReader     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier)     Param.Value = ticket     Param = cmd.Parameters.Add("@UserID", SqlDbType.Int)     Param.Value = userID     Dim Session As SessionDetails = Nothing     Try         con.Open()         r = cmd.ExecuteReader         If r.Read() Then             Session = New SessionDetails()             Session.CreateDate = r("CreateDate")             Session.UserID = userID             Session.Ticket = ticket         End If     Finally         con.Close()     End Try     Return Session End Function 

The TaskDB Class

The TaskDB class contains five methods, which allow tasks to be retrieved and updated in different ways. It is possible to shorten the TaskDB class by consolidating some methods for example, by replacing UpdateTaskStatus and CompleteTask with a more generic UpdateTask method. However, more specific, limited stored procedures can generally be optimized more efficiently by the SQL Server engine. In addition, using carefully targeted methods helps focus the application code, preventing it from being used for tasks that haven't been anticipated beforehand (and therefore not optimized or tested for security).

The structure for the TaskDB class is shown here:

 Public Class TaskDB     Private ConnectionString As String     Friend Sub New(ByVal connectionString As String)         Me.ConnectionString = connectionString     End Sub     Public Function GetTasks(ByVal userID As Integer, _       ByVal completedOnly As Boolean) As DataSet         ' (Code omitted.)     End Function     Public Function GetAvailableTasks() As TaskDetails()         ' (Code omitted.)     End Function     Public Function AddTask(ByVal task As TaskDetails) As Guid         ' (Code omitted.)     End Function     Public Sub UpdateTaskStatus(ByVal taskGuid As Guid, _       ByVal status As TaskStatus)         ' (Code omitted.)     End Sub     Public Sub CompleteTask(ByVal taskGuid As Guid, _       ByVal renderedFileUrl As String)         ' (Code omitted.)     End Sub End Class 

Note

Tasks are identified using a statistically unique GUID. Alternatively, you can use an autoincrementing identity column for the same purpose. However, autoincrementing numbers are "guessable"; for example, if you submit a task that is assigned the number 402, it's quite likely that you might find another user's task with an ID such as 400 or 401. A GUID solves this problem because each value is generated independently. Knowing one task GUID can't help a user guess another task's GUID.


Listings 18-10 and 18-11 show the GetTasks method and the GetAvailableTasks method, respectively.

Listing 18-10 The GetTasks method
 Public Function GetTasks(ByVal userID As Integer, _   ByVal completedOnly As Boolean) As DataSet     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("", con)     Dim Adapter As New SqlDataAdapter(cmd)     Dim ds As New DataSet()     If completedOnly Then         cmd.CommandText = "GetCompletedTasksByUser"     Else         cmd.CommandText = "GetTasksByUser"     End If     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@UserID", SqlDbType.Int)     Param.Value = userID     Dim Tasks As New ArrayList()     Try         con.Open()         Adapter.Fill(ds, "Tasks")     Finally         con.Close()     End Try     Return ds End Function 
Listing 18-11 The GetAvailableTasks method
 Public Function GetAvailableTasks() As TaskDetails()     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("GetAvailableTasks", con)     Dim r As SqlDataReader     Dim Tasks As New ArrayList()     Try         con.Open()         r = cmd.ExecuteReader()         Do While r.Read()             Dim Task As New TaskDetails()             Task.TaskGUID = r("ID")             Task.SourceName = r("SourceName")             Task.Status = r("State")             Task.SubmitDate = r("SubmitDate")             ' Here we use ToString() to defend against a null value,             ' which would otherwise cause an exception.             Task.RenderedFileUrl = r("RenderedFileURL").ToString()             Tasks.Add(Task)         Loop     Finally         con.Close()     End Try     Return Tasks.ToArray(GetType(TaskDetails)) End Function 

One interesting fact about the TaskDB class is that the GetTasks method returns a DataSet, whereas the GetAvailableTasks method returns an array of TaskDetails objects. Generally, the second option is better for encapsulation because it hides the database details from the client. However, the second option can't be used in conjunction with client-side data binding because the TaskDetails class doesn't provide property procedures. In fact, even if you add property procedures to the TaskDetails class, this information will not be added to the automatically generated proxy class that the client uses, and data binding still won't be allowed. The only workaround is to either create a client that doesn't use data binding or return a DataSet rather than an array of custom objects. In the interest of simplifying development, the TaskDB class adopts the second approach.

The AddTask method (shown in Listing 18-12) is quite straightforward. It just calls the AddTask stored procedure and returns the generated GUID. This code is typical of a multitier distributed application; if all the components of your system are properly isolated, you'll find that their code is straightforward, logical, and unsurprising.

Listing 18-12 The AddTask method
 Public Function AddTask(ByVal task As TaskDetails) As Guid     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("AddTask", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@UserID", SqlDbType.Int)     Param.Value = task.UserID     Param = cmd.Parameters.Add("@Status", SqlDbType.SmallInt)     Param.Value = task.Status     Param = cmd.Parameters.Add("@SourceName", _       SqlDbType.NVarChar, 50)     Param.Value = task.SourceName     ' Add the output parameter.     Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier)     Param.Direction = ParameterDirection.Output     Try         con.Open()         cmd.ExecuteNonQuery()     Finally         con.Close()     End Try     ' Return the unique identifier.     Return Param.Value End Function 

Finally, the TaskDB class provides two methods for updating database information. The UpdateTaskStatus method (shown in Listing 18-13) allows the internal processor to modify the task status when it is placed in progress or when an error occurs. When the task completes, the CompleteTask method (shown in Listing 18-14) modifies the status accordingly and sets the corresponding file path for the RenderedFileUrl.

Listing 18-13 The UpdateTaskStatus method
 Public Sub UpdateTaskStatus(ByVal taskGuid As Guid, _   ByVal status As TaskStatus)     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("UpdateTaskStatus", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter     Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier)     Param.Value = taskGuid     Param = cmd.Parameters.Add("@Status", SqlDbType.SmallInt)     Param.Value = status     Try         con.Open()         cmd.ExecuteNonQuery()     Finally         con.Close()     End Try End Sub 
Listing 18-14 The CompleteTask method
 Public Sub CompleteTask(ByVal taskGuid As Guid, _   ByVal renderedFileUrl As String)     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand("CompleteTask", con)     cmd.CommandType = CommandType.StoredProcedure     Dim Param As SqlParameter 
     Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier)     Param.Value = taskGuid     Param = cmd.Parameters.Add("@Status", SqlDbType.SmallInt)     Param.Value = TaskStatus.Complete     Param = cmd.Parameters.Add("@RenderedFileURL", _                                SqlDbType.NVarChar, 150)     Param.Value = renderedFileUrl     Try         con.Open()         cmd.ExecuteNonQuery()     Finally         con.Close()     End Try End Sub 

The SuperComputeTables Class

The SuperComputeTables class provides a gateway to the other database classes. It accepts a connection string argument and passes it to a new instance of the UserDB, SessionDB, and TaskDB classes. It also retrieves the connection string information. This approach (shown in Listing 18-15) is the same pattern used in the Chapter 16 and Chapter 17 case studies.

Listing 18-15 Composition with SuperComputeTables
 Public Class SuperComputeTables     Public Users As UserDB     Public Sessions As SessionDB     Public Tasks As TaskDB     Public Sub New()         ' Retrieve the connection string.         Dim Connection As String = ConfigurationSettings.AppSettings( _           "SuperComputeConnection")         ' Create the table classes.         Users = New UserDB(Connection)         Sessions = New SessionDB(Connection)         Tasks = New TaskDB(Connection)     End Sub End Class 


Microsoft. NET Distributed Applications(c) Integrating XML Web Services and. NET Remoting
MicrosoftВ® .NET Distributed Applications: Integrating XML Web Services and .NET Remoting (Pro-Developer)
ISBN: 0735619336
EAN: 2147483647
Year: 2005
Pages: 174

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net