/// <summary>
/// Adds actual table fields in a row to select list of a query.</summary>
/// <param name="query">
/// Query to select fields into (required).</param>
/// <param name="row">
/// Row with fields to be selected (required).</param>
/// <param name="exclude">
/// Fields to be excluded (optional).</param>
public static SqlQuery SelectTableFields(this SqlQuery query, IRow row, params Field[] exclude)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (row == null)
{
throw new ArgumentNullException("row");
}
HashSet <Field> excludeFields =
(exclude != null && exclude.Length > 0) ? new HashSet <Field>(exclude) : null;
var fields = row.Fields;
for (int i = 0; i < row.Fields.Count; i++)
{
Field field = fields[i];
if (EntityFieldExtensions.IsTableField(field))
{
if (excludeFields == null ||
!excludeFields.Contains(field))
{
query.Select(field);
}
}
}
return(query);
}
public CompareEntityToDBPageModel()
{
var assembly = Assembly.GetAssembly(typeof(CategoryRow));
var rowClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute <ConnectionKeyAttribute>() != null && w.IsSealed);
foreach (var rowClass in rowClasses)
{
Row row = (Row)Activator.CreateInstance(rowClass);
var connectionKey = rowClass.GetCustomAttribute <ConnectionKeyAttribute>().Value;
using (var connection = SqlConnections.NewByKey(connectionKey))
{
var schemaProvider = SchemaHelper.GetSchemaProvider(connection.GetDialect().ServerType);
var tableName = row.Table;
string schema = null;
if (tableName.IndexOf('.') > 0)
{
schema = tableName.Substring(0, tableName.IndexOf('.'));
tableName = tableName.Substring(tableName.IndexOf('.') + 1);
}
var rowFields = row.GetFields();
var dbFields = schemaProvider.GetFieldInfos(connection, schema ?? "dbo", tableName);
for (int i = 0; i < row.FieldCount; i++)
{
Field rowfield = rowFields[i];
if (EntityFieldExtensions.IsTableField(rowfield))
{
var dbField = dbFields.FirstOrDefault(f => f.FieldName == rowfield.Name);
string strNull = rowfield.Flags.HasFlag(FieldFlags.NotNull) ? "[NotNull]" : "";
if (dbField == null)
{
Issues.Add($"{Issues.Count + 1}. {strNull} {rowfield.Type} {rowfield.Name} <span class=\"label label-danger\">no corresponding field in database</span> at Table: {row.Table}");
}
else
{
string strTypeMismatch = rowfield.Type.ToString() == SchemaHelper.SqlTypeNameToFieldType(dbField.DataType, dbField.Size) ?
"" : "DataType Mismatch";
string strNullableMismatch = dbField.IsNullable == false && rowfield.Flags.HasFlag(FieldFlags.NotNull) == false ?
"Nullable Mismatch" : "";
if (!strNullableMismatch.IsEmptyOrNull() || !strTypeMismatch.IsEmptyOrNull())
{
Issues.Add($"{Issues.Count + 1}. {strNull} {rowfield.Type} {rowfield.Name} "
+ $"<span class=\"label label-danger\">{strTypeMismatch} {strNullableMismatch}</span> at Table: {row.Table}");
}
}
}
}
}
}
}
/// <summary>
/// Adds foreign / calculated table fields in a row to select list of a query.</summary>
/// <param name="query">
/// Query to select fields into (required).</param>
public static SqlQuery SelectNonTableFields(this SqlQuery query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
var ext = (ISqlQueryExtensible)query;
foreach (var field in ((IRow)ext.FirstIntoRow).Fields)
{
if (!EntityFieldExtensions.IsTableField(field) &&
(field.Flags & FieldFlags.NotMapped) != FieldFlags.NotMapped)
{
query.Select(field);
}
}
return(query);
}
public CompareEntityToDBPageModel()
{
var assembly = Assembly.GetAssembly(typeof(CompareEntityToDBPageModel));
#region Entity
var rowClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute <ConnectionKeyAttribute>() != null && w.IsSealed);
foreach (var rowClass in rowClasses)
{
Row row = (Row)Activator.CreateInstance(rowClass);
var connectionKey = rowClass.GetCustomAttribute <ConnectionKeyAttribute>().Value;
using (var connection = SqlConnections.NewByKey(connectionKey))
{
var dialect = connection.GetDialect();
var schemaProvider = SchemaHelper.GetSchemaProvider(dialect.ServerType);
var tableName = row.Table;
string schema = null;
if (tableName.IndexOf('.') > 0)
{
schema = tableName.Substring(0, tableName.IndexOf('.')).Trim('[', ']');
tableName = tableName.Substring(tableName.IndexOf('.') + 1).Trim('[', ']');
}
else
{
tableName = tableName.Trim('[', ']');
}
var rowFields = row.GetFields();
var dbFields = schemaProvider.GetFieldInfos(connection, schema, tableName);
if (dbFields == null || dbFields.Count() == 0)
{
Issues.Add($"{Issues.Count + 1}. {rowClass} at Table: {row.Table}; connection key: {connectionKey} <span class=\"label label-danger\">unable to retrive Table info.</span>");
}
else
{
for (int i = 0; i < row.FieldCount; i++)
{
Field rowfield = rowFields[i];
if (EntityFieldExtensions.IsTableField(rowfield))
{
var dbField = dbFields.FirstOrDefault(f => f.FieldName == rowfield.Name);
string strNull = rowfield.Flags.HasFlag(FieldFlags.NotNull) ? "[NotNull]" : "";
if (dbField == null)
{
Issues.Add($"{Issues.Count + 1}. {rowClass} > {strNull} {rowfield.Type} {rowfield.Name} at Table: {row.Table} <span class=\"label label-danger\">no corresponding field in database</span>");
}
else
{
var rowfieldTypeName = rowfield.Type.ToString();
string strTypeMismatch = rowfieldTypeName == SchemaHelper.SqlTypeNameToFieldType(dbField.DataType, dbField.Size) ?
"" : "DataType Mismatch";
string strNullableMismatch = dbField.IsNullable == false && rowfield.Flags.HasFlag(FieldFlags.NotNull) == false ?
"Nullable Mismatch" : "";
if (!strNullableMismatch.IsEmptyOrNull() || !strTypeMismatch.IsEmptyOrNull())
{
Issues.Add($"{Issues.Count + 1}. {rowClass} > {strNull} {rowfield.Type} {rowfield.Name} "
+ $"at Table: {row.Table} > {dbField.DataType} {(dbField.IsNullable ? "NULL" : "NOT NULL")} <span class=\"label label-danger\">{strTypeMismatch} {strNullableMismatch}</span>");
}
}
}
}
}
}
}
#endregion
#region Form
Issues.Add("---------------------------");
Issues.Add("---------------------------");
var formClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute <FormScriptAttribute>() != null && w.GetCustomAttribute <BasedOnRowAttribute>() != null);
foreach (var formClass in formClasses)
{
var basedOnRowType = formClass.GetCustomAttribute <BasedOnRowAttribute>().RowType;
Row basedOnRow = (Row)Activator.CreateInstance(basedOnRowType);
var rowFields = basedOnRow.GetFields();
for (int i = 0; i < basedOnRow.FieldCount; i++)
{
Field rowfield = rowFields[i];
if (rowfield.Flags.HasFlag(FieldFlags.NotNull) && !rowfield.Flags.HasFlag(FieldFlags.Identity))
{
var propInfo = formClass.GetProperty(rowfield.PropertyName);
if (propInfo == null)
{
Issues.Add($"{Issues.Count + 1}. {rowfield.Type} {rowfield.PropertyName} is [NotNull] in {basedOnRowType.Name} but not in {formClass.Name}");
}
}
}
}
#endregion
}
public GenerateMigrationFromEntityPageModel()
{
var assembly = Assembly.GetAssembly(typeof(GenerateMigrationFromEntityPageModel));
var rowClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute <ConnectionKeyAttribute>() != null && w.IsSealed);
foreach (var rowClass in rowClasses)
{
Row row = (Row)Activator.CreateInstance(rowClass);
string tableName = SchemaHelper.GetTableNameOnly(row.Table);
string schema = SchemaHelper.GetSchemaName(row.Table);
var rowFields = row.GetFields();
StringBuilder sb = new StringBuilder();
sb.Append($@"Create.Table(""{tableName}"")");
if (!string.IsNullOrWhiteSpace(schema) && schema != "dbo")
{
sb.Append($@".InSchema(""{schema}"")");
}
for (int i = 0; i < row.FieldCount; i++)
{
Field rowfield = rowFields[i];
if (EntityFieldExtensions.IsTableField(rowfield))
{
string strNullable = rowfield.Flags.HasFlag(FieldFlags.NotNull) ? ".NotNullable()" : ".Nullable()";
string strSize = rowfield.Type == FieldType.String ? rowfield.Size.ToString() : "";
sb.AppendLine();
sb.Append("<br />");
sb.Append($@".WithColumn(""{rowfield.Name}"")");
if (rowfield.Type == FieldType.String)
{
sb.Append($@".AsString({(rowfield.Size > 0 ? rowfield.Size : 100)})");
}
else
{
sb.Append($@".As{rowfield.Type.ToString()}()");
}
sb.Append(strNullable);
if (rowfield.Flags.HasFlag(FieldFlags.PrimaryKey))
{
sb.Append(".PrimaryKey()");
}
if (rowfield.Flags.HasFlag(FieldFlags.Identity))
{
sb.Append(".Identity()");
}
if (rowfield.Flags.HasFlag(FieldFlags.Unique))
{
sb.Append(".Unique()");
}
if (!string.IsNullOrWhiteSpace(rowfield.ForeignTable))
{
string foreignTableName = SchemaHelper.GetTableNameOnly(rowfield.ForeignTable);
sb.Append($@".ForeignKey(""{foreignTableName}"", ""{rowfield.ForeignField}"")");
string foreignSchema = SchemaHelper.GetSchemaName(rowfield.ForeignTable);
if (!string.IsNullOrWhiteSpace(foreignSchema) && foreignSchema != "dbo")
{
sb.Append($@".InSchema(""{schema}"")");
}
}
}
}
sb.Append(";");
sb.AppendLine();
sb.Append("<br /> ");
Migrations.Add(sb.ToString());
}
}
public GenerateMigrationFromEntityPageModel()
{
var assembly = Assembly.GetAssembly(typeof(GenerateMigrationFromEntityPageModel));
var rowClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute <ConnectionKeyAttribute>() != null && w.IsSealed);
var migrationNumber = 0;
foreach (var rowClass in rowClasses)
{
Row row = (Row)Activator.CreateInstance(rowClass);
string tableName = SchemaHelper.GetTableNameOnly(row.Table);
string schema = SchemaHelper.GetSchemaName(row.Table);
migrationNumber += 10;
var migrationModel = new MigrationModel
{
RowType = rowClass,
SchemaName = schema,
TableName = tableName,
MigrationNumber = migrationNumber
};
var rowFields = row.GetFields();
StringBuilder sb = new StringBuilder();
sb.Append($@"Create.Table(""{tableName}"")");
if (!string.IsNullOrWhiteSpace(schema) && schema != "dbo")
{
sb.Append($@".InSchema(""{schema}"")");
}
for (int i = 0; i < row.FieldCount; i++)
{
Field rowfield = rowFields[i];
if (EntityFieldExtensions.IsTableField(rowfield))
{
string strNullable = rowfield.Flags.HasFlag(FieldFlags.NotNull) ? ".NotNullable()" : ".Nullable()";
string strSize = rowfield.Type == FieldType.String ? rowfield.Size.ToString() : "";
sb.AppendLine();
//sb.Append("<br />");
sb.Append($@".WithColumn(""{rowfield.Name}"")");
if (rowfield.Type == FieldType.String)
{
sb.Append($@".AsString({(rowfield.Size > 0 ? rowfield.Size : 100)})");
}
else
{
sb.Append($@".As{rowfield.Type.ToString()}()");
}
sb.Append(strNullable);
if (rowfield.Flags.HasFlag(FieldFlags.PrimaryKey))
{
sb.Append(".PrimaryKey()");
}
if (rowfield.Flags.HasFlag(FieldFlags.Identity))
{
sb.Append(".Identity()");
}
if (rowfield.Flags.HasFlag(FieldFlags.Unique))
{
sb.Append(".Unique()");
}
if (!string.IsNullOrWhiteSpace(rowfield.ForeignTable))
{
string foreignTableName = SchemaHelper.GetTableNameOnly(rowfield.ForeignTable);
string foreignSchema = SchemaHelper.GetSchemaName(rowfield.ForeignTable);
migrationModel.ParentTables.Add(foreignTableName);
sb.Append($@".ForeignKey(""{foreignTableName}"", ""{rowfield.ForeignField}"")");
if (!string.IsNullOrWhiteSpace(foreignSchema) && foreignSchema != "dbo")
{
sb.Append($@".InSchema(""{schema}"")");
}
}
}
}
sb.Append(";");
sb.AppendLine();
sb.AppendLine();
migrationModel.Migration = sb.ToString();
if (Migrations.Exists(e => e.TableName == migrationModel.TableName))
{
migrationModel.Remarks += $"//Warning: Duplicate migration found." + Environment.NewLine
+ $"//TableName: {migrationModel.TableName}." + Environment.NewLine
+ $"//RowClass: {migrationModel.RowType.FullName}" + Environment.NewLine;
}
Migrations.Add(migrationModel);
}
//gather parent migrations
foreach (var migration in Migrations)
{
for (int i = 0; i < migration.ParentTables.Count - 1; i++)
{
var parentTable = migration.ParentTables[i];
var parentMigration = Migrations.Find(f => f.TableName == parentTable);
if (parentMigration != null)
{
migration.ParentTablesMigrations.Add(parentMigration);
}
else
{
migration.Remarks += $"//Warning: {parentTable} has been used as Foreign Table but the corresponding Entity/Row is not found." + Environment.NewLine;
}
if (migration.MigrationNumber <= migration.MaxParentMigrationNumber)
{
migration.MigrationNumber = migration.MaxParentMigrationNumber + 1;
}
}
}
//ordering migrations accroding to dependancies
int arrangementCount = 0; //safety counter
bool needArrangement = true;
while (needArrangement && arrangementCount < 10000)
{
arrangementCount++;
var notArrangedMigrations = Migrations.Where(w => w.IsArranged == false);
foreach (var migration in notArrangedMigrations)
{
var maxNumber = migration.MaxParentMigrationNumber;
//while (true)
//{
// var parentMigrations = migration.ParentTablesMigrations;
// if (parentMigrations.Count == 0) break;
// foreach (var parentMigration in parentMigrations)
// {
// if (maxNumber < parentMigration.MaxParentMigrationNumber)
// maxNumber = parentMigration.MaxParentMigrationNumber;
// }
//}
if (migration.MigrationNumber < maxNumber)
{
migration.MigrationNumber = maxNumber + 2;
}
}
needArrangement = Migrations.Exists(w => w.IsArranged == false);
}
Migrations = Migrations.OrderBy(o => o.MigrationNumber).ToList();
}
public CompareEntityToDBPageModel()
{
var assembly = Assembly.GetAssembly(typeof(CompareEntityToDBPageModel));
#region Entity
var rowClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute <ConnectionKeyAttribute>() != null && w.IsSealed);
foreach (var rowClass in rowClasses)
{
Row row = (Row)Activator.CreateInstance(rowClass);
var rowFields = row.GetFields();
var connectionKey = rowClass.GetCustomAttribute <ConnectionKeyAttribute>().Value;
var TableComparisonInfo = new TableComparisonInfo
{
ConnectionKey = connectionKey,
RowClassName = rowClass.Name,
TableName = row.Table
};
TableComparisonInfos.Add(TableComparisonInfo);
using (var connection = SqlConnections.NewByKey(connectionKey))
{
var dialect = connection.GetDialect();
var schemaProvider = SchemaHelper.GetSchemaProvider(dialect.ServerType);
var tableName = SchemaHelper.GetTableNameOnly(row.Table);
string schema = SchemaHelper.GetSchemaName(row.Table);
var dbFields = schemaProvider.GetFieldInfos(connection, schema, tableName);
if (dbFields == null || dbFields.Count() == 0)
{
TableComparisonInfo.Issues += $"Unable to retrive Table info from Database. ";
}
else
{
for (int i = 0; i < row.FieldCount; i++)
{
Field rowfield = rowFields[i];
if (EntityFieldExtensions.IsTableField(rowfield))
{
var dbField = dbFields.FirstOrDefault(f => f.FieldName == rowfield.Name);
var FieldComparisonInfo = new FieldComparisonInfo {
RowField = rowfield, DBField = dbField
};
TableComparisonInfo.FieldComparisonInfos.Add(FieldComparisonInfo);
}
}
//query test
try
{
var query = new SqlQuery()
.From(rowFields)
.SelectTableFields(row)
.SelectForeignFields(row)
.Take(1);
connection.Query(query);
}
catch (Exception ex) { TableComparisonInfo.Issues += " Error while executing query: " + ex.Message; }
}
}
}
#endregion
#region Form
//Issues.Add("---------------------------");
//Issues.Add("---------------------------");
//var formClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute<FormScriptAttribute>() != null && w.GetCustomAttribute<BasedOnRowAttribute>() != null);
//foreach (var formClass in formClasses)
//{
// var basedOnRowType = formClass.GetCustomAttribute<BasedOnRowAttribute>().RowType;
// Row basedOnRow = (Row)Activator.CreateInstance(basedOnRowType);
// var rowFields = basedOnRow.GetFields();
// for (int i = 0; i < basedOnRow.FieldCount; i++)
// {
// Field rowfield = rowFields[i];
// if (rowfield.Flags.HasFlag(FieldFlags.NotNull) && !rowfield.Flags.HasFlag(FieldFlags.Identity))
// {
// var propInfo = formClass.GetProperty(rowfield.PropertyName);
// if (propInfo == null)
// {
// Issues.Add($"{Issues.Count + 1}. {rowfield.Type} {rowfield.PropertyName} is [NotNull] in {basedOnRowType.Name} but not in {formClass.Name}");
// }
// }
// }
//}
#endregion
}
static void Main(string[] args)
{
var assembly = Assembly.GetAssembly(typeof(Serene.Northwind.Entities.CategoryRow));
var rowClasses = assembly.GetTypes().Where(w => w.GetCustomAttribute <TableNameAttribute>() != null);
using (var connection = SqlConnections.New(@"Data Source=(LocalDb)\MSSqlLocalDB; Initial Catalog=Serene_Northwind_v1; Integrated Security=True", "System.Data.SqlClient"))
{
connection.Open();
var schemaProvider = SchemaHelper.GetSchemaProvider(connection.GetDialect().ServerType);
foreach (var rowClass in rowClasses)
{
Row row = (Row)Activator.CreateInstance(rowClass);
Console.WriteLine(row.Table);
var tableName = row.Table;
string schema = null;
if (tableName.IndexOf('.') > 0)
{
schema = tableName.Substring(0, tableName.IndexOf('.'));
tableName = tableName.Substring(tableName.IndexOf('.') + 1);
}
var rowFields = row.GetFields();
var dbFields = schemaProvider.GetFieldInfos(connection, schema ?? "dbo", tableName);
for (int i = 0; i < row.FieldCount; i++)
{
Field rowfield = rowFields[i];
if (EntityFieldExtensions.IsTableField(rowfield))
{
var dbField = dbFields.FirstOrDefault(f => f.FieldName == rowfield.Name);
string strNull = rowfield.Flags.HasFlag(FieldFlags.NotNull) ? "NOT NULL" : "NULL";
if (dbField == null)
{
Console.WriteLine($"\t{rowfield.Type} {rowfield.Name} {strNull} \t\t no corresponding field in database!");
}
else
{
string strTypeMismatch = rowfield.Type.ToString() == SchemaHelper.SqlTypeNameToFieldType(dbField.DataType, dbField.Size) ?
"" : "datatype mismatch";
string strNullableMismatch = rowfield.Flags.HasFlag(FieldFlags.NotNull) == !dbField.IsNullable ?
"" : "nullable mismatch";
Console.WriteLine($"\t{rowfield.Type} {rowfield.Name} {strNull}"
+ $"\t\t {strTypeMismatch}\t{strNullableMismatch}");
}
}
}
Console.WriteLine();
}
connection.Close();
}
Console.ReadKey();
}
