Welcome to AspAdvice Sign in | Join | Help

AzamSharp

Some day I will know everything I hope that day never comes

Syndication

Tags

Navigation

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

Sponsor

Published Friday, November 03, 2006 9:14 PM by azamsharp

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# Retrieve sproc parameters with SqlCommandBuilder.DeriveParameters Meth @ Sunday, November 05, 2006 10:24 AM

You've been kicked (a good thing) - Trackback from DotNetKicks.com

DotNetKicks.com

Leave a Comment

(required) 
required 
(required) 
Enter the code you see below