SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials
by David Hayden ( ASP.NET C# Sql Server Developer )
Filed: ADO.NET Tutorials
Last night I was updating a DatabaseHelper Library that I use for small .NET consulting projects. It shares the same interface as Enterprise Library 2.0 DAAB, but without all the bulk. It is a “same taste, less filling” kind of library optimized for Sql Server Database Access that can easily be replaced by the Data Access Appliction Block with basically no code changes should the need arise.
There are a few methods I hadn't got around to implementing yet since I rarely use them. One is the family of method overloads that take an object array of parameter values as such:
IDataReader ExecuteReader(string storedProcedureName,
params object[] parameterValues)
The idea here is that you want to call a stored procedure that takes some parameters, but you want the convenience of just passing in an object array of parameter values as opposed to creating all the parameters manually. The developer is “asking” the library to “discover“ the proper parameters and assign the values accordingly.
The main reason I don't normally use this overload is because it requires an extra roundtrip to the database by the library to discover the parameters. It is convenient, but not as optimized performance-wise. As an added level of complexity, the DAAB has a parameter cache to cache the parameters discovered on the first call to the stored procedure. This saves you the performance hit on subsequent calls, but just adds complexity to the library as well as cache management.
SqlCommandBuilder.DeriveParameters
The magical bit of code that discovers stored procedure parameters in the background is the SqlCommandBuilder.DeriveParameters method. You call the method passing in an SqlCommand object as such:
SqlCommandBuilder.DeriveParameters(command);
and it will populate the SqlParametersCollection on the command for you. Only thing you need to do is populate the input parameters with their appropriate values and execute the query.
SqlCommandBuilder.DeriveParameters Example
Here is a first cut at what the code might look like to use this functionality:
private IDataReader ExecuteReader(string storedProcedureName,
params object[] parameterValues)
{
SqlConnection connection = null;
try
{
connection = CreateConnection();
// Create Command
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
// Open Connection
connection.Open();
// Discover Parameters for Stored Procedure
// Populate command.Parameters Collection.
// Causes Rountrip to Database.
SqlCommandBuilder.DeriveParameters(command);
// Initialize Index of parameterValues Array
int index = 0;
// Populate the Input Parameters With Values Provided
foreach (SqlParameter parameter in command.Parameters)
{
if (parameter.Direction == ParameterDirection.Input ||
parameter.Direction == ParameterDirection.
InputOutput)
{
parameter.Value = parameterValues[index];
index++;
}
}
return command.ExecuteReader(CommandBehavior.
CloseConnection);
}
catch
{
if (connection != null && connection.State
== ConnectionState.Open)
connection.Close();
throw;
}
}
You could add some caching here of the parameters so the parameter discovery only happens once.
The client code calling the method may look like:
// Call GetBlogsByBlogId Stored Procedure
// BlogId = 1
using (IDataReader dr = ExecuteReader("GetBlogByBlogId", 1))
{
Blog blog = null;
if (dr.Read())
{
BlogFactory factory = new BlogFactory();
blog = factory.Construct(dr);
}
}
Conclusion
Next time you want to find the parameters used by a stored procedure, check out the use of SqlCommandBuilder.DeriveParameters.
Source: David Hayden ( ASP.NET C# Sql Server Developer )
Filed: ADO.NET Tutorials