Marketing Ninja

I’ve had my nose stuck in a terrific ASP.NET book for the past few days and it inspired me to re-write the data layer for my entire Facebook application in ASP.NET. (I’ll tell you about the book at the end of the post.)

We’re going to learn how to construct a very flexible data layer that works to save us overhead by dynamically rolling multiple SQL commands into a single SQL connection instance, protects us against SQL injection attacks, and is very easy to modify down the road.

The Three Layers of an ASP.NET Application

In any beginners’ level ASP.NET book you are introduced to the three layers of an ASP.NET application: the presentation layer, the business layer, and the data layer. While this article focuses specifically on designing and implementing a great data layer in ASP.NET, let’s go over the three layers real quickly.

• The Data Layer is responsible for communicating with the database, whether it is a flat-file, a SQL DBMS, or a large XML document, and performing all database operations relevant to the input from a user in a specific instance. If a user needs to add some new data, the data layer carries that out, and if a user needs to view some data, the data layer carries that out also.
• The Business Layer acts as a go-between for the presentation layer and the data layer; the data layer merely performs operations directly onto the database, whereas the business layer transforms information from the presentation layer into commands that the data layer understands. For instance, if a user submitted some form data on a search form, the business layer would translate that POST or GET request into the appropriate function or object call on the data layer, which the data layer would then render into a SQL request. The vice-versa is also true; the business layer is used to transform the results of a SQL execution command into an object like a DataSet which can then be translated by the presentation layer into a graphical display for the human user.
• The Presentation Layer is what the human user sees when he or she uses the service; it’s all of the graphical output rendered by every ASP.NET control and XHTML markup.

My Old School Data Layer

First we’re going to look at how I originally designed my data layer and we’ll cover some of the flaws inherent to its design. My old data layer had the following characteristics:

• All data operations were divided up into classes based on business-layer relevancy; user-related operations were contained in UserDB.cs class, statistics were contained in a StatsDB.cs class and so forth.
• Every SQL stored procedure was represented by a specific method in the data layer objects.
• Every method was atomic; at the start of each method call a SQL connection would be established, parameters passed by the business layer would be supplied to the SqlCommand object, the command would execute, error trapping would ensue, and the connection would close after the results were returned.
• All information about the SQL connection itself was read from a base class that pulled data from the web.config connections region.

Here is a quick code sample of what one of my methods looked like:
public bool AddUserToNetwork(SnapUser user, String FBID)
{
bool returnValue = true;
SqlConnection con = new SqlConnection(this.connectionString);
SqlCommand cmd = new SqlCommand("Snap_AddUserNetwork");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int));
cmd.Parameters["@UserID"].Value = user.UserID;
cmd.Parameters.Add(new SqlParameter("@FBID", SqlDbType.NVarChar, 256));
cmd.Parameters["@FBID"].Value = FBID;
try
{
con.Open(); //Open the sql connection
cmd.ExecuteNonQuery();
user.UserID = (int)cmd.Parameters["@UserID"].Value;
}
catch (SqlException err)
{
returnValue = false;
SError error = new SError("UserDB.cs",
err.Procedure, FBID, (short)err.LineNumber, err.Message);
this.errorLogger.logError(error); //Log this error
}
finally
{
con.Close(); //Clean up
}
return returnValue; //Return whether the operation failed or not
}

(Click for a larger image)

All of my methods looked like this in one way or another; here are some of the strengths of this design:

• Robust – Once the business layer objects are passed to the data layer, there is very little chance of this application breaking. Given the way I designed my SQL stored procedures, which are not visible here, there is very little chance for this application to throw an error.
• Secure - This approach, using the SQLCommand object and the SqlParameters collection, is as utterly impenetrable to SQL injection attacks as possible.
• Independent - This method does not depend on any start-up or initialize methods for shared resource allocation, such as opening up a shared SQL connection, thus these methods can be called any place, any time.

As I discovered down the road, however, there are some significant drawbacks to this style of wrapping all SQL transactions into their own independent units of execution:

• Inflexible – If I needed to add a user to more than one network I would have to either call this function more than once or redesign the function so it can potentially accept multiple users; but since the EXEC command in SQL isn’t designed to handle multiple data sets at once, redesigning this function to accept multiple users would require an entirely new approach.
• Inefficient - If I have to make multiple SQL calls at once on any given page, I am going to be opening up multiple connections, which is probably one of the worst things you can do when you’re designing for scalability.
  

  The greatest overhead in any data-driven application is typically establishing a connection to the database.

• Difficult to Maintain – As I mentioned in the inflexibility section, rewriting this code for new uses would be difficult as I would need to write a new stored procedure at the database level in order to be consistent with my design. Either have all of your queries be written as a SQL stored procedure or have all of them embedded into your ASP.NET as parameterized queries; don’t do a bit of both.

The New and Improved Data Layer

So after reading this book I mentioned earlier (I will mention the book at the end of the article), I came across a section in it which described a very simple data layer that the author designed for the sake of creating a few specialized Web Parts.

At the time when I read it I was reminded of the drawbacks with my first design that I was experiencing and it occurred to me that THIS was exactly what I was looking for. I’ll go ahead and describe it to you.

• Rather than incorporate a SQL stored procedure execution into a single function with all resource allocation performed locally, have all SQL stored procedure calls be written as parameterized SQL in a static snapSQL.cs class.
• Have all of my custom data types (such as the SnapUser data type in my previous code sample) inherit from a custom Business Entity interface so they can be used to populate a SqlReader for use in a Repeater or DataGrid seamlessly.
• Define a generic SQL execution module which opens up a connection and executes handful of SnapQuery data structures used for representing SQL queries. Each snap query contains:
  1. A string containing any number of parameterized SQL queries
  2. A collection of strings representing the names of the parameters
  3. A collection of strings containing the values of the parameters

  Given that this single instance of my “SQL Execute” class can rattle off multiple SQL transactions under a single shared connection and still keep the connection open only for as long as needed.

• Transform all SQL output into collections of data types which implement the IBusinessEntity interface.

Now here’s my code sample where I put it all together into a single .CS file. I did not include any glue code that can be used to make this work, just all of the individual components of the data layer that I mentioned above. If you have any questions please post them here.

AjaxNinja – SQL Data Layer Code Sample

The Mystery Book

The prestige. The book I’ve been nose-deep in for the past few days is ASP.NET 2.0 Web Parts in Action by Darren Neimke and if you look at my code sample you’ll see a ton of similarities between my code and his examples on pages 26 through 29. I haven’t finished reading the book yet and I plan on publishing a full review, but let me say that the little revelation I had when I read Darren’s simple data layer made the $30 I chipped out on the book more than worth it. While the book focuses primarily on Web Parts, the keystone of Microsoft’s new portal framework for ASP.NET 2.0, it has proven to be very informative for ASP.NET design in general.

Disclaimer: If you click on the links for this book and subsequently purchase it, I will receive a small commission from Amazon.com. I only refer products that I actually use and think are worthwhile.

If you enjoyed this post, make sure you subscribe to my RSS feed!