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:

var cs = ConfigurationManager.ConnectionStrings["NamedDbConnection"];
using (var conn = new SqlConnection(cs.ConnectionString))
	using (var cmd = new SqlCommand("SpName", conn))
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.Parameters["@SomeParameter"].Value = someValue;

		using (var reader = cmd.ExecuteReader())
			while (reader.Read())
				var col = reader["SomeColumn"] as string;

Author: Adam Prescott

I'm enthusiastic and passionate about creating intuitive, great-looking software. I strive to find the simplest solutions to complex problems, and I embrace agile principles and test-driven development.

One thought on “SqlCommandBuilder.DeriveParameters”

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: