Setting SQL Parameters for Stored Procs

[Roger Webb]

All the code I've seen for Parameterized stored procs seems to think that
the developer knows the parameter names at design time. I've got a
situation where I have a hashtable of possible parameters, and an unknown
stored proc name. Is there a way to Automatically create the Parameters
collection from the Database, then loop through them and set the parameters
appropriately?
- Roger

 

[Dumitru Sbenghe]

You must read the names of the parameters using a database dependent method
(I don't think is any ADO.NET standard way to do it)

For Sql server you can read this data using
- sp_sproc_columns
or
- using the views INFORMATION_SCHEMA.ROUTINES and
INFORMATION_SCHEMA.PARAMETER

Any database must have a method to read this information.

Dumitru Sbenghe

 

[David Browne]

All the code I've seen for Parameterized stored procs seems to think that
the developer knows the parameter names at design time. I've got a
situation where I have a hashtable of possible parameters, and an unknown
stored proc name. Is there a way to Automatically create the Parameters
collection from the Database, then loop through them and set the
parameters
appropriately?

How about SqlCommandBuilder.DeriveParameters?

David