SqlCommandBuilder.DeriveParameters

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))
{
	conn.Open();
	using (var cmd = new SqlCommand("SpName", conn))
	{
		cmd.CommandType = CommandType.StoredProcedure;
		SqlCommandBuilder.DeriveParameters(cmd);
		
		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. View all posts by Adam Prescott