public static ReadOnlyCollection <Line> Create(string schema, string table, ReadOnlyCollection <Column> columns)
{
var textBuilder = new TextBuilder();
textBuilder.Add($"public static ReadOnlyCollection<Line> CreateInsertSqlStatement(IEnumerable<{table}> records)");
using (textBuilder.AddCSharpBlock())
{
//textBuilder.Add($"var sqlTable = new SqlTable(\"{_owner}\",\"{_name}\", new[]");
//using (textBuilder.AddCSharpBlock())
//{
// sequence = new Sequence();
// foreach (var column in columns)
// {
// var last = sequence.Next() == columns.Count - 1;
// var name = column.ColumnName;
// var separator = !last ? "," : null;
// textBuilder.Add($"\"{name}\"{separator}");
// }
//}
//textBuilder.AddToLastLine(".ToReadOnlyCollection());");
//textBuilder.Add(Line.Empty);
textBuilder.Add("var columns = new[]");
using (textBuilder.AddCSharpBlock())
{
foreach (var indexedColumn in columns.SelectIndexed())
{
if (indexedColumn.Index > 0)
{
textBuilder.AddToLastLine(",");
}
var column = indexedColumn.Value;
textBuilder.Add($"\"{column.ColumnName}\"");
}
}
textBuilder.AddToLastLine(";");
textBuilder.Add($"var rows = records.Select(record => new[]");
using (textBuilder.AddCSharpBlock())
{
foreach (var indexedColumn in columns.SelectIndexed())
{
if (indexedColumn.Index > 0)
{
textBuilder.AddToLastLine(",");
}
var column = indexedColumn.Value;
var methodName = MethodName.GetToSqlConstantMethodName(column.SqlDataTypeName, column.IsNullable);
textBuilder.Add($"record.{column.ColumnName}.{methodName}()");
}
}
textBuilder.AddToLastLine(").ToReadOnlyCollection();");
textBuilder.Add($"var insertSqlStatement = InsertSqlStatementFactory.Create(\"{schema}.{table}\", columns, rows);");
textBuilder.Add("return insertSqlStatement;");
}
return(textBuilder.ToLines());
}
public static ReadOnlyCollection <Line> Create(string schema, string table, Column identifierColumn, Column versionColumn,
IReadOnlyCollection <Column> columns)
{
var arguments = new List <string>();
arguments.Add($"{table} record");
if (versionColumn != null)
{
var csharpTypeName = SqlDataTypeArray.SqlDataTypes.First(i => i.SqlDataTypeName == versionColumn.SqlDataTypeName).CSharpTypeName;
arguments.Add($"{csharpTypeName} expected{versionColumn.ColumnName}");
}
var textBuilder = new TextBuilder();
textBuilder.Add($"public static ReadOnlyCollection<Line> CreateUpdateSqlStatement({arguments.Join(", ")})");
using (textBuilder.AddCSharpBlock())
{
textBuilder.Add("var setColumns = new []");
using (textBuilder.AddCSharpBlock())
foreach (var item in columns.SelectIndexed())
{
if (item.Index > 0)
{
textBuilder.AddToLastLine(",");
}
var column = item.Value;
var method = MethodName.GetToSqlConstantMethodName(column.SqlDataTypeName, column.IsNullable);
textBuilder.Add($"new ColumnNameValue(\"{column.ColumnName}\", record.{column.ColumnName}.{method}())");
}
textBuilder.AddToLastLine(";");
textBuilder.Add("var whereColumns = new[]");
using (textBuilder.AddCSharpBlock())
{
var method = MethodName.GetToSqlConstantMethodName(identifierColumn.SqlDataTypeName, identifierColumn.IsNullable);
textBuilder.Add($"new ColumnNameValue(\"{identifierColumn.ColumnName}\", record.{identifierColumn.ColumnName}.{method}())");
if (versionColumn != null)
{
textBuilder.AddToLastLine(",");
method = MethodName.GetToSqlConstantMethodName(versionColumn.SqlDataTypeName, versionColumn.IsNullable);
textBuilder.Add($"new ColumnNameValue(\"{versionColumn.ColumnName}\", expected{versionColumn.ColumnName}.{method}())");
}
}
textBuilder.AddToLastLine(";");
textBuilder.Add($"var updateSqlStatement = UpdateSqlStatementFactory.Create(\"{schema}.{table}\", setColumns, whereColumns);");
textBuilder.Add($"var validation = ValidationFactory.Create(\"update {schema}.{table} failed\");");
textBuilder.Add("var textBuilder = new TextBuilder();");
textBuilder.Add("textBuilder.Add(updateSqlStatement);");
textBuilder.Add("textBuilder.Add(validation);");
textBuilder.Add("return textBuilder.ToLines();");
}
return(textBuilder.ToLines());
}
public static ReadOnlyCollection <Line> Create(string schema, string table, Column identifierColumn, Column versionColumn)
{
var arguments = new List <string>();
var csharpTypeName = SqlDataTypeArray.SqlDataTypes.First(i => i.SqlDataTypeName == identifierColumn.SqlDataTypeName).CSharpTypeName;
arguments.Add($"{csharpTypeName} {identifierColumn.ColumnName.ToCamelCase()}");
if (versionColumn != null)
{
csharpTypeName = SqlDataTypeArray.SqlDataTypes.First(i => i.SqlDataTypeName == versionColumn.SqlDataTypeName).CSharpTypeName;
arguments.Add($"{csharpTypeName} {versionColumn.ColumnName.ToCamelCase()}");
}
var textBuilder = new TextBuilder();
textBuilder.Add($"public static ReadOnlyCollection<Line> CreateDeleteSqlStatement({arguments.Join(", ")})");
using (textBuilder.AddCSharpBlock())
{
textBuilder.Add("var whereColumns = new[]");
using (textBuilder.AddCSharpBlock())
{
var method = MethodName.GetToSqlConstantMethodName(identifierColumn.SqlDataTypeName, identifierColumn.IsNullable);
textBuilder.Add($"new ColumnNameValue(\"{identifierColumn.ColumnName}\", {identifierColumn.ColumnName.ToCamelCase()}.{method}())");
if (versionColumn != null)
{
textBuilder.AddToLastLine(",");
method = MethodName.GetToSqlConstantMethodName(versionColumn.SqlDataTypeName, versionColumn.IsNullable);
textBuilder.Add($"new ColumnNameValue(\"{versionColumn.ColumnName}\", {versionColumn.ColumnName.ToCamelCase()}.{method}())");
}
}
textBuilder.AddToLastLine(";");
textBuilder.Add($"var deleteSqlStatement = DeleteSqlStatementFactory.Create(\"{schema}.{table}\", whereColumns);");
textBuilder.Add($"var validation = ValidationFactory.Create(\"delete {schema}.{table} failed\");");
textBuilder.Add("var textBuilder = new TextBuilder();");
textBuilder.Add("textBuilder.Add(deleteSqlStatement);");
textBuilder.Add("textBuilder.Add(validation);");
textBuilder.Add("return textBuilder.ToLines();");
}
return(textBuilder.ToLines());
}
public static ReadOnlyCollection <Line> Create(string message)
{
var textBuilder = new TextBuilder();
textBuilder.Add("textBuilder.Add(\"if @@rowcount = 0\");");
textBuilder.Add("using (textBuilder.AddBlock(\"begin\", \"end\"))");
using (textBuilder.AddCSharpBlock())
{
textBuilder.Add($"textBuilder.Add(\"raiserror('{message}',16,1)\");");
textBuilder.Add("textBuilder.Add(\"return\");");
}
return(textBuilder.ToLines());
}
private static ReadOnlyCollection <Line> GetConstructor(string name, ReadOnlyCollection <DataTransferObjectField> fields)
{
var textBuilder = new TextBuilder();
var parameters = fields.Select(field => $"{field.Type} {field.Name.ToCamelCase()}").Join(", ");
textBuilder.Add($"public {name}({parameters})");
using (textBuilder.AddCSharpBlock())
{
foreach (var field in fields)
{
textBuilder.Add($"{field.Name} = {field.Name.ToCamelCase()};");
}
}
return(textBuilder.ToLines());
}
public static ReadOnlyCollection <Line> CreateDataTransferObject(string name, ReadOnlyCollection <DataTransferObjectField> fields)
{
var textBuilder = new TextBuilder();
textBuilder.Add($"public sealed class {name}");
using (textBuilder.AddCSharpBlock())
{
foreach (var field in fields)
{
textBuilder.Add($"public readonly {field.Type} {field.Name};");
}
textBuilder.Add(Line.Empty);
var constructor = GetConstructor(name, fields);
textBuilder.Add(constructor);
}
return(textBuilder.ToLines());
}
private void CsharpOrm_Click(object sender, EventArgs e)
{
var connectionString = DatabaseNode.Databases.Server.ConnectionString;
GetTableSchemaResult getTableSchemaResult;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var tableName = $"{DatabaseNode.Name}.{_owner}.{_name}";
getTableSchemaResult = TableSchema.GetTableSchema(connection, tableName);
}
var dataTransferObjectFields = getTableSchemaResult.Columns
.Select(column =>
{
var name = column.ColumnName;
var typeName = column.TypeName;
var isNullable = column.IsNullable;
var csharpTypeName = SqlDataTypeArray.SqlDataTypes.First(i => i.SqlDataTypeName == typeName).CSharpTypeName;
var csharpType = CSharpTypeArray.CSharpTypes.First(i => i.Name == csharpTypeName);
if (isNullable == true && csharpType.Type.IsValueType)
{
csharpTypeName += "?";
}
return(new DataTransferObjectField(name, csharpTypeName));
})
.ToReadOnlyCollection();
var dataTransferObject = DataTransferObjectFactory.CreateDataTransferObject(_name, dataTransferObjectFields).ToIndentedString(" ");
var columns = getTableSchemaResult.Columns
.Select(i => new Foundation.Data.DbQueryBuilding.Column(i.ColumnName, i.TypeName, i.IsNullable == true))
.ToReadOnlyCollection();
var createInsertSqlSqlStatementMethod = CreateInsertSqlStatementMethodFactory.Create(_owner, _name, columns);
var identifierColumn = getTableSchemaResult.UniqueIndexColumns
.Select(i => getTableSchemaResult.Columns.First(j => j.ColumnId == i.ColumnId))
.Select(i => new Foundation.Data.DbQueryBuilding.Column(i.ColumnName, i.TypeName, i.IsNullable == true))
.First();
var versionColumn = columns.FirstOrDefault(i => i.ColumnName == "Version");
columns = columns.Where(i => i.ColumnName != identifierColumn.ColumnName).ToReadOnlyCollection();
var createUpdateSqlStatementMethod = CreateUpdateSqlStatementMethodFactory.Create(_owner, _name, identifierColumn, versionColumn, columns);
var createDeleteSqlStatementMethod = CreateDeleteSqlStatementMethodFactory.Create(_owner, _name, identifierColumn, versionColumn);
var textBuilder = new TextBuilder();
textBuilder.Add(dataTransferObject);
textBuilder.Add(Line.Empty);
textBuilder.Add($"public static class {_name}SqlStatementFactory");
using (textBuilder.AddCSharpBlock())
{
textBuilder.Add(createInsertSqlSqlStatementMethod);
textBuilder.Add(Line.Empty);
textBuilder.Add(createUpdateSqlStatementMethod);
textBuilder.Add(Line.Empty);
textBuilder.Add(createDeleteSqlStatementMethod);
}
Clipboard.SetText(textBuilder.ToLines().ToIndentedString(" "));
var queryForm = (QueryForm)DataCommanderApplication.Instance.MainForm.ActiveMdiChild;
queryForm.SetStatusbarPanelText("Copying script to clipboard finished.",
queryForm.ColorTheme != null ? queryForm.ColorTheme.ForeColor : SystemColors.ControlText);
}
