Better the devil you know - Part III.
Roger Torres - December 18th, 2008
In Part II of this series I described the architecture and main components of the data access layer (DAL). While most of the design is based on ADO.NET, there are a couple of important differences:
- All the SQL code is defined inside a Visual Studio project as “code-behind” associated to the command classes. This is important to keep the SQL code close to the .NET data adapters (not only in the VS project but in the same source control system).
- Developers don’t need to deal with stored procedures directly since the framework will take care of all the deployment issues automatically. This is the desired behavior during the development phase when most security restrictions are normally removed. Developers are not forced to deal with the proliferation of stored procedures, naming guidelines, source code synchronization, and other headaches.
By default, all commands are executed in “Text” mode, which means the internal ADO.NET commands are created with the CommandType property set to CommandType.Text. Once the system is deployed to a production environment, and stored procedures are established as the only way to access the data, the execution mode of the connection object should be changed as follows:
cn.ExecutionMode = System.Data.CommandType.StoredProcedure;
At this juncture, all commands should be deployed as stored procedures to the underlying database. The connection class implements a deployment mechanism to assemble the stored procedures, taking care of the standard naming guidelines defined by the connection provider. Developers can choose to deploy (a) one command at a time, or (b) all commands defined inside an assembly dedicated to the DAL. Let’s see how to deploy the AdventureWorks data access layer using the second option:
cn.Deploy(typeof(AdventureWorks.DAL.AdventureWorksDbProvider).Assembly);
In general, the entire DAL should be deployed to a production or testing environment once the application is ready. Note that the current principal must have enough rights to create stored procedures.
EXECUTION METHODS
When developing a DAL using plain ADO.NET, developers must deal with repetitive patterns that are tedious and error prone. A regular pattern when executing a command would perform the following steps:
STEPS
- Declare input and/or output parameters.
- Set input parameter values.
- Execute command (using one of several execution methods implemented by ADO.NET - NonQuery, Scalar, DataReader, etc)
- Process results if the command returns a resultset.
- Get output parameter values.
Developers must also take care of other issues like handling exceptions, null values, transactions, and multiple commands executed in a single batch.
The main goal of the DAL execution methods is to encapsulate all this boilerplate code, but provide a flexible interface to cover the most common data operations. In general, all commands are executed by a connection instance following the same STEPS enumerated above. We can group the execution methods in four functional categories:
- Basic ADO.NET execution methods: These are the most basic command execution methods implemented by the framework. The only difference with ADO.NET is the generic interface to return strong typed results.
- Type readers: Methods used to read and materialize data into .NET objects or collections.
- Command binders: Methods used to bind .NET objects to commands before and after execution.
- Batch execution methods: Methods used to invoke multiple commands in a single database call, taking care of input/output parameter passing automatically.
The following table summarizes all the command execution methods, and how the framework invokes ADO.NET internally:
| Category | Execution Method | ADO.NET | Steps |
|---|---|---|---|
| Basic | Execute | ExecuteNonQuery | 1, 2, 3, 5 |
| Basic | ExecuteScalar<T> | ExecuteScalar | 1, 2, 3, 5 The framework uses generics to enforce a strong typed result. |
| Type Reader | ExecuteScalar<T>(ReaderCallback) | ExecuteReader | 1, 2, 3, 4 Uses callback delegate to materialize the first returned record into an instance of T. |
| Type Reader | ExecuteReader<T>(ReaderCallback) | ExecuteReader | 1, 2, 3, 4 Uses callback delegate to materialize all returned records into a list of instances of T, or a second option to materialize all returned records inside a target container object. |
| Type Reader | ExecuteReader<K, T> | ExecuteReader | 1, 2, 3, 4 Uses callback delegate to materialize all returned records into a dictionary of instances of T. |
| Command Binder | ExecuteBinder<T> | ExecuteNonQuery | 1, 2, 3, 5 Uses binder to dematerialize a single instance of T into the command’s properties before step 1, and update the instance back with the values of output parameters after step 5. |
| Batch | Execute(IEnumerable<Command>) | ExecuteReader | 1, 2, 3, 5 for each command in script The framework internally creates a SQL batch script that executes each command in the list as a stored procedure, taking care of input and output parameters automatically. |
| Batch and Command Binder | ExecuteBinder<T>(IEnumerable<T>) | ExecuteReader | Same as above but using binders to bind the list of objects to a single command. |
In Part II we described how to execute a basic command to insert employee records. Most of the data operations can be implemented by the basic methods, but we can exploit some common patterns and find a few shortcuts that can improve our coding experience while enforcing consistency and a more robust product.
In the next post I will explore the generic reader pattern.
Lots of great possibilities. Can’t wait for more especially when you open source the code. Big THANKS!
“At this juncture, all commands should be deployed as stored procedures to the underlying database.”
I think its a mistake to force developers to use either all stored procedures or all SQL as text. There will often be times, when using a production database, when a stored procedure solution is unacceptable. For instance, if you have some SQL which returns a query with a complex WHERE clause. Consider “SELECT * FROM Investor WHERE ConsultantID IN dbo.ConvertCSVToTable(@ConsultantIDList) AND …” Imagine that there are another 5 possible sub-clauses following the AND. Each sub-clause may or may not be invoked, depending on what a user is searching on. This kind of query is best done by dynamically building a string in C# (or VB.NET). The code is then invoked as a parameterized query. Each parameterized query with a distinct WHERE clause will have its execution plan cached by SQL Server (speeding up execution). I can’t see any (sensible) way of writing these complex queries as stored procedures without using dymanic SQL. If I use dynamic SQL the execution plan can be cached because each query will vary dynamically. By forcing people to use one or the other of all stored procedure or all text commands your DAL is imposing unacceptable contraints.
Sorry, I meant “If I use dynamic SQL no meaningful execution plan can be cached”
Despite my criticism, I think this DAL assembly looks good. Even if I have to write all my SQL as command texts in C#, it looks like a big improvement on what I’m currently using. I’ll definitely give it a go. Thanks for this.
Hi Mark
I understand your point, and that’s one of the reasons I created the batch execution mode. If you need to build sql dynamically, it’s cleaner to write a command for each statement and send the entire package in a batch. The dynamic sql is generated by the framework based on the individual commands. I think I have some examples in part 5 or 6 of the series. You can also deploy commands as stored procedures selectively (individual commands or DAL assemblies), it’s up to you how the DAL is partitioned into physical modules.
This framework is by no means a replacement to NHibernate, LINQ for Entities, or LINQ for SQL. This is just a simpler way to achieve similar (and sometimes better performing) results for the most common scenarios. Finally, the close control over all your sql code, closer to your C# code, and automatic deployment are a big plus in big projects when you have thousands of commands.
Thanks for your comments, and let me know if you have further questions or suggestions. I will be releasing the source code today or tomorrow.
Roger
I am a DBA + ASP.Net developer. SP is a must for larger application. For small application, does not matter, whenever the easiest to the developer himself..
SP can do the following while LINQ and Entity Framwork cannot do:
1. Assign a XML query plan to a particular stored procedure
2 Recursive SQL
3 Hireachy data type, geo support in SQL 2008
4 Full Text Search
5 SQL Bulk Copy using ADO.Net
6 XML update for hundreds of records at a time using OPEN XML, 100 times faster
7 DBA can modify the SP, some developers have very low SQL knowledge and this is dangerous for them to write dynamic SQL unless he submits all this dynamic SQL to a DBA for approval.
Good points Ben. This framework is able to use both, SQL Text commands and Sps, all based on the same CoNatural commands. I agree with you about the extra power of stored procedures. You can always assign the DAL tasks to your DBAs … so they can always maintain the SQL and C# (or VB.NET) wrapping commands in separate assemblies.