Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Data Access for Partially Connected Applications : Page 6






The Hidden Value of Stored Procedures
For my next architectural trick: stored procedures. Stored procedures have a lot of advantages, from security to performance. I do not want to engage in a discussion about the general advantages and disadvantages of stored procedures, as such a discussion really has nothing to do with the subject of this article. However, there is one aspect of stored procedures that is often overlooked, yet is of incredible importance to the data access infrastructure: stored procedures are the ultimate abstraction of data access. Using stored procedures, you can easily port your application to use any type of database as the back end.

Some readers may wonder how that would work. After all, many databases and data sources do not support stored procedures at all. Nevertheless, I stick to my statement. Here's why. Developers often think of stored procedures as snippets of code that live inside the database. However, at a more abstract level, stored procedures are simply little programs with a certain signature (procedure name and parameter names and types) that sit between the client application and the database. From an architectural point of view, the fact that stored procedures live inside SQL Server is irrelevant. And if you happen to be talking to a data source that does not support stored procedures, you can always reproduce the same concept yourself using standard .NET code.

Stored Procedures are the ultimate abstraction of data access.
Consider a simple example: Let's say you want to query all customers from a database whose name starts with A. Without stored procedures, you could use the infrastructure like so:

IDataService svc = DataServiceFactory.GetDataService(); IDbCommand cmd = svc.GetCommandObject( "SELECT * FROM Customers WHERE Name = @Name"); svc.AddCommandParameter(cmd,"@Name","Egger"); DataSet ds = svc.ExecuteQuery(ds,"Customers");

In this case, the select statement is sent to the database server as specified. Using stored procedures, the procedure has a name and receives a single parameter. The procedure then executes the SQL statement internally and returns the result set. You can call such as procedure in the following fashion:

IDataService svc = DataServiceFactory.GetDataService(); IDbCommand cmd = svc.GetCommandObject("GetNames"); cmd.CommandType = CommandType.StoredProcedure; svc.AddCommandParameter(cmd,"@Name","Egger"); DataSet ds = svc.ExecuteQuery(ds,"Customers");

Both approaches work fine as long as the data service served up by the service factory links you to SQL Server. If you were to switch to a completely different data storage system (let's use XML files as an example), you would have problems in either case. In the first case, you would have to map the SELECT command to something you can execute on an XML file. Although not impossible, this task would be extremely difficult. You would have to create an engine that can parse T-SQL syntax and then find a way to apply the operations to XML, which is difficult because XML follows a completely different paradigm. I do not think that it is feasible to implement such a solution.

The stored procedure approach also will not work out of the box, because XML certainly does not support the concept of stored procedures. However, you can create a solution relatively easily. All you have to do is create a piece of code named GetNames with a single parameter, and then call that code from an XML data service. That little snippet simply needs to load the XML file, apply an XPath query (or any other sort of XML processing), and return the result in a fashion compatible to the SQL Server stored procedure. With ADO.NET and System.XML, this task is surprisingly easy.

The approach I want to demonstrate accomplishes this task very elegantly (in my opinion). Rather than adding all such methods to an XML data service manually, I like to use an object I call a stored procedure façade. This is an object that can act very similar to a system that supports stored procedures natively. The idea is to create a simple .NET object with methods that can be treated like stored procedures, even though they are just standard methods. The goal is to provide a simple way to execute those methods without having to hard code them into the data service. This task is accomplished by a method called ExecuteFakeQuery(), which is defined on an abstract StoredProcFacade class. Developers can derive from that class, add their own methods, and then execute them by passing a standard IDbCommand object (like the one you created in the second example above) to the ExecuteFakeQuery() method.

The key to implementing such a façade object lies in .NET Reflection. Reflection allows you to inspect objects and find out what methods and properties they have. Reflection also allows you to execute these methods dynamically. Using these capabilities, you can inspect the command object passed to the ExecuteFakeQuery() method, match it up with a method on the façade object, execute that method dynamically, and return the result as if it came from a stored procedure in SQL Server. Listing 5 shows the implementation of the base façade class.

Now that you have the tricky part wrapped up in a reusable class, you can subclass it and add the GetNames() method that the query is expecting to use. Listing 6 shows an implementation of that method that is purposely kept rather wild to demonstrate how different the method can be from normal data access. (This method assumes an XML file as it would have been created by the naïve offline approach shown above.)

Now all that's missing is the ExecuteQuery() method in a new XML data service. The implementation of that method is so simple it only requires a few lines of code:

public DataSet ExecuteQuery(IDbCommand cmd, string resultName) { if (cmd.CommandType != CommandType.StoredProcedure) { throw new Exception("Not a stored proc!"); } StoredProcFacade f = new CustomerStoredProcedures(); return f.ExecuteFakeQuery((SqlCommand)cmd); }

Of course, this code could be easily enhanced by allowing for an external configuration option specifying which stored procedure façade to use.

Voila! You have now ported the application to work with XML as a database through the use of stored procedures. Note that in many scenarios, the fake stored procedures can be mapped to highly performing database access, resulting in implementations far superior to the XML example. Also, the implementation of these stored procedures (fake or real) is pretty straightforward, especially as many of them can usually be code generated. This is especially true for CRUD (insert, update, delete) stored procedures that deal with insert, update, and delete operations.

Note that another benefit you get from this approach is that the stored procedure (fake or real) can map data from a structurally incompatible data source to the expected result format.

Comment and Contribute








