public static ReadOnlyCollection <Line> Create(string table, IReadOnlyCollection <string> columns, IReadOnlyCollection <IReadOnlyCollection <string> > rows)
{
Assert.IsTrue(!table.IsNullOrEmpty());
Assert.IsTrue(!table.IsNullOrEmpty());
ArgumentNullException.ThrowIfNull(columns);
ArgumentNullException.ThrowIfNull(rows);
Assert.IsTrue(columns.Count > 0);
Assert.IsTrue(rows.All(row => row.Count == columns.Count));
var textBuilder = new TextBuilder();
textBuilder.Add($"insert into {table}({columns.Join(",")})");
textBuilder.Add("values");
using (textBuilder.Indent(1))
{
foreach (var indexedRow in rows.SelectIndexed())
{
if (indexedRow.Index > 0)
{
textBuilder.AddToLastLine(",");
}
var values = indexedRow.Value.Join(",");
textBuilder.Add($"({values})");
}
}
return(textBuilder.ToLines());
}
private string CreateUpdateScript()
{
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 textBuilder = new TextBuilder();
textBuilder.Add($"update {Name}");
textBuilder.Add("set");
using (textBuilder.Indent(1))
{
var last = getTableSchemaResult.Columns.Count - 1;
foreach (var item in getTableSchemaResult.Columns.SelectIndexed())
{
var column = item.Value;
var line = new StringBuilder();
line.Append($"{column.ColumnName} = @{column.ColumnName}");
if (item.Index < last)
{
line.Append(',');
}
textBuilder.Add(line.ToString());
}
}
if (getTableSchemaResult.UniqueIndexColumns.Count > 0)
{
textBuilder.Add("where");
using (textBuilder.Indent(1))
{
var last = getTableSchemaResult.UniqueIndexColumns.Count - 1;
foreach (var item in getTableSchemaResult.UniqueIndexColumns.SelectIndexed())
{
var columnId = item.Value.ColumnId;
var column = getTableSchemaResult.Columns.First(i => i.ColumnId == columnId);
var line = new StringBuilder();
line.Append($"{column.ColumnName} = @{column.ColumnName}");
if (item.Index < last)
{
line.Append(',');
}
textBuilder.Add(line.ToString());
}
}
}
var script = textBuilder.ToLines().ToIndentedString(" ");
return(script);
}
public static IDisposable AddBlock(this TextBuilder indentedTextBuilder, string begin, string end)
{
indentedTextBuilder.Add(begin);
var indentation = indentedTextBuilder.Indent(1);
return(new Disposer(() =>
{
indentation.Dispose();
indentedTextBuilder.Add(end);
}));
}
public static ReadOnlyCollection <Line> Create(string table, IReadOnlyCollection <ColumnNameValue> whereColumns)
{
Assert.IsTrue(!table.IsNullOrEmpty());
Assert.IsNotNull(whereColumns);
Assert.IsTrue(whereColumns.Count > 0);
var textBuilder = new TextBuilder();
textBuilder.Add($"delete {table}");
textBuilder.Add("where");
using (textBuilder.Indent(1))
textBuilder.Add(whereColumns.Join(" and"));
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());
}
public static ReadOnlyCollection <Line> CreateDeleteSqlStatement(Guid id, long version)
{
var whereColumns = new[]
{
new ColumnNameValue("Id", id.ToSqlConstant()),
new ColumnNameValue("Version", version.ToSqlConstant())
};
var deleteSqlStatement = DeleteSqlStatementFactory.Create("dbo.OrmSampleTable", whereColumns);
var validation = ValidationFactory.Create("delete dbo.OrmSampleTable failed");
var textBuilder = new TextBuilder();
textBuilder.Add(deleteSqlStatement);
textBuilder.Add(validation);
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> 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> 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());
}
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> CreateUpdateSqlStatement(OrmSampleTable record, long expectedVersion)
{
var setColumns = new[]
{
new ColumnNameValue("Version", record.Version.ToSqlConstant()),
new ColumnNameValue("Text", record.Text.ToNullableNVarChar()),
new ColumnNameValue("Timestamp", record.Timestamp.ToSqlConstant())
};
var whereColumns = new[]
{
new ColumnNameValue("Id", record.Id.ToSqlConstant()),
new ColumnNameValue("Version", expectedVersion.ToSqlConstant())
};
var updateSqlStatement = UpdateSqlStatementFactory.Create("dbo.OrmSampleTable", setColumns, whereColumns);
var validation = ValidationFactory.Create("update dbo.OrmSampleTable failed");
var textBuilder = new TextBuilder();
textBuilder.Add(updateSqlStatement);
textBuilder.Add(validation);
return(textBuilder.ToLines());
}
public static ReadOnlyCollection <Line> Create(string table, IReadOnlyCollection <ColumnNameValue> setColumns,
IReadOnlyCollection <ColumnNameValue> whereColumns)
{
Assert.IsTrue(!table.IsNullOrEmpty());
ArgumentNullException.ThrowIfNull(setColumns);
Assert.IsTrue(setColumns.Count > 0);
ArgumentNullException.ThrowIfNull(whereColumns);
Assert.IsTrue(whereColumns.Count > 0);
Assert.IsTrue(whereColumns.All(column => !column.Value.IsNullOrEmpty()));
var textBuilder = new TextBuilder();
textBuilder.Add($"update {table}");
textBuilder.Add("set");
using (textBuilder.Indent(1))
textBuilder.Add(setColumns.Join(","));
textBuilder.Add($"where");
using (textBuilder.Indent(1))
textBuilder.Add(whereColumns.Join(" and"));
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());
}
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);
}
private void CreateDatabaseSnapshotScriptToClipboardMenuItem_Click(object?sender, EventArgs e)
{
var databaseName = _name;
var databaseSnapshotName = $"{databaseName}_Snapshot_{DateTime.Now.ToString("yyyyMMdd_HHmm")}";
var logical_file_name = GetLogicalFileName(databaseName);
var osFileName = $"D:\\Backup\\{databaseSnapshotName}.ss";
var textBuilder = new TextBuilder();
textBuilder.Add($"CREATE DATABASE [{databaseSnapshotName}]");
textBuilder.Add("ON");
using (textBuilder.AddBlock("(", ")"))
{
textBuilder.Add($"NAME = {logical_file_name},");
textBuilder.Add($"FILENAME = {osFileName.ToVarChar()}");
}
textBuilder.Add($"AS SNAPSHOT OF [{databaseName}]");
textBuilder.Add(Line.Empty);
textBuilder.Add("USE master");
textBuilder.Add($"ALTER DATABASE [{databaseName}] SET SINGLE_USER");
textBuilder.Add($"RESTORE DATABASE [{databaseName}] FROM");
textBuilder.Add($"DATABASE_SNAPSHOT = {databaseSnapshotName.ToVarChar()}");
textBuilder.Add($"ALTER DATABASE [{databaseName}] SET MULTI_USER WITH NO_WAIT");
var text = textBuilder.ToLines().ToIndentedString(" ");
var queryForm = (IQueryForm)sender;
queryForm.ClipboardSetText(text);
}
