Few week ago, I have blogged about wrapper around SPROC calling routine. At that moment I thought, about this, only like a sort of a syntax-sugar around “low level” data access classes.
Yesterday, I have faced a requirement, to verify and revalidate all my stored procedures signatures over new database. The task tended to be mundane and useless, but thanks to introduced abstraction (that builder), we can create a test, that will “fake” a stored procedure builder with a recording-mock, record all procedure calls and it’s parameters and verify it’s signatures against target database.
Here, how it works:
- Tests replace CommandBuilderFactory, with Factory, that produces recording builders.
- Test executes All possible methods of each Data Gateway, to record called procedures and parameters.
- Each procedure is verified, against it’s SQL declaration.
Recording builder, in my case, allows a gateway to fill-in all required parameters. When builder is requested to do “build-up”, to produce an executor in my case, it just throws special exception. This type exception is excepted by caller.
Great, now we have a list of procedures, it’s parameters and parameter types. Next task is to check parameters of each procedure against procedure in SQL. ADO.net has method “SqlCommandBuilder.DeriveParameters”, which populates a SPROC call with it’s parameters.
Viola, the test is ready, and can be executed on continuous basis, to make shore, that nobody will commit wrong SPROC declaration anymore.
So, the lesson that I got, is, that abstraction over BCL classes has a hi chance to pay off to you :)
