public static nHydrate.DataImport.SQLObject ToDatabaseObject(this StoredProcedure item)
{
var retval = new nHydrate.DataImport.StoredProc();
retval.Name = item.Name;
retval.Schema = item.Schema;
retval.SQL = item.SQL;
//Fields
foreach (var f in item.Fields)
{
retval.FieldList.Add(new nHydrate.DataImport.Field()
{
DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), f.DataType.ToString(), true),
DefaultValue = f.Default,
Length = f.Length,
Name = f.Name,
Nullable = f.Nullable,
Scale = f.Scale,
});
}
//Parameters
foreach (var p in item.Parameters)
{
retval.ParameterList.Add(new nHydrate.DataImport.Parameter()
{
DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), p.DataType.ToString(), true),
DefaultValue = p.Default,
Length = p.Length,
Name = p.Name,
Nullable = p.Nullable,
Scale = p.Scale,
IsOutputParameter = p.IsOutputParameter,
});
}
return(retval);
}
public static string GetChangedText(this nHydrate.DataImport.StoredProc item, nHydrate.DataImport.StoredProc target)
{
var retval = string.Empty;
if (item.Collate != target.Collate)
{
retval += "Collate: " + item.Collate + "->" + target.Collate + "\r\n";
}
if (item.Name != target.Name)
{
retval += "Name: " + item.Name + "->" + target.Name + "\r\n";
}
if (item.Schema != target.Schema)
{
retval += "Schema: " + item.Schema + "->" + target.Schema + "\r\n";
}
if (item.SQL != target.SQL)
{
retval += "Original SQL\r\n" + item.SQL + "\r\n\r\nNew SQL\r\n" + target.SQL + "\r\n";
}
#region Parameters
var addedParameters = target.ParameterList.Where(x => !item.ParameterList.Select(z => z.Name).Contains(x.Name)).ToList();
var deletedParameters = item.ParameterList.Where(x => !target.ParameterList.Select(z => z.Name).Contains(x.Name)).ToList();
var commonParameters = item.ParameterList.Where(x => target.ParameterList.Select(z => z.Name).Contains(x.Name)).ToList();
if (addedParameters.Count > 0)
{
retval += "Added Parameters: " + string.Join(", ", addedParameters.Select(x => x.Name).OrderBy(x => x).ToList()) + "\r\n";
}
if (deletedParameters.Count > 0)
{
retval += "Deleted Parameters: " + string.Join(", ", deletedParameters.Select(x => x.Name).OrderBy(x => x).ToList()) + "\r\n";
}
foreach (var parameter in commonParameters)
{
var t = parameter.GetChangedText(target.ParameterList.FirstOrDefault(x => x.Name == parameter.Name));
if (!string.IsNullOrEmpty(t))
{
retval += "Changed Parameter (" + parameter.Name + ")\r\n" + t + "\r\n";
}
}
#endregion
#region Fields
var addedFields = target.FieldList.Where(x => !item.FieldList.Select(z => z.Name).Contains(x.Name)).ToList();
var deletedFields = item.FieldList.Where(x => !target.FieldList.Select(z => z.Name).Contains(x.Name)).ToList();
var commonFields = item.FieldList.Where(x => target.FieldList.Select(z => z.Name).Contains(x.Name)).ToList();
if (addedFields.Count > 0)
{
retval += "\r\nAdded fields: " + string.Join(", ", addedFields.Select(x => x.Name).OrderBy(x => x).ToList()) + "\r\n";
}
if (deletedFields.Count > 0)
{
retval += "\r\nDeleted fields: " + string.Join(", ", deletedFields.Select(x => x.Name).OrderBy(x => x).ToList()) + "\r\n";
}
foreach (var field in commonFields)
{
var t = field.GetChangedText(target.FieldList.FirstOrDefault(x => x.Name == field.Name));
if (!string.IsNullOrEmpty(t))
{
retval += "Changed field (" + field.Name + ")\r\n" + t + "\r\n";
}
}
#endregion
return(retval);
}
/// <summary>
/// Convert a new DSL model into a data import model
/// </summary>
/// <param name="model"></param>
/// <param name="diagram"></param>
/// <returns></returns>
public static nHydrate.DataImport.Database Convert(nHydrateModel model, Microsoft.VisualStudio.Modeling.Diagrams.Diagram diagram)
{
var database = new nHydrate.DataImport.Database();
database.Collate = model.Collate;
#region Load the entities
foreach (var entity in model.Entities.Where(x => x.IsGenerated))
{
var newEntity = new nHydrate.DataImport.Entity();
newEntity.ID = entity.Id;
newEntity.Name = entity.Name;
newEntity.Schema = entity.Schema;
newEntity.AllowCreateAudit = entity.AllowCreateAudit;
newEntity.AllowModifyAudit = entity.AllowModifyAudit;
newEntity.AllowTimestamp = entity.AllowTimestamp;
newEntity.IsTenant = entity.IsTenant;
database.EntityList.Add(newEntity);
#region Load the fields
foreach (var field in entity.Fields)
{
var newField = new nHydrate.DataImport.Field();
newField.ID = field.Id;
newField.Nullable = field.Nullable;
newField.Collate = field.Collate;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.DefaultValue = field.Default;
newField.Identity = (field.Identity == IdentityTypeConstants.Database);
newField.IsIndexed = field.IsIndexed;
newField.IsReadOnly = field.IsReadOnly;
newField.Length = field.Length;
newField.Name = field.Name;
newField.PrimaryKey = field.IsPrimaryKey;
newField.Scale = field.Scale;
newField.IsBrowsable = field.IsBrowsable;
newEntity.FieldList.Add(newField);
}
#endregion
}
#endregion
#region Load Relations
foreach (var shape in diagram.NestedChildShapes)
{
if (shape is EntityAssociationConnector)
{
var connector = shape as EntityAssociationConnector;
var parent = connector.FromShape.ModelElement as Entity;
var child = connector.ToShape.ModelElement as Entity;
var relation = connector.ModelElement as EntityHasEntities;
var parentTable = database.EntityList.FirstOrDefault(x => parent != null && x.Name == parent.Name);
var childTable = database.EntityList.FirstOrDefault(x => child != null && x.Name == child.Name);
//If we found both parent and child tables...
if (parentTable != null && childTable != null)
{
var newRelation = new nHydrate.DataImport.Relationship();
newRelation.ID = shape.Id;
newRelation.TargetEntity = childTable;
newRelation.RoleName = ((EntityHasEntities)connector.ModelElement).RoleName;
newRelation.SourceEntity = parentTable;
parentTable.RelationshipList.Add(newRelation);
//Create the column links
var fieldList = model.RelationFields.Where(x => relation != null && x.RelationID == relation.Id);
foreach (var columnSet in fieldList)
{
var field1 = parent.Fields.FirstOrDefault(x => x.Id == columnSet.SourceFieldId);
var field2 = child.Fields.FirstOrDefault(x => x.Id == columnSet.TargetFieldId);
var column1 = parentTable.FieldList.FirstOrDefault(x => field1 != null && x.Name == field1.Name);
var column2 = childTable.FieldList.FirstOrDefault(x => field2 != null && x.Name == field2.Name);
newRelation.RelationshipColumnList.Add(new nHydrate.DataImport.RelationshipDetail()
{
ParentField = column1,
ChildField = column2,
}
);
}
}
}
}
#endregion
#region Load Stored Procedures
foreach (var storedProc in model.StoredProcedures.Where(x => x.IsGenerated))
{
var newStoredProc = new nHydrate.DataImport.StoredProc();
newStoredProc.ID = storedProc.Id;
newStoredProc.Name = storedProc.Name;
newStoredProc.Schema = storedProc.Schema;
newStoredProc.SQL = storedProc.SQL;
database.StoredProcList.Add(newStoredProc);
//Load the fields
foreach (var field in storedProc.Fields)
{
var newField = new nHydrate.DataImport.Field();
newField.ID = field.Id;
newField.Nullable = field.Nullable;
//newField.Collate = field.Collate;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.DefaultValue = field.Default;
//newField.Identity = (field.Identity == IdentityTypeConstants.Database);
//newField.IsIndexed = field.IsIndexed;
newField.Length = field.Length;
newField.Name = field.Name;
//newField.PrimaryKey = field.IsPrimaryKey;
newField.Scale = field.Scale;
newStoredProc.FieldList.Add(newField);
}
//Load the parameters
foreach (var parameter in storedProc.Parameters)
{
var newParameter = new nHydrate.DataImport.Parameter();
newParameter.ID = parameter.Id;
newParameter.Nullable = parameter.Nullable;
//newField.Collate = field.Collate;
newParameter.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), parameter.DataType.ToString());
newParameter.DefaultValue = parameter.Default;
//newField.Identity = (field.Identity == IdentityTypeConstants.Database);
//newField.IsIndexed = field.IsIndexed;
newParameter.Length = parameter.Length;
newParameter.Name = parameter.Name;
//newField.PrimaryKey = field.IsPrimaryKey;
newParameter.Scale = parameter.Scale;
newStoredProc.ParameterList.Add(newParameter);
}
}
#endregion
#region Load Views
foreach (var view in model.Views.Where(x => x.IsGenerated))
{
var newView = new nHydrate.DataImport.View();
newView.ID = view.Id;
newView.Name = view.Name;
newView.Schema = view.Schema;
newView.SQL = view.SQL;
database.ViewList.Add(newView);
//Load the fields
foreach (var field in view.Fields)
{
var newField = new nHydrate.DataImport.Field();
newField.ID = field.Id;
newField.Nullable = field.Nullable;
//newField.Collate = field.Collate;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.DefaultValue = field.Default;
//newField.Identity = (field.Identity == IdentityTypeConstants.Database);
//newField.IsIndexed = field.IsIndexed;
newField.Length = field.Length;
newField.Name = field.Name;
//newField.PrimaryKey = field.IsPrimaryKey;
newField.Scale = field.Scale;
newView.FieldList.Add(newField);
}
}
#endregion
#region Load Functions
foreach (var function in model.Functions.Where(x => x.IsGenerated))
{
var newFunction = new nHydrate.DataImport.Function();
newFunction.ID = function.Id;
newFunction.Name = function.Name;
newFunction.Schema = function.Schema;
newFunction.SQL = function.SQL;
database.FunctionList.Add(newFunction);
//Load the fields
foreach (var field in function.Fields)
{
var newField = new nHydrate.DataImport.Field();
newField.ID = field.Id;
newField.Nullable = field.Nullable;
//newField.Collate = field.Collate;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.DefaultValue = field.Default;
//newField.Identity = (field.Identity == IdentityTypeConstants.Database);
//newField.IsIndexed = field.IsIndexed;
newField.Length = field.Length;
newField.Name = field.Name;
//newField.PrimaryKey = field.IsPrimaryKey;
newField.Scale = field.Scale;
newFunction.FieldList.Add(newField);
}
//Load the parameters
foreach (var parameter in function.Parameters)
{
var newParameter = new nHydrate.DataImport.Parameter();
newParameter.ID = parameter.Id;
newParameter.Nullable = parameter.Nullable;
//newField.Collate = field.Collate;
newParameter.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), parameter.DataType.ToString());
newParameter.DefaultValue = parameter.Default;
//newField.Identity = (field.Identity == IdentityTypeConstants.Database);
//newField.IsIndexed = field.IsIndexed;
newParameter.Length = parameter.Length;
newParameter.Name = parameter.Name;
//newField.PrimaryKey = field.IsPrimaryKey;
newParameter.Scale = parameter.Scale;
newFunction.ParameterList.Add(newParameter);
}
}
#endregion
return database;
}
