Tag Archives: stored procedure

Last week, I wrote a short article about the SqlCommandBuilder.DeriveParameters method that can be used to automatically populate the parameters of a stored procedure command. One of the downsides to this approach is that each call to the method will result in a call to the database. Since stored procedure parameters aren’t typically changing very often, this can result in a lot of unnecessary chatter. So what’s the solution? Cache the parameter collection, of course!

Here’s a very simple way to do just that. I have an abstract class that maintains a static dictionary of SqlParameterCollections, indexed by SQL command. When the static DeriveParameters method is called from within derived classes, the dictionary is checked. If a matching parameter collection is found, it will be used to create a new parameter collection using the existing collection as a blueprint. If no collection is found, SqlCommandBuilder.DeriveParameters is used, and the resulting collection is cached.

Note that caching stored procedure parameters can have a negative side-effect: if the parameters DO change, your cache may need to be refreshed. This could be handled in a number of ways. You could have the cached parameters expire after a certain amount of time, you could have a manual trigger, or you could build logic into your application that automatically refreshes the cache if the appropriate error condition is detected.

Update 10/10/2012:

The SqlParameter class also implements ICloneable. I’ve updated my caching logic to take advantage of this. Note that you must cast the parameter to ICloneable in order to invoke its Clone method.

Share this:

Like this:

One of the first things I learned when getting into .NET was how to access a SQL database. Most of the data access I need to do is stored procedure-based, so I did this by using the Enterprise Library Data Access Block. It was magical, and it worked, so I never asked questions.

The main thing that’s kept me from deviating is the DiscoverParameters method. We use so many stored procedures, and many of them have a large number of parameters. Manually creating parameters in code was just not an option. Today I learned about a fantastic new method that has liberated me, though: SqlCommandBuilder.DeriveParameters.

This handy little method gives me the same benefit of automatically populating a stored procedure command’s SqlParametersCollection. Here’s an example:

Share this:

Like this:

Database stored procedure calls are one of the trickiest things to unit test, and there are many different approaches that can be taken. My team has run the gamut: test DBs that rollback with each run, no testing for direct data access functions (!), virtual functions w/ partial mocks (see here).

The latest approach that I’ve been using is much more straightforward and feels like a more natural use of Rhino Mocks. Let’s look at some examples of how to test some common database stored procedure tasks. (Note that these examples assume use of the Microsoft Enterprise Library.)

Create a mockable Database

The primary challenge that I’ve found with testing database code is that Microsoft.Practices.EnterpriseLibrary.Data.Database isn’t mock-friendly. However, the other “pieces” such as DbCommand and DbCommandParameterCollection are very easy to work with. So, we can solve the Database problem by creating a simple wrapper (Important! Note that the methods have the virtual keyword, which will allow them to be overridden.):

Executing a stored procedure

Now that we are able to mock the database object, we can write some useful tests. Let’s say you want to execute a stored procedure named “MyStoredProcedure,” and you want to write a test to verify that your code handles an exception thrown when it’s executed. That’s very easy!

Share this:

Like this:

Hi, I’m Adam. This is my blog.

I started this blog as a personal searchable repository of things I've learned and figured out. It evolved into a mechanism to facilitate personal growth, and now it's turned into a bit of a hobby. I enjoy writing about software development, my professional life, and related topics.

I love to hear from readers, so please feel encouraged to leave comments on anything you read here.

Follow Blog

Enter your email address to follow this blog and receive notifications of new posts by email.