Get Stored Procedure Parameters Using SqlCommandBuilder.DeriveParameters Method
Are you tired of attaching the parameters using the following code:
myCommand.Parameters.AddWithValue("@UserID",userID);
myCommand.Parameters.AddWithValue("@CategoryID",categoryID);
and so on.....
Well, you can use the SqlCommandBuilder.DeriveParameters method to get the parameters from the stored procedure you are refering to.
private
static void foo(
params object[] parameterValues)
{
int index = 0;
using (SqlConnection myConnection = new SqlConnection("Server=localhost;Database=School;Trusted_Connection=true"))
{
SqlCommand myCommand = new SqlCommand("usp_GetStudentExams",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
/*
* Since SqlCommandBuilder.DeriveParameters makes a call to the database to get the parameters you can
* cache the parameters.
* */
SqlCommandBuilder.DeriveParameters(myCommand);
foreach (SqlParameter parameter in myCommand.Parameters)
{
if (parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.InputOutput)
{
parameter.Value = parameterValues[index];
index++;
}
}
SqlDataReader dr = myCommand.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr["FirstName"] as String);
Console.WriteLine(dr["LastName"] as String);
Console.WriteLine(dr["Title"] as String);
}
}
}
For more information check out the msdn link: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder(VS.80).aspx