public static MethodParameterMap MapToParameterColumnMap(ListViewItem aItem, MapedObject aMap, String methodName, int parameterCount, bool ruleBased, Guid id)
{
ParameterColumnMap aPair = new ParameterColumnMap();
if (!ruleBased)
{
aPair.ColumnName = aItem.SubItems[0].Text;
}
else
{
aPair.ColumnName = aItem.SubItems[1].Text;
}
aPair.RuleBased = ruleBased;
aPair.ParameterName = aItem.SubItems[1].Text;
aPair.DataType = aItem.SubItems[2].Text;
aPair.IsNullable = bool.Parse(aItem.SubItems[4].Text);
return(AddParameterMap(aMap, aPair, methodName, id, parameterCount));
}
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());
}
private static SqlParameter CreateParameter(SqlDataReader reader, ParameterColumnMap columnMap)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = reader.GetString(columnMap.ParamName);
int managedTypeId = reader.GetInt16(columnMap.ParamDataTypeNum);
parameter.SqlDbType = GetSqlDbTypeEnumFromManagedTypeId(managedTypeId);
string sqlDbTypeName = reader.GetString(columnMap.TypeName);
if (sqlDbTypeName == null) // Fixes unit test issue where simple UDT's have a null type (Insight fixes the null later)
{
parameter.SqlDbType = SqlDbType.Udt; // Set the type to UDT so insight knows its a UDT
}
parameter.Direction = GetParameterDirection(reader.GetInt16(columnMap.ParamDirType));
object charLengthObj = reader[columnMap.Length];
if (charLengthObj is int)
{
int charLength = (int)charLengthObj;
// Sql sometimes returns zero rather than -1, so fix it (it failed output parameters unit tests)
if (charLength == 0 && (parameter.SqlDbType == SqlDbType.NVarChar || parameter.SqlDbType == SqlDbType.VarBinary ||
parameter.SqlDbType == SqlDbType.VarChar))
{
charLength = -1;
}
parameter.Size = charLength;
}
if (parameter.SqlDbType == SqlDbType.Decimal)
{
parameter.Scale = (byte)reader.GetInt16(columnMap.Scale);
parameter.Precision = (byte)reader.GetInt16(columnMap.Precision);
}
if (parameter.SqlDbType == SqlDbType.Structured)
{
parameter.TypeName = reader.GetString(columnMap.TypeCatalogName) + "." +
reader.GetString(columnMap.TypeSchemaName) + "." +
reader.GetString(columnMap.TypeName);
}
#if !NO_UDT
if (parameter.SqlDbType == SqlDbType.Udt)
{
parameter.UdtTypeName = reader.GetString(columnMap.TypeCatalogName) + "." +
reader.GetString(columnMap.TypeSchemaName) + "." +
reader.GetString(columnMap.TypeName);
}
#endif
if (parameter.SqlDbType == SqlDbType.Xml)
{
parameter.XmlSchemaCollectionDatabase = ReadDbNullStringSafely(reader, columnMap.XmlCatalogName);
parameter.XmlSchemaCollectionOwningSchema = ReadDbNullStringSafely(reader, columnMap.XmlSchemaName);
parameter.XmlSchemaCollectionName = ReadDbNullStringSafely(reader, columnMap.XmlCollectionName);
}
if ((parameter.SqlDbType == SqlDbType.DateTime2 || parameter.SqlDbType == SqlDbType.Time ||
parameter.SqlDbType == SqlDbType.DateTimeOffset))
{
object value = reader.GetValue(columnMap.DateTimePrecision);
if (value is int)
{
parameter.Scale = (byte)(((int)value) & 0xff);
}
}
return(parameter);
}
private void AddToMapList(ParameterColumnMap aMap)
{
var newPair = new ListViewItem(new string[] { aMap.ColumnName, aMap.ParameterName, aMap.DataType, aMap.IsNullable.ToString(), aMap.IsNullable.ToString() });
lstMappedPairs.Items.Add(newPair);
}
