views:

477

answers:

5

Is there a sane way to get a list of expected parameters from an SqlDataSource given the name of the stored procedure assigned to its SelectCommand property?

A: 

I dont have VS.NET to try it out, but .Prepare() may do this for you :)

Or you could just add the parameters. :)

Nic Wise
A: 

I am not sure if you can do it in the ADO.NET environment directly, somehow - but you could always query the "sys" system views to get that information:

select * from sys.parameters
where object_id in 
      (select object_id from sys.procedures where name = 'YourSProc')

Marc

marc_s
A: 

The designer tries to do this for you. Did it fail? Is there a reason you can't do it at design time?

John Saunders
+8  A: 

You can use the SqlCommandBuilder.DeriveParameters method to return information on the parameters associated with a stored procedure. Iterating over the collection will allow you to determine the parameter name, data type, direction, etc.

SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection("myConnectionString");
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "myStoredProcName";
cmd.Connection = conn;

conn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
conn.Close();

SqlParameterCollection collection = cmd.Parameters;

Take a look at the SqlParameterCollection and SqlParameter class definitions for more information.

sgriffinusa
excellent - thanks, that's very helpful to know.
marc_s
A: 

And also with:

EXEC sp_sproc_columns 'YourSProc'