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;
			}
		}
	}
}
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s