refreshing stored proc SqlParameters

Question

I need to write a method that receives a stored procedure name and returns an array of the SqlParameters for that specific procedure. I know about the sp_help system stored procedure in Sql Server but want this done through code. I found something that seems to do this in VB.NET : Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "sp_AdoTest" Cmd1.CommandType = adCmdStoredProc Cmd1.Parameters.Refresh But cant seem to find any equavalent in C# using SqlClient. Any kind of pointers would be appreciated.

denkai · Answer

The Microsoft data access application block contains functionality to derive the parameter set for a stored procedure from the actual procedure. It looks something like this: private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection string spName bool includeReturnValueParameter) { if( connection == null ) throw new ArgumentNullException( connection ); if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( spName ); SqlCommand cmd = new SqlCommand(spName connection); cmd.CommandType = CommandType.StoredProcedure; connection.Open(); SqlCommandBuilder.DeriveParameters(cmd); connection.Close(); SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; cmd.Parameters.CopyTo(discoveredParameters 0); // Init the parameters with a DBNull value foreach (SqlParameter discoveredParameter in discoveredParameters) { discoveredParameter.Value = DBNull.Value; } return discoveredParameters; } Good luck Dominique