// https://www.developerfusion.com/article/143896/understanding-the-basics-of-roslyn/
public static void GenerateSchema()
{
string sql = GetScript("columns.sql");
System.Collections.Generic.List <ColumnDefinition> allColumns = null;
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allColumns = connection.Query <ColumnDefinition>(sql).ToList();
}
//var ls = allColumns.GroupBy(p => new { p.TABLE_NAME })
// .Select(g => g.First().TABLE_NAME)
//.ToList();
System.Collections.Generic.List <string> ls = null;
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
ls = connection.Query <string>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ").ToList();
}
foreach (string table in ls)
{
var tableColumns = allColumns
.Where(p => p.TABLE_NAME.Equals(table, System.StringComparison.OrdinalIgnoreCase))
.ToList();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"
namespace BlueMine.Db
{
public partial class ");
sb.Append("T_"); // filed-name may not be equal to class name, rename class
sb.Append(table);
sb.Append(@"
{
");
foreach (ColumnDefinition cl in tableColumns)
{
sb.Append(@" public ");
sb.Append(cl.DOTNET_TYPE);
sb.Append(" ");
// for keywords
//if (!Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsValidIdentifier(cl.COLUMN_NAME))
if (Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsReservedKeyword(
Microsoft.CodeAnalysis.CSharp.SyntaxFacts.GetKeywordKind(cl.COLUMN_NAME)
))
{
sb.Append("@");
}
sb.Append(cl.COLUMN_NAME);
bool useProperties = true;
if (useProperties)
{
sb.Append(" { get; set; } // ");
}
else
{
sb.Append("; // ");
}
sb.AppendLine(cl.SQL_TYPE);
} // Next cl
sb.AppendLine(@" }");
sb.AppendLine(System.Environment.NewLine);
sb.AppendLine(@"}");
string fileContents = sb.ToString();
string dir = System.IO.Path.Combine("Db", "Redmine", "BlueMappings", table + ".cs");
string d = System.IO.Path.GetDirectoryName(dir);
if (!System.IO.Directory.Exists(d))
{
System.IO.Directory.CreateDirectory(d);
}
System.IO.File.WriteAllText(dir, fileContents, System.Text.Encoding.UTF8);
}
}
public static void GenerateContext(string ns)
{
string table_prefix = "T_";
string cs = @"
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace " + ns + @"
{
public partial class RedmineContext : DbContext
{
";
List <TableDefinitions> allTables = null;
// LOOP Tables
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
System.Console.WriteLine(connection.ConnectionString);
allTables = connection.Query <TableDefinitions>("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME ")
.ToList();
}
for (int i = 0; i < allTables.Count; ++i)
{
cs += @" public virtual DbSet<" + table_prefix + allTables[i].TABLE_NAME + @"> " + allTables[i].TABLE_NAME + @" { get; set; }
";
}
// MSG from entity framwork on error:
// Unable to generate entity type for table 'dbo.groups_users'. Please see the warning messages.
cs += @"
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(
$@""Server ={ System.Environment.MachineName}\SQLEXPRESS; Database = Redmine; Integrated Security = true; "");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
";
string sql = SchemaGenerator.GetScript("columns.sql");
System.Collections.Generic.List <ColumnDefinition> allColumns = null;
System.Collections.Generic.List <IndexDefinition> allIndices = null;
System.Collections.Generic.List <PrimaryKeyDefinition> allPrimaryKeys = null;
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allColumns = connection.Query <ColumnDefinition>(sql).ToList();
}
sql = SchemaGenerator.GetScript("indices.sql");
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allIndices = connection.Query <IndexDefinition>(sql).ToList();
}
sql = SchemaGenerator.GetScript("primary_keys.sql");
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
allPrimaryKeys = connection.Query <PrimaryKeyDefinition>(sql).ToList();
}
for (int i = 0; i < allTables.Count; ++i)
{
List <PrimaryKeyDefinition> primarykeyInfo = (
from x in allPrimaryKeys
where x.TABLE_NAME == allTables[i].TABLE_NAME
orderby x.PRIMARY_KEY_NAME ascending
select x
).ToList();
List <ColumnDefinition> columnInfo = (
from x in allColumns
where x.TABLE_NAME == allTables[i].TABLE_NAME
orderby x.ORDINAL_POSITION ascending
select x
).ToList();
List <IndexDefinition> indexInfo = (
from x in allIndices
where x.TABLE_NAME == allTables[i].TABLE_NAME
orderby x.INDEX_ORDINAL ascending
select x
).ToList();
cs += @" modelBuilder.Entity<" + table_prefix + allTables[i].TABLE_NAME + @">(entity =>
{
";
for (int l = 0; l < primarykeyInfo.Count; ++l)
{
string indcoldef = primarykeyInfo[l].PRIMARY_KEY;
if (primarykeyInfo[l].IS_MULTICOLUMN_KEY)
{
indcoldef = "new { " + primarykeyInfo[l].PRIMARY_KEY + " } ";
}
cs += @"
entity.HasKey(e => " + indcoldef + @")";
if (!string.IsNullOrEmpty(primarykeyInfo[l].PRIMARY_KEY_NAME))
{
cs += @"
.HasName(""" + primarykeyInfo[l].PRIMARY_KEY_NAME + @""")";
}
cs += @";
";
}
for (int k = 0; k < indexInfo.Count; ++k)
{
string indcoldef = indexInfo[k].KEY_COLUMNS;
if (indexInfo[k].IS_MULTICOLUMN_INDEX)
{
indcoldef = "new { " + indexInfo[k].KEY_COLUMNS + " } ";
}
cs += @"
entity.HasIndex(e => " + indcoldef + @")";
if (!string.IsNullOrEmpty(indexInfo[k].INDEX_NAME))
{
cs += @"
.HasName(""" + indexInfo[k].INDEX_NAME + @""")";
}
cs += @";
";
}
cs += @";
";
for (int j = 0; j < columnInfo.Count; ++j)
{
// Indices
//entity.HasIndex(e => e.NewStatusId)
//.HasName("index_workflows_on_new_status_id");
//entity.HasIndex(e => new { e.RootId, e.Lft, e.Rgt })
// .HasName("index_issues_on_root_id_and_lft_and_rgt");
// .IsUnique();
// Columns
cs += @"
entity.Property(e => e.";
if (!Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsValidIdentifier(columnInfo[j].COLUMN_NAME))
{
System.Console.WriteLine($@" ""{allTables[i].TABLE_NAME}"".""{columnInfo[j].COLUMN_NAME}"" is not a valid identifier.");
}
if (Microsoft.CodeAnalysis.CSharp.SyntaxFacts.IsReservedKeyword(
Microsoft.CodeAnalysis.CSharp.SyntaxFacts.GetKeywordKind(columnInfo[j].COLUMN_NAME)
))
{
cs += "@";
}
cs += columnInfo[j].COLUMN_NAME + @")";
if (columnInfo[j].IS_REQUIRED)
{
cs += @"
.IsRequired()";
}
cs += @"
.HasColumnName(""" + columnInfo[j].COLUMN_NAME + @""")";
if (columnInfo[j].COLUMN_TYPE != null)
{
cs += @"
.HasColumnType(""" + columnInfo[j].COLUMN_TYPE + @""")";
}
if (columnInfo[j].MAX_LENGTH.HasValue && columnInfo[j].MAX_LENGTH != -1)
{
cs += @"
.HasMaxLength(" + columnInfo[j].MAX_LENGTH.ToString() + @")";
}
if (!string.IsNullOrEmpty(columnInfo[j].COLUMN_DEFAULT))
{
cs += @"
.HasDefaultValueSql(""" + columnInfo[j].COLUMN_DEFAULT + @""")";
}
cs += @";
";
//columnInfo[j].COLUMN_NAME
//columnInfo[j].COLUMN_DEFAULT
// columnInfo[j].DOTNET_TYPE
//columnInfo[j].ORDINAL_POSITION
// columnInfo[j].SQL_TYPE
} // Next j
cs += @"
});
";
} // Next i
cs += @"
} // End Sub OnModelCreating
} // End partial class RedmineContext : DbContext
} // End namespace " + ns + @"
";
System.Console.WriteLine(cs);
System.IO.File.WriteAllText("SomeContext.cs", cs, System.Text.Encoding.UTF8);
}
public static void Test()
{
string table_schema = "dbo";
// string table_name = "T_VWS_Ref_Darstellung";
string table_name = "T_VWS_Ref_PdfLegendenKategorie";
using (System.Data.Common.DbConnection connection = SqlFactory.GetConnection())
{
string select_query = connection.QueryFirstEmbedded <string>("SELECT_Columns.sql", typeof(TestDapper), new
{
in_schema = table_schema,
in_table = table_name,
});
TableDeclaration td = connection.QueryFirstEmbedded <TableDeclaration>("Upsert_Table.sql", typeof(TestDapper), new
{
in_schema = table_schema,
in_table = table_name,
});
if (td == null)
{
return;
}
System.Collections.Generic.List <System.Collections.Generic.IDictionary <string, object> > data_to_insert =
connection.Query(select_query).Select(x => (System.Collections.Generic.IDictionary <string, object>)x).ToList();
System.Console.WriteLine(data_to_insert);
System.Collections.Generic.IEnumerable <TableColumnDefinition> lsColumns = connection.QueryEmbedded <TableColumnDefinition>("Upsert_Columns.sql", typeof(TestDapper), new
{
in_schema = table_schema,
in_table = table_name,
});
if (lsColumns == null || !System.Linq.Enumerable.Any(lsColumns))
{
return;
}
string sql = @"
SET NOCOUNT ON;
" + td.table_declaration + @"
INSERT INTO @" + td.variable_name + @"
(
";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " ";
}
else
{
sql += ",";
}
sql += cd.QUOTED_COLUMN_NAME;
sql += System.Environment.NewLine;
}
sql += @")
";
for (int i = 0; i < data_to_insert.Count; ++i)
{
if (i != 0)
{
sql += "UNION ALL ";
}
sql += "SELECT ";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " ";
}
else
{
sql += ",";
}
object value = data_to_insert[i][cd.COLUMN_NAME];
sql += ObjectToInsertStringValue(value);
sql += System.Environment.NewLine;
}
}
sql += @";
-- SELECT * FROM @" + td.variable_name + @"
MERGE INTO " + table_name + @" AS A
USING @" + td.variable_name + @" AS CTE ON CTE." + lsColumns.AsList()[0].QUOTED_COLUMN_NAME + " = A." + lsColumns.AsList()[0].QUOTED_COLUMN_NAME + @"
WHEN MATCHED
THEN UPDATE
";
foreach (TableColumnDefinition cd in lsColumns)
{
if (cd.ORDINAL_POSITION == 1)
{
continue;
}
sql += " ";
if (cd.ORDINAL_POSITION == 2)
{
sql += "SET ";
}
else
{
sql += " ,";
}
sql += "A." + cd.QUOTED_COLUMN_NAME + " = CTE." + cd.QUOTED_COLUMN_NAME + " ";
sql += System.Environment.NewLine;
}
sql += @"
WHEN NOT MATCHED THEN
INSERT
(
";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " ";
}
else
{
sql += ",";
}
sql += cd.QUOTED_COLUMN_NAME;
sql += System.Environment.NewLine;
}
sql += @"
)
VALUES
(
";
foreach (TableColumnDefinition cd in lsColumns)
{
sql += " ";
if (cd.ORDINAL_POSITION == 1)
{
sql += " CTE.";
}
else
{
sql += ",CTE.";
}
sql += cd.QUOTED_COLUMN_NAME;
sql += System.Environment.NewLine;
}
sql += @"
);
-- DELETE FROM " + table_name + @" WHERE xxx = '123';
-- SELECT * FROM " + table_name + @";
SET NOCOUNT OFF;
";
System.Console.WriteLine(sql);
}
}
