private string GetCommandText(CommandDefinition commandDefinition, IBaseModel entityType, CrudAction crudAction)
{
var entityName = entityType?.GetType()?.Name;
if (!string.IsNullOrWhiteSpace(commandDefinition?.Command) || !string.IsNullOrWhiteSpace(commandDefinition?.QueryFilePath))
{
return(!string.IsNullOrWhiteSpace(commandDefinition.Command) ? commandDefinition.Command : fileService.GetFileContent(commandDefinition.QueryFilePath));
}
string commandText = "";
var tableName = entityName?.Pluralize();
if (crudAction == CrudAction.Delete)
{
commandText = $"DELETE FROM {tableName}";
}
else if (crudAction == CrudAction.Insert)
{
commandText = $"INSERT INTO {tableName}";
}
else if (crudAction == CrudAction.Select)
{
commandText = $"SELECT * FROM {tableName}";
}
else if (crudAction == CrudAction.Update)
{
commandText = $"UPDATE {tableName}";
}
var parameters = GetPropertiesWithValues(entityType)?.ToList();
if (crudAction == CrudAction.Delete ||
crudAction == CrudAction.Select)
{
var whereClause = "";
if ((parameters?.Count ?? 0) > 0)
{
whereClause = string.Join(" AND ", parameters.Select(p => $"{p.Name} = @{p.Name}"));
if ((whereClause?.Length ?? 0) > 0)
{
whereClause = $" WHERE {whereClause}";
}
}
commandText = $"{commandText} {whereClause}".Trim();
}
else if (crudAction == CrudAction.Insert)
{
var parameterNames = string.Join(", ", parameters?.Select(p => $"@{p.Name}")?.ToList() ?? Enumerable.Empty <string>());
var columns = string.Join(", ", parameters?.Select(p => p.Name)?.ToList() ?? Enumerable.Empty <string>());
commandText = $"{commandText} ({columns}) VALUES ({parameterNames})";
var identityProperty = GetIdentityProperty(entityType)?.FirstOrDefault();
if (identityProperty != null)
{
commandText = $"{commandText} SELECT * FROM {tableName} WHERE {identityProperty.Name} = SCOPE_IDENTITY()";
}
else
{
commandText = $"{commandText} {GetCommandText(commandDefinition, entityType, CrudAction.Select)}";
}
}
else if (crudAction == CrudAction.Update)
{
var primaryKeyColumnNames = GetPrimaryKeyProperties(entityType)?.Select(p => p.Name)?.ToArray();
var columnNames = entityType.GetPropertyNames()?.Where(c => Array.IndexOf(primaryKeyColumnNames, c) == -1)?.ToList();
var updateClause = string.Join(",", columnNames?.Select(c => $"{c} = {ParameterPrefix}{c}")?.ToList() ?? Enumerable.Empty <string>());
var whereClause = string.Join(",", primaryKeyColumnNames?.Select(c => $"{c} = {ParameterPrefix}{c}")?.ToList() ?? Enumerable.Empty <string>());
commandText = $"SET {updateClause} {whereClause}";
}
return(commandText);
}
