| static private GetSqlIndexesForTable ( ) : string | ||
| return | string | |
public Entity GetEntity(string connectionString, string name, IEnumerable <SpecialField> auditFields)
{
var database = new Database();
#region Load Entities
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlDatabaseTables()))
{
while (tableReader.Read())
{
var newEntity = new Entity();
newEntity.Name = tableReader["name"].ToString();
if (newEntity.Name.Match(name)) //Only the specified item
{
database.EntityList.Add(newEntity);
}
newEntity.Schema = tableReader["schema"].ToString();
}
}
#endregion
#region Load Entity Fields
using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForTable(name)))
{
while (columnReader.Read())
{
var columnName = columnReader["columnName"].ToString();
var tableName = columnReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
//Ensure the field name is not an Audit field
if (entity != null && !auditFields.Any(x => x.Name.ToLower() == columnName.ToLower()))
{
var maxSortOrder = 0;
if (entity.FieldList.Count > 0)
{
maxSortOrder = entity.FieldList.Max(x => x.SortOrder);
}
var newColumn = new Field()
{
Name = columnName, SortOrder = ++maxSortOrder
};
entity.FieldList.Add(newColumn);
newColumn.Nullable = (int)columnReader["allow_null"] == 1;
if (bool.Parse(columnReader["is_identity"].ToString()))
{
newColumn.Identity = true;
}
if (columnReader["isPrimaryKey"] != System.DBNull.Value)
{
newColumn.PrimaryKey = true;
}
try
{
newColumn.DataType = DatabaseHelper.GetSQLDataType(columnReader["system_type_id"].ToString(), database.UserDefinedTypes);
}
catch { }
var defaultvalue = columnReader["default_value"].ToString();
SetupDefault(newColumn, defaultvalue);
newColumn.Length = (int)columnReader["max_length"];
//Decimals are a little different
if (newColumn.DataType == SqlDbType.Decimal)
{
newColumn.Length = (byte)columnReader["precision"];
newColumn.Scale = (int)columnReader["scale"];
}
}
else if (entity != null)
{
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.CreatedDate ||
x.Type == SpecialFieldTypeConstants.CreatedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowCreateAudit = true;
}
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.ModifiedDate ||
x.Type == SpecialFieldTypeConstants.ModifiedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowModifyAudit = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Timestamp &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowTimestamp = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Tenant &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.IsTenant = true;
}
}
}
}
#endregion
#region Load Entity Fields Extra
using (var indexReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlIndexesForTable()))
{
while (indexReader.Read())
{
var indexName = indexReader["indexname"].ToString();
var columnName = indexReader["columnname"].ToString();
var tableName = indexReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
if (entity != null)
{
var pk = bool.Parse(indexReader["is_primary_key"].ToString());
var column = entity.FieldList.FirstOrDefault(x => x.Name == columnName);
if (column != null && !pk)
{
column.IsIndexed = true;
}
}
}
}
#endregion
LoadIndexes(database, connectionString);
LoadUniqueFields(database, connectionString);
return(database.EntityList.FirstOrDefault());
}
public Database Import(string connectionString, IEnumerable <SpecialField> auditFields)
{
try
{
var database = new Database();
#region Load user defined types
LoadUdts(database, connectionString);
#endregion
#region Load Entities
this.ProgressText = "Loading Entities...";
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlDatabaseTables()))
{
while (tableReader.Read())
{
var newEntity = new Entity();
newEntity.Name = tableReader["name"].ToString();
database.EntityList.Add(newEntity);
newEntity.Schema = tableReader["schema"].ToString();
}
}
#endregion
#region Load Entity Fields
using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForTable()))
{
while (columnReader.Read())
{
var columnName = columnReader["columnName"].ToString();
var tableName = columnReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
//Ensure the field name is not an Audit field
if (entity != null && !auditFields.Any(x => x.Name.Match(columnName)))
{
var maxSortOrder = 0;
if (entity.FieldList.Count > 0)
{
maxSortOrder = entity.FieldList.Max(x => x.SortOrder);
}
var newColumn = new Field()
{
Name = columnName, SortOrder = ++maxSortOrder
};
entity.FieldList.Add(newColumn);
newColumn.Nullable = (int)columnReader["allow_null"] == 1;
if ((int)columnReader["is_identity"] == 1)
{
newColumn.Identity = true;
}
if (columnReader["isPrimaryKey"] != System.DBNull.Value)
{
newColumn.PrimaryKey = true;
}
try
{
newColumn.DataType = DatabaseHelper.GetSQLDataType(columnReader["system_type_id"].ToString(), database.UserDefinedTypes);
}
catch { }
var defaultvalue = columnReader["default_value"].ToString();
SetupDefault(newColumn, defaultvalue);
//newColumn.ImportedDefaultName = "";
newColumn.Length = (int)columnReader["max_length"];
//Decimals are a little different
if (newColumn.DataType == SqlDbType.Decimal)
{
newColumn.Length = (byte)columnReader["precision"];
newColumn.Scale = (int)columnReader["scale"];
}
}
else if (entity != null)
{
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.CreatedDate ||
x.Type == SpecialFieldTypeConstants.CreatedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowCreateAudit = true;
}
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.ModifiedDate ||
x.Type == SpecialFieldTypeConstants.ModifiedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowModifyAudit = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Timestamp &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowTimestamp = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Tenant &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.IsTenant = true;
}
}
}
}
using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForComputed()))
{
while (columnReader.Read())
{
var tableName = columnReader["tableName"].ToString();
var columnName = columnReader["columnName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
if (entity != null)
{
var column = entity.FieldList.FirstOrDefault(x => x.Name.ToLower() == columnName.ToLower());
if (column != null)
{
column.IsComputed = true;
column.Formula = columnReader["definition"].ToString();
}
}
}
}
#endregion
#region Load Entity Indexes
using (var indexReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlIndexesForTable()))
{
while (indexReader.Read())
{
var indexName = indexReader["indexname"].ToString();
var columnName = indexReader["columnname"].ToString();
var tableName = indexReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
if (entity != null)
{
var pk = bool.Parse(indexReader["is_primary_key"].ToString());
var column = entity.FieldList.FirstOrDefault(x => x.Name == columnName);
if (column != null && !pk)
{
column.IsIndexed = true;
}
}
}
}
#endregion
#region Load Relations
var dsRelationship = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForRelationships());
foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows)
{
var constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString();
var parentTableName = (string)rowRelationship["UQ_TABLE_NAME"];
var childTableName = (string)rowRelationship["FK_TABLE_NAME"];
var parentTable = database.EntityList.FirstOrDefault(x => x.Name == parentTableName);
var childTable = database.EntityList.FirstOrDefault(x => x.Name == childTableName);
if (parentTable != null && childTable != null)
{
Relationship newRelation = null;
var isAdd = false;
if (database.RelationshipList.Count(x => x.ConstraintName == constraintName) == 0)
{
newRelation = new Relationship();
if (rowRelationship["object_id"] != System.DBNull.Value)
{
newRelation.ImportData = rowRelationship["object_id"].ToString();
}
newRelation.SourceEntity = parentTable;
newRelation.TargetEntity = childTable;
newRelation.ConstraintName = constraintName;
var search = ("_" + childTable.Name + "_" + parentTable.Name).ToLower();
var roleName = constraintName.ToLower().Replace(search, string.Empty);
if (roleName.Length >= 3)
{
roleName = roleName.Remove(0, 3);
}
var v = roleName.ToLower();
if (v != "fk")
{
newRelation.RoleName = v;
}
isAdd = true;
}
else
{
newRelation = database.RelationshipList.First(x => x.ConstraintName == constraintName);
}
//add the column relationship to the relation
var columnRelationship = new RelationshipDetail();
var parentColumnName = (string)rowRelationship["UQ_COLUMN_NAME"];
var childColumnName = (string)rowRelationship["FK_COLUMN_NAME"];
if (parentTable.FieldList.Count(x => x.Name == parentColumnName) == 1 && (childTable.FieldList.Count(x => x.Name == childColumnName) == 1))
{
columnRelationship.ParentField = parentTable.FieldList.First(x => x.Name == parentColumnName);
columnRelationship.ChildField = childTable.FieldList.First(x => x.Name == childColumnName);
newRelation.RelationshipColumnList.Add(columnRelationship);
//ONLY ADD THIS RELATION IF ALL WENT WELL
if (isAdd)
{
parentTable.RelationshipList.Add(newRelation);
}
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
}
}
#endregion
#region Load Views
this.ProgressText = "Loading Views...";
LoadViews(database, connectionString);
#endregion
#region Load Indexes
this.ProgressText = "Loading Indexes...";
LoadIndexes(database, connectionString);
#endregion
LoadUniqueFields(database, connectionString);
return(database);
}
catch (Exception ex /*ignored*/)
{
throw;
}
finally
{
this.ProgressText = string.Empty;
this.ProgressValue = 0;
}
}