private void PopulateParamters(Procedure procedure, StoreProcedureMetadata result, SqlConnection conn)
{
var procedureName = procedure.Name;
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
select
name as ParameterName,
type_name(user_type_id) as TypeName,
max_length as ParameterMaxBytes,
precision as ParameterPrecision,
scale as ParameterScale,
is_nullable as IsNullable
from sys.parameters where object_id = object_id(@procedureName)
order by parameter_id
";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@procedureName", procedureName);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var param = new ParameterInfo();
typeof(ParameterInfo).GetProperties().Where(p => p.Name != "DotNetType").ToList().ForEach(p =>
{
p.SetValue(param, reader[p.Name]);
});
var dotNetType = _sqlTypeConverter.TypeList.First(_ => _.SqlServerTypeName == param.TypeName).DotNetType;
param.DotNetType = dotNetType;
result.Parameters.Add(param);
}
}
}
}
public StoreProcedureMetadata GetMetadata(Procedure procedure, SqlConnection connection)
{
var result = new StoreProcedureMetadata();
PopulateParamters(procedure, result, connection);
PopulateResults(procedure, result, connection);
return(result);
}
public void CreateStoredProcedureAccessClasses(Procedure procedure, StoreProcedureMetadata metadata)
{
FileInfo file;
if (!string.IsNullOrEmpty(procedure.Criteria))
{
var criteria = GetCriteria(metadata.Parameters, procedure.Criteria, procedure.Namespaces);
var criteriaFile = Path.Combine(procedure.Locations.Interfaces, procedure.Criteria + ".cs");
file = new FileInfo(criteriaFile);
file.Directory.Create();
File.WriteAllText(criteriaFile, criteria);
}
var wrapperData = GetWrapperData(procedure);
var wrapperDataFile = Path.Combine(procedure.Locations.Interfaces, procedure.WrapperData + ".cs");
file = new FileInfo(wrapperDataFile);
file.Directory.Create();
File.WriteAllText(wrapperDataFile, wrapperData);
var executorInterfaceData = GetExecutorInterface(procedure);
var executorInterfaceFile = Path.Combine(procedure.Locations.Interfaces, "I" + procedure.Executor + ".cs");
file = new FileInfo(executorInterfaceFile);
file.Directory.Create();
File.WriteAllText(executorInterfaceFile, executorInterfaceData);
for (int i = 0; i < metadata.Results.Count; i++)
{
var oneClass = GetClass(metadata.Results[i], procedure.Classes[i], procedure.Namespaces);
var oneClassFile = Path.Combine(procedure.Locations.Interfaces, procedure.Classes[i] + ".cs");
file = new FileInfo(oneClassFile);
file.Directory.Create();
File.WriteAllText(oneClassFile, oneClass);
oneClass = GetMapperInterface(metadata.Results[i], procedure.Classes[i], procedure.Namespaces);
oneClassFile = Path.Combine(procedure.Locations.Interfaces, $"I{procedure.Classes[i]}Mapper.cs");
file = new FileInfo(oneClassFile);
file.Directory.Create();
File.WriteAllText(oneClassFile, oneClass);
oneClass = GetMapperClass(metadata.Results[i], procedure.Classes[i], procedure.Namespaces);
oneClassFile = Path.Combine(procedure.Locations.Implementations, $"{procedure.Classes[i]}Mapper.cs");
file = new FileInfo(oneClassFile);
file.Directory.Create();
File.WriteAllText(oneClassFile, oneClass);
}
var executorClassData = GetExecutorClass(procedure, metadata);
var executorClassFile = Path.Combine(procedure.Locations.Implementations, procedure.Executor + ".cs");
file = new FileInfo(executorClassFile);
file.Directory.Create();
File.WriteAllText(executorClassFile, executorClassData);
}
private void PopulateResults(Procedure procedure, StoreProcedureMetadata result, SqlConnection conn)
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = procedure.Name;
cmd.CommandType = CommandType.StoredProcedure;
result.Parameters.ForEach(p =>
{
var dotNetType = _sqlTypeConverter.TypeList.First(_ => _.SqlServerTypeName == p.TypeName).DotNetType;
var value = (object)DBNull.Value;
if (dotNetType != null)
{
if (dotNetType == typeof(string))
{
value = "";
}
else
{
value = Activator.CreateInstance(dotNetType);
}
}
cmd.Parameters.AddWithValue(p.ParameterName, value);
});
var listOfResults = new List <DataTable>();
using (var reader = cmd.ExecuteReader())
{
listOfResults.Add(reader.GetSchemaTable());
while (reader.NextResult())
{
listOfResults.Add(reader.GetSchemaTable());
}
}
listOfResults.ForEach(r =>
{
var singleResultData = new List <SchemaTableRow>();
foreach (DataRow row in r.Rows)
{
var newRow = SchemaTableRow.FromDataRow(row);
newRow.FriendlyTypeName = _cSharpClassNameConverter.GetCSharpName(newRow.DataType);
singleResultData.Add(newRow);
}
result.Results.Add(singleResultData);
});
}
}
public string GetExecutorClass(Procedure procedure, StoreProcedureMetadata metadata)
{
var result = new StringBuilder();
result.Append(Templates.ExecutorClassTemplate
.Replace(Templates.ClassTag, procedure.Executor)
.Replace(Templates.ResultClass, procedure.WrapperData)
.Replace(Templates.CriteriaClass, procedure.Criteria)
.Replace(Templates.InterfaceNamespaceTag, procedure.Namespaces.Interfaces)
.Replace(Templates.NamespaceTag, procedure.Namespaces.Classes))
.Replace(" ", "\t");
var privateMembers = new StringBuilder();
procedure.Classes.ForEach(c =>
{
privateMembers.AppendLine($"\t\tprivate readonly I{c}Mapper _{FirstCharToLower(c)}Mapper;");
});
var contructorParameters = new StringBuilder();
procedure.Classes.ForEach(c =>
{
string comma = "," + Environment.NewLine;
if (procedure.Classes.IndexOf(c) == procedure.Classes.Count - 1)
{
comma = "";
}
contructorParameters.Append($"\t\t\tI{c}Mapper {FirstCharToLower(c)}Mapper{comma}");
});
var setMemebers = new StringBuilder();
procedure.Classes.ForEach(c =>
{
setMemebers.AppendLine($"\t\t\t_{FirstCharToLower(c)}Mapper = {FirstCharToLower(c)}Mapper;");
});
var initWrapper = new StringBuilder();
procedure.Classes.ForEach(c =>
{
initWrapper.AppendLine($"\t\t\tresult.{c}Data = new List<{c}>();");
});
var commandParameters = new StringBuilder();
metadata.Parameters.ForEach(p =>
{
bool isFirst = metadata.Parameters.IndexOf(p) == 0;
if (isFirst)
{
commandParameters.AppendLine($"\t\t\t\tvar param = command.CreateParameter();");
}
else
{
commandParameters.AppendLine($"\t\t\t\tparam = command.CreateParameter();");
}
commandParameters.AppendLine($"\t\t\t\tparam.ParameterName = \"{p.ParameterName}\";");
var type = _sqlTypeConverter.TypeList.First(t => t.SqlServerTypeName == p.TypeName);
commandParameters.AppendLine($"\t\t\t\tparam.DbType = System.Data.DbType.{type.DbType.ToString()};");
if (type.IsMaxLengthNeeded)
{
commandParameters.AppendLine($"\t\t\t\tparam.Size = {p.ParameterMaxBytes};");
}
if (type.IsPrecisionNeeded)
{
commandParameters.AppendLine($"\t\t\t\tparam.Precision = {p.ParameterPrecision};");
}
if (type.IsScaleNeeded)
{
commandParameters.AppendLine($"\t\t\t\tparam.Precision = {p.ParameterScale};");
}
var value = p.ParameterName.Replace("@", "");
commandParameters.AppendLine($"\t\t\t\tparam.Value = criteria.{value};");
if (Nullable.GetUnderlyingType(p.DotNetType) != null || p.DotNetType == typeof(string) || p.IsNullable)
{
commandParameters.AppendLine($"\t\t\t\tif(criteria.{value} == null)");
commandParameters.AppendLine("\t\t\t\t{");
commandParameters.AppendLine("\t\t\t\t\tparam.Value = DBNull.Value;");
commandParameters.AppendLine("\t\t\t\t}");
}
commandParameters.AppendLine("\t\t\t\tcommand.Parameters.Add(param);");
});
var readResults = new StringBuilder();
procedure.Classes.ForEach(c =>
{
bool isFirst = procedure.Classes.IndexOf(c) == 0;
if (!isFirst)
{
readResults.AppendLine($"\t\t\t\t\tawait reader.NextResultAsync();");
}
readResults.AppendLine("\t\t\t\t\twhile (await reader.ReadAsync())");
readResults.AppendLine("\t\t\t\t\t{");
readResults.AppendLine($"\t\t\t\t\t\tresult.{c}Data.Add(_{FirstCharToLower(c)}Mapper.Map(reader));");
readResults.AppendLine("\t\t\t\t\t}");
});
var output = result.ToString()
.Replace(Templates.Mappers, privateMembers.ToString())
.Replace(Templates.ConstructorParameters, contructorParameters.ToString())
.Replace(Templates.SetMembers, setMemebers.ToString())
.Replace(Templates.InitWrapper, initWrapper.ToString())
.Replace(Templates.CommandParameters, commandParameters.ToString())
.Replace(Templates.ReadResults, readResults.ToString())
.Replace(Templates.ProcedureName, @"""" + procedure.Name + @"""");
if (string.IsNullOrEmpty(procedure.Criteria))
{
output = output.Replace(@" criteria, ", "");
}
return(output);
}
