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