/// <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()); }