How can I retrieve a list of parameters from a stored procedure in SQL Server

Solution 1:

You want the SqlCommandBuilder.DeriveParameters(SqlCommand) method. Note that it requires an additional round trip to the database, so it is a somewhat significant performance hit. You should consider caching the results.

An example call:

using (SqlConnection conn = new SqlConnection(CONNSTRING))
using (SqlCommand cmd = new SqlCommand("StoredProc", conn)) {
   cmd.CommandType = CommandType.StoredProcedure;
   SqlCommandBuilder.DeriveParameters(cmd);

   cmd.Parameters["param1"].Value = "12345";

   // ....
}

Solution 2:

You can use SqlCommandBuilder.DeriveParameters() (see SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials) or there's this way which isn't as elegant.