Yesterday I wrote a new class in the CSharpGears Framework. It is called ParameterExtractor and it's purpose is to discover the parameters of a given stored procedure and retrieve them in a collection. This class utilizes the method DeriveParameters in order to discover the parameters of the procedure and therefore, it can be only used with SQL Server unfortunately, but since SQL Server is quitte often a choice for database solution, this class might come in handy. The class also has caching capabilities, which can help saving performance since DeriveParameters method causes a roundtrip to the database, and you certainly don't want to have two accesses to the database for each query. Because of that, it uses static caching and keeps the list of parameters in a collection for further reuse. I have to admit that I am not certain about the thread safety of this operation, it is possible that a lock statement is required as a critical region.
My intention is to combine the ParameterExtractor and integrate it with my N-tier architecture, so that it will help me automate the parameter adding process from my business objects to the stored procedures. The framework already supports the reverse process, namely to map the datareader's columns into a list of business objects ( you can read my post about Object relational mapping if you are interested). I hope that soon my project architecture will contain clean, separated code which is also easy to write, due to the generics of C#.
However, one should be aware of the limitations of this class, it only works with SQL Server, and of course, it needs more memory to keep the stored procedure parameters, but its greatest adventage is that it is possible to write a code that will examine a business object and it will decide alone which attributes are needed to be mapped with which parameters - AUTOMATICALLY :).
"I am thinking that I am lazy..." - David Byrne
