Creating the Data Access Component
The data access component exists within the data tier and serves as the middleman between the underlying database and the business objects. It is responsible for filling the business object with data from database records as well as creating or updating records based upon changes within the business objects.
Implementing the DataAccess Project
The Visual C# Simple Distributed Application solution template (created in Chapter 1, "Introducing .NET and Enterprise Architecture") provides a DataAccess project, as shown in Figure 2-3. Initially, this project contains a single source file that implements the DataComponent object.
Figure 2-3: The DataAccess project for the IssueTracker solution
Creating the Application Database
The IssueTracker enterprise application will consist of eight tables, three containing application data records and five containing reference data records. You can create the tables manually using the table designer within the SQL Server Enterprise Manager or by using the SQL Server Query Analyzer to execute the setup scripts defined in Appendix A, "Building the IssueTracker Database."
Using the SQL Server Enterprise Manager
The SQL Server Enterprise Manager provides an interface for administering the entire SQL Server database. Services include creating and managing database instances, connections, users, stored procedures, tables, and constraints. The built-in table designer offers the fastest and easiest way to create new database tables.
The IssueTracker enterprise application will require a database, such as SQL Server, to store its application data. From the SQL Server Enterprise Manager, expand the local server tree node, as shown in Figure 2-4. Next, click the node labeled Databases and then select Action ˜ New Database from the menu. Enter IssueTracker for the database name , as shown in Figure 2-5, and click OK to create the database.
Figure 2-4: The SQL Server Enterprise Manager
Figure 2-5: Creating a new database in SQL Server
Once you have created the database, you can add new tables with the help of the table designer. First, select Action ˜ New ˜ Table from the SQL Server Enterprise Manager menu. In the table designer, enter the column name, the data type, and the data type characteristics for each column to match the IssueTracker table creation scripts as specified in Appendix A, "Building the IssueTracker Database."
Using the SQL Server Query Analyzer
The SQL Server Query Analyzer is another helpful database tool that provides an interface for executing SQL statements and viewing the results. This tool helps test SQL statements before you embed them within the application. Another approach to creating tables is to build SQL statements that build each table, as shown in Figure 2-6. Typically, applications provide database setup scripts with many lines of SQL instructions that build a database schema required by an application. You can customize these scripts for a target database platform that might offer features not available from other databases.
Figure 2-6: The SQL Server Query Analyzer
Creating Stored Procedures
Stored procedures are fast-executing routines that are coded and compiled on the database server. You can use stored procedures for all forms of data access, including select, insert, update, delete, and validation commands. Visual Studio .NET provides a helpful approach to quickly create stored procedures.
To create a stored procedure, begin by displaying the data access component in its design view. Right-click the sqlDataAdapter control and select Configure Data Adapter from its context menu. This launches the Data Adapter Configuration Wizard, as shown in Figure 2-7.
Figure 2-7: The Data Adapter Configuration Wizard start page
Click Next to advance from the wizard's start page. Next, create a connection to the database by clicking the New Connection button and entering the connection details in the Data Link Properties dialog box, as shown in Figure 2-8.
Figure 2-8: Data Link Properties dialog box used to create new database connections
Click Next to continue and click the Create New Stored Procedures radio button. Click Next to continue to the Generate Stored Procedures page. Click the Query Builder button to choose a table with which to work. In the Add Table dialog box, select only the table relating to the business object being worked on ”in this case, the Dat_Issue table ”and then click the Close button. The Query Builder window displays, as shown in Figure 2-9.
Figure 2-9: The ADO.NET Query Builder
Individually select each column name that will be used to fill the business object and click the OK button. For stored procedures relating to a single business object, enter a criteria field associated with the primary key column. In the case of the app_IssueSelect stored procedure, enter a criteria of = @IssueID . Before advancing, click the Advanced Options button, disable the Use Optimistic Concurrency and Refresh the DataSet options, and click the OK button. Click the Next button to display the Create the Stored Procedures page. It is important to enter the stored procedure names consistently in the naming convention specified by the data access framework:
app_[object][action]
Figure 2-10 illustrates the stored procedure naming convention as it is applied to the Issue object. From here, you can view the stored procedure creation scripts by clicking the Preview SQL Script button. The wizard also provides an option to automatically create the stored procedures within SQL Server. Click the Finish button to finalize the process.
Figure 2-10: Naming the auto-created stored procedures
The wizard produces the stored procedures shown in Listing 2-1 and may optionally create the stored procedures in the connected database.
 |
