private Dictionary <string, List <StoredProcedureProperty> > GetParameters(SqlConnection conn)
{
Dictionary <string, List <StoredProcedureProperty> > results =
new Dictionary <string, List <StoredProcedureProperty> >();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"
SELECT
ProcedureName = pr.Name,
ParameterName = p.Name,
TypeName = ty.name,
HasDefaultValue = p.has_default_value,
DefaultValue = p.default_value,
IsOutputParam = p.is_output,
IsTableType = ty.is_table_type,
IsUserDefined = ty.is_user_defined,
UserTypeId = ty.user_type_id
FROM
sys.parameters p
INNER JOIN
sys.procedures pr ON p.object_id = pr.object_id
INNER JOIN
sys.types ty ON p.user_type_id = ty.user_type_id
ORDER BY
p.Name, p.parameter_id" ;
command.CommandType = System.Data.CommandType.Text;
var reader = command.ExecuteReader();
while (reader.NextResult())
{
string procName = reader.GetString(0);
string paramName = reader.GetString(1);
string typeName = reader.GetString(2);
bool isOutput = reader.GetBoolean(5);
bool isTable = reader.GetBoolean(6);
bool isUserDefined = reader.GetBoolean(7);
int userTypeId = reader.GetInt32(8);
if (!results.TryGetValue(procName, out List <StoredProcedureProperty> props))
{
props = new List <StoredProcedureProperty>();
results.Add(procName, props);
}
props.Add(new StoredProcedureProperty()
{
IsOutput = isOutput,
IsTableType = isTable,
Name = paramName,
ParamType = SqlTypeUtility.FromSqlType(typeName)
});
}
}
return(results);
}
public static Type ResolveUserType(this ColumnDescription columnDescription)
{
_ = Check.NotNull(nameof(columnDescription), columnDescription);
return(columnDescription.UserType switch
{
"sql_variant" => typeof(int),
_ => SqlTypeUtility.GetDataType(columnDescription.UserType, columnDescription.MaxLength),
});
public void AddColumnPrescriptions()
{
var fileName = Temporary.GetTempFilePath();
var project = CreateProject();
ValueFactoryManager.Initialize();
var i = 0;
foreach (var tableDescription in project.Descriptor.TableDescriptions)
{
var tablePrescription = new TablePrescription(tableDescription);
foreach (var columnDescription in tableDescription.ColumnDescriptions)
{
if (columnDescription.Identity != null || !columnDescription.IsWritable())
{
continue;
}
var type = columnDescription.UserType switch
{
"sql_variant" => typeof(int),
_ => SqlTypeUtility.GetDataType(columnDescription.UserType, columnDescription.MaxLength),
};
var factory = ValueFactoryManager.ValueFactoryInfos.FilterByType(type).FirstOrDefault();
Assert.NotNull(factory);
var configuration = factory.Configurator.CreateConfiguration();
var valueFactoryPrescription = new ValueFactoryPrescription(factory.Name + i++, factory.Name, configuration);
project.Prescriptor.AddValueFactory(valueFactoryPrescription);
var columnPrescription = new ColumnPrescription(columnDescription, valueFactoryPrescription, null);
tablePrescription.AddColumn(columnPrescription);
}
project.Prescriptor.AddTable(tablePrescription);
}
ProjectManager.SaveToFile(project, fileName);
Output.WriteLine(File.ReadAllText(fileName));
var project2 = ProjectManager.LoadFromFile(fileName);
IoAssert.AsExpected(project2);
}
public static string SqlDefinition(this ColumnDescription columnDescription) => SqlTypeUtility.GetSqlColumnDefinition(
columnDescription.Name,
columnDescription.UserType,
columnDescription.Nullable,
columnDescription.MaxLength,
columnDescription.Precision,
columnDescription.Scale,
null,
null);
