Data Access Architecture guide - Part ii

Summary

• Proposal For Data Access Pattern
• Some Notes on Stored Procedures
• Server-Side Caching

Proposal For Data Access Pattern

Always think hard about your data access strategy. It can distinguish between successful and unsuccessful applications. 

How Stored Procedures are Usually Called in ADO.NET

In ADO.NET stored procedures are usually executed in a database (assume SQL Server) by creating a SqlCommand object, setting up its parameters, and then executing the query on the SqlCommand object. This approach suffers from the following limitations:

• Code is length. You will need code to prepare each parameter.
• The code is tightly coupled to ADO.NET. What happens if the syntax changes again? Remember DAO? RDO? RDS? ADO? ADO+? and finally ADO.NET?
• You cannot use pure T-SQL transactions instead of ADO.NET controlled transactions.

While the first two disadvantages can be handled with helpers, the last one requires a totally different approach.

Data Access Proposal

The main points of the data access proposal are:

• The main goal is to reduce network round-trips between business and data tiers by doing between these tiers at each round trip. Therefore, collect all information about all stored procedure calls that will execute together and build an SQL script that is executed in one round trip.
• Another main goal is how to use pure T-SQL transactions when a transaction has to span multiple stored procedure calls. Again this is handled in the SQL script.
• You have great debugging opportunity because the SQL script can be intercepted and tested in isolation (using Query Analyzer) against the database.
• The code is decoupled form ADO.NET  to better handle future changes.

The developer that write the code that generates the script doe not need to know about the internals of stored procedures called by the script. This is especially important if a stored procedure will be changed so that it needs a transaction even though it was not needed before. Once the script is generated, it is sent to the database in a single network round-trip and executed without any additional network round-trips.

External Design of the Data Access Proposal

The 'external design' means the part of the design that the consumer of the data access pattern will see and interact with. In other words, the external design refers to classes and their data/function members. As far as the consumer of the data access proposal knows, he/she must create an instance of BatchCommand and call AddStoredProcCall() one or more times to add a call to a stored procedure. The BatchCommand instance is then sent to paHelper class for executing all of the stored procedure calls.  A class diagram and a sequence diagram are given below:

Note that the data access proposal is similar to the Command Processor design pattern in Pattern-Oriented Software Architecture. The Command Processor pattern is in turn closely related to the Command Pattern.

Some Notes on Stored Procedures

In some situations, sending several rows in one stored procedure call can be very useful. Here are a few techniques that can be used

XML

One possible way to benefit from the built-in support for XML in SQL Server 2000 is to use stored procedures to parse XML documents. With this support, you can send an XML document, which would contain many rows, as a parameter to a stored procedure. From within the stored procedure, use OPENXML to fin information in the XML document.

Other possible formats for sending data include bitmaps and User-Defined Types (UDT). However, XML is often the preferred format to use.

Data Transportation Between Stored Procedures

When one stored procedure needs to call another, XML can also be used, but there are also other alternatives.

• Temp Tables and Cursors
  Create a temp table and send its name to be used in the called stored procedure. Another solution is to create a cursor and return it for example to the caller. The caller can then continue using that cursor without knowing the WHERE clause for the cursor.

CREATE PROCEDURE SampleReturnCursor @Cur CURSOR VARYING OUTPUT
AS
    DECLARE aCursor CURSOR LOCAL FOR
    SELECT * FROM TAble

    OPEN aCursor
    SET @Cur = aCursor

    DEALLOCATE aCursor

• UDF
  Often a called stored procedure that creates a result set for use by the caller can be rewritten as an Inline Table Valued Function (ITVF) or Multistatement Table Valued Function (MTVF). In this case you do not have the problem of caching the rows and putting them somewhere else where the caller can have access to them.. This is because the UDF can be used in SELECT statement just as a table or view.

Server-Side Caching

Server-side caching refers to maintain some frequently-accessed information in the memory of the application server. Accessing this data is much faster since a trip to the database is no longer required. The main problem is keeping the cache in sync with the database. This is more difficult if you have scaled out the application - that is, set up a farm of application server each running the same application. In this case, only cache static data, otherwise you risk the main goal of the application - correctness.

it is recommended that caching be skipped unless it is really needed. Going to the database is often quick enough, is simple, and has fewer risks.

Server-Side Caching in .NET

With .NET, two techniques open up the potential for server-side caching, one being the use of COM+ component service object pooling, and the other being the use of ADO.NET DataSets for carrying complex data, for example, in pooled objects.