CREATE PROCEDURE [dbo].[app_IssueSelectAll] AS SET NOCOUNT ON; SELECT IssueID, TypeID, UserID, EntryDate, StatusID, Summary, Description, PriorityID FROM Dat_Issue GO CREATE PROCEDURE [dbo].[app_IssueSelect] ( @IssueID int ) AS SET NOCOUNT ON; SELECT IssueID, TypeID, UserID, EntryDate, StatusID, Summary, Description, PriorityID FROM Dat_Issue WHERE (IssueID = @IssueID); GO CREATE PROCEDURE [dbo].[app_IssueInsert] ( @IssueID int, @TypeID int, @UserID int, @EntryDate datetime, @StatusID int, @Summary char(128), @Description text, @PriorityID int, @ModifiedDate datetime ) AS SET NOCOUNT OFF; INSERT INTO Dat_Issue(IssueID, TypeID, UserID, EntryDate, StatusID, Summary, Description, PriorityID, ModifiedDate) VALUES (@IssueID, @TypeID, @UserID, @EntryDate, @StatusID, @Summary, @Description, @PriorityID, @ModifiedDate); GO CREATE PROCEDURE [dbo].[app_IssueUpdate] ( @IssueID int, @TypeID int, @UserID int, @EntryDate datetime, @StatusID int, @Summary char(128), @Description text, @PriorityID int, @ModifiedDate datetime, @Original_IssueID int, @Original_ModifiedDate datetime ) AS SET NOCOUNT OFF; UPDATE Dat_Issue SET IssueID = @IssueID, TypeID = @TypeID, UserID = @UserID, EntryDate = @EntryDate, StatusID = @StatusID, Summary = @Summary, Description = @Description, PriorityID = @PriorityID, ModifiedDate = @ModifiedDate WHERE (IssueID = @Original_IssueID) AND (ModifiedDate = @Original_ModifiedDate) ; IF @@ROWCOUNT=0 RAISERROR ('Optimistic concurrency failed.', 10, 1) GO CREATE PROCEDURE [dbo].[app_IssueDelete] ( @Original_IssueID int, @Original_ModifiedDate datetime ) AS SET NOCOUNT OFF; DELETE FROM Dat_Issue WHERE (IssueID = @Original_IssueID) AND (ModifiedDate = @Original_ModifiedDate) ; GO | |
The process of creating stored procedures can be time consuming. Four stored procedures should exist for every business object. In the IssueTracker application, this means you need four stored procedures for the Issue, User , and Company objects. You must create an additional stored procedure for each collection of objects as well.
You need to modify the stored procedures produced by Visual Studio .NET to better support concurrency. In any enterprise application, the possibility exists that a row of data will be accessed and possibly modified by more than one user. Adopting a concurrency model defines how the application reacts to such a situation. The three common models for handling concurrency are the last-in-wins , optimistic, and pessimistic models.
The least dependable approach to handling concurrency is to take no action. As multiple users view and edit the same record, the last user to update the row ends up with the stored values. This is known as a last-in-wins scenario because it does not matter which user begins editing row data first. The last user to update the row keeps the changes.
An alternative approach is to implement optimistic concurrency. Optimistic concurrency locks a row from other users while an update operation is in progress. If it is detected that an existing row has already been claimed for change by another object, then the update fails and the user is prompted to refresh the data. This approach adds a slight performance overhead for update operations but adds to the overall data integrity. Optimistic concurrency is the preferred model for managing concurrency within disconnected data.
Another alternative to handling concurrency is to completely lock the row from the moment the data is initially read, a process known as pessimistic concurrency . This adds a tremendous performance overhead because rows that may not be intended for updates still perform expensive record locks. If an application is not concerned about accommodating concurrent updates, then no action is necessary. If concurrency handling is necessary, then optimistic concurrency performs best for data that rarely encounters user conflicts during update and delete operations. Records that are often shared and updated concurrently by multiple users should rely upon pessimistic concurrency for better performance.
By default, DataSet objects handle optimistic concurrency by comparing all original row values to the values that currently exist within the database. If the values are the same, it is assumed that another user did not change the row and the new values may be committed to the database. In Listing 2-1, the app_IssueUpdate stored procedure has been modified to evaluate a time stamp, identified as ModifiedDate. The code that invokes this stored procedure supplies values that indicate when the record was originally read. If the time stamp of the row is more recent than that provided by the code, then the code is working with out-of-date data. In this case, the RAISERROR statement throws an exception.
| Note | Alternatively, another approach to managing concurrency is to replace the DateTime column with a Globally Unique Identifier (GUID) column. This permits the same row version comparison in the WHERE clause, but it offers the added performance benefit of applying a nonclustered index. |
You can handle optimistic concurrency violations in many ways. In most cases, the application notifies the user that their changes cannot be saved and offers to reload the relevant data. A rich user interface could also track the user interface form fields that changed and, if they are not affected by the reload, automatically reapply the user edits. You should consistently apply the approach you choose to all concurrency situations.
Setting Up Database Security
Because enterprise applications manage business data and services, security must be a part of the application from the beginning. In the data tier, there are three areas of security: stored procedures, Windows Authentication, and nonpersisted security information.
Working with Stored Procedures
Stored procedures are much more than compiled scripts that efficiently execute SQL statements. They are a first line of defense against intrusive attacks upon the application. Any time a SQL command is created dynamically based upon user input, it is at risk of being hijacked by a malicious user. The most common example is a simple query:
SELECT IssueID, Summary from Dat_Issue WHERE PriorityID = ?
Assuming that the question mark (?) identifies a user-inputted value, it is possible for a malicious user to enter a value that results in the following query instead:
SELECT IssueID, Summary from Dat_Issue WHERE PriorityID = 1; DROP Dat_Issue;
The result of the query would return the appropriate records and then immediately delete the entire Dat_Issue table. Limiting the user interface entry field helps, but accepting only parameterized values in a stored procedure is better. This is part of the reason that the data access framework relies upon stored procedures for all data management.
Working with Windows Authentication
SQL Server supports two different approaches to user authentication: Windows Authentication and Mixed Mode Authentication. By relying upon Windows Authentication, you avoid the need to build a connection string that embeds the database password.
Also, because typical enterprise data is read from significantly more than written to, you should create two separate accounts with separate permissions to read and write. This reduces the risk of damage if someone compromises the read-access password.
Avoiding Persisted Security Information
Set persist security info to false to avoid security-sensitive details, such as the password, from being returned by means of the ConnectionString property of the SqlConnection or OleDbConnection object. Append the following statement to the connection string:
persist security info=False;
Enabling Connection Pooling
When several users are connecting to a database, opening and closing a connection for each user can add overhead that eventually brings the database to a crawl. Connection pooling enables an application to work with a number of already-open connections that are passed between processes rather than constantly opened and closed. Most languages require extra code or a third-party library to enable connection pooling. However, with ADO.NET, the connection pooling is already in the Connection object. The following code implicitly creates a new connection pool when the Open method is invoked using a different connection string:
SqlConnection connection1 = new SqlConnection(); connection1.ConnectionString = "Initial Catalog=IssueTracker"; connection1.Open(); //connection pool 1 is created SqlConnection connection2 = new SqlConnection(); connection2.ConnectionString = "Initial Catalog=CustomerDatabase"; connection2.Open(); //connection pool 2 is created because the connection strings differ SqlConnection connection3 = new SqlConnection(); connection3.ConnectionString = "Initial Catalog=IssueTracker"; connection3.Open(); //connection comes from pool 1 because connection string matches
When a SqlConnection object requests a connection, it obtains that connection from the connection pool until the maximum pool size is reached. If the maximum pool size is reached, the SqlConnection object queues that request until the connection pool has an available connection. This is why it is important to always close a database connection. If several connections are made and the connection pool reaches its capacity, any remaining connections will be queued. Any queued connection requests that reach their Connect Timeout property value will return as an error. You can set the connection pool characteristics by appending to the connection string. To override the default connection pool settings and set a new maximum pool size of 250 connections, include the following connection string:
//example SqlConnection connection; connection = new SqlConnection( "user id=sa;password=;" + "Data Source=localhost;Initial Catalog=IssueTracker;" + "Max Pool Size=250"); connection.Open();
Using Transactions
Transactions are a series of commands that all need to be executed successfully for the whole series to be successful. If any of the commands within a series fails, then all of the commands within the series fail. You can accomplish transactions in ADO.NET with three methods : BeginTransaction, Commit, and RollBack. The following example creates a SqlTransaction object and assigns it to a SqlCommand object. One or more queries execute within the transaction, and the SqlTransaction object's Commit method commits all changes. If any exceptions are thrown within the try/catch block, the RollBack method undoes the changes:
SqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; try { command.CommandText = "UPDATE Dat_Issue SET StatusID = 0"; command.ExecuteNonQuery(); command.CommandText = "UPDATE Dat_Issue SET TypeID = 1"; command.ExecuteNonQuery(); transaction.Commit(); } catch( SqlException exception ) { transaction.RollBack(); } 
EAN: 2147483647
Pages: 119