System
System.Collections.Generic
System.Collections.Specialized
System.Data
System.Data.SqlTypes
System.Globalization
System.IO
System.Linq
System.Net
System.Security.Cryptography
System.Text
System.Threading.Tasks
void Main()
{
// Create a new instance of the library with the data context to use.
var schemaUpdater = new SchemaUpdater(Util.CurrentDataContext);
// Link the classes to tables.
schemaUpdater.AddEntity(typeof(Account), typeof(Accounts));
// Commit any updates to the database.
schemaUpdater.SubmitUpdates();
}
// The conceptual class to modify.
class Account
{
public Guid Id { get; set; }
public string Name { get; set; }
}
class Award
{
public Guid Id { get; set; }
public Guid AccountId { get; set; }
public string AwardName { get; set; }
}
// The code supporting the schema evolution.
#region Classes
class SchemaUpdater
{
private List trackedEntities;
private DataContext dataContext;
private static Dictionary SQLTypeMap = new Dictionary
{
{ typeof(string), "VARCHAR(255)" },
{ typeof(Guid), "UNIQUEIDENTIFIER" },
{ typeof(bool), "BIT" },
{ typeof(int), "INT" },
{ typeof(float), "FLOAT" },
{ typeof(DateTime), "DATETIME" }
};
public SchemaUpdater(DataContext dataContext)
{
this.dataContext = dataContext;
this.trackedEntities = new List();
}
public SchemaUpdater AddEntity(Type conceptualType, Type persistantType)
{
this.trackedEntities.Add(new EntityPair(conceptualType, persistantType));
return this;
}
public SchemaUpdater SubmitUpdates()
{
"Beginning update...".Dump();
this.trackedEntities.ForEach(e => UpdateEntity(e));
"\nUpdate complete.".Dump();
return this;
}
private void UpdateEntity(EntityPair entityPair)
{
string.Format("\n[{0}]:\n", entityPair.PersistantEntity.Name).Dump();
// Generate a list of their fields.
var conceptualFields = entityPair.ConceptualEntity.GetProperties().Select(p => new Field(p.Name, p.PropertyType));
var persistantFields = entityPair.PersistantEntity.GetFields().Select(f => new Field(f.Name, f.FieldType));
// Create some useful models.
Relation conceptual = new Relation("Conceptual", conceptualFields);
Relation persistant = new Relation("Persistant", persistantFields);
// Compare the two list of fields.
var missingColumns = conceptual.Diff(persistant);
if (missingColumns.Count() != 0)
{
foreach (var column in missingColumns)
{
this.CreateColumn(entityPair.PersistantEntity.Name, column.Name, SQLTypeMap[column.FieldType]);
string.Format("* Added new column [{0}].[{1}] of type {2}.", entityPair.PersistantEntity.Name, column.Name, SQLTypeMap[column.FieldType]).Dump();
}
}
else
{
"* No columns to add.".Dump();
}
// Identify which columns can be deleted.
persistant.Diff(conceptual).ForEach(c => string.Format("* Column '{0}' can be deleted.", c.Name).Dump());
}
private void CreateColumn(string tableName, string columnName, string sqlType)
{
string sqlStatment = string.Format(@"ALTER TABLE [{0}] ADD [{1}] {2};", tableName, columnName, sqlType);
this.RunSQL(sqlStatment);
}
private void RunSQL(string sqlStatment)
{
this.dataContext.ExecuteCommand(sqlStatment);
}
}
class Relation
{
public string Name { get; set; }
public IEnumerable Fields { get; set; }
public Relation (String name, IEnumerable fields)
{
this.Name = name;
this.Fields = fields;
}
public IEnumerable Diff (Relation input)
{
return this.Fields.Where(f => input.Fields.Contains(f) == false);
}
}
class Field : IEquatable
{
public string Name { get; set; }
public Type FieldType { get; set; }
public Field (String name, Type fieldType)
{
this.Name = name;
this.FieldType = fieldType;
}
// Fields are equal if their names and field types are equal.
public bool Equals (Field field)
{
// Check whether the fields properties are equal.
return this.Name == field.Name && this.FieldType == field.FieldType;
}
}
#endregion
#region Structs
public struct EntityPair
{
public Type ConceptualEntity;
public Type PersistantEntity;
public EntityPair(Type conceptualType, Type persistantType)
{
ConceptualEntity = conceptualType;
PersistantEntity = persistantType;
}
}
#endregion