/// <summary>
/// The the stored procedure parameter parameters for the proc specified in the command
/// </summary>
/// <param name="cmdToPopulate">The command containing the stored procedure</param>
/// <returns>A SqlDataReader containing the parameters.</returns>
private static SqlDataReader GetParametersAsReader(SqlCommand cmdToPopulate)
{
var procToPopulateName = new SqlObjectName(cmdToPopulate.CommandText);
var parameterRequestProc = new SqlObjectName(procToPopulateName.Database, "sys", "sp_procedure_params_100_managed");
var parameterRequestCmd = new SqlCommand(parameterRequestProc.Name, cmdToPopulate.Connection, cmdToPopulate.Transaction);
parameterRequestCmd.CommandType = CommandType.StoredProcedure;
parameterRequestCmd.Parameters.Add(CreateNVarCharParameter("@procedure_name", procToPopulateName.Name));
if (!procToPopulateName.Schema.IsNullOrWhiteSpace())
{
parameterRequestCmd.Parameters.Add(CreateNVarCharParameter("@procedure_schema", procToPopulateName.Schema));
}
SqlDataReader parameterReader = parameterRequestCmd.ExecuteReader();
return(parameterReader);
}
public static void DeriveParameters(SqlCommand cmdToPopulate)
{
AssertCommandIsValid(cmdToPopulate, "DeriveParameters");
AssertConnectionIsValid(cmdToPopulate);
var newParameters = new List <SqlParameter>();
var procToPopulateName = new SqlObjectName(cmdToPopulate.CommandText);
var parameterRequestProc = new SqlObjectName(procToPopulateName.Database, "sys", "sp_procedure_params_100_managed");
var parameterRequestCmd = new SqlCommand(parameterRequestProc.Name, cmdToPopulate.Connection, cmdToPopulate.Transaction);
parameterRequestCmd.CommandType = CommandType.StoredProcedure;
parameterRequestCmd.Parameters.Add(CreateNVarCharParameter("@procedure_name", procToPopulateName.Name));
if (!procToPopulateName.Schema.IsNullOrWhiteSpace())
{
parameterRequestCmd.Parameters.Add(CreateNVarCharParameter("@procedure_schema", procToPopulateName.Schema));
}
using (SqlDataReader parameterReader = parameterRequestCmd.ExecuteReader())
{
var columnMap = new ParameterColumnMap(parameterReader);
while (parameterReader.Read())
{
SqlParameter parameter = CreateParameter(parameterReader, columnMap);
newParameters.Add(parameter);
}
}
if (newParameters.Count == 0)
{
throw new InvalidOperationException($"The stored procedure '{cmdToPopulate.CommandText}' doesn't exist.");
}
cmdToPopulate.Parameters.Clear();
cmdToPopulate.Parameters.AddRange(newParameters.ToArray());
}
