public IsValidResponse Validate()
{
try
{
var service = new SqlRecordService(this);
return(service.VerifyConnection());
}
catch (Exception ex)
{
var response = new IsValidResponse();
response.AddInvalidReason("Error creating service: " + ex.DisplayString());
return(response);
}
}
public void RefreshSource()
{
//the nonmetadata sql service checks fields in database instead of metadata
var nonMetaDataService = new SqlRecordService(Settings);
if (!DatabaseExists())
{
CreateDatabase(Settings);
}
DropAllTriggers(RecordMetadata.Select(m => m.SchemaName));
DropAllForeignKeyConstraints(RecordMetadata.Select(m => m.SchemaName));
DropAllIndexes(RecordMetadata.Select(m => m.SchemaName));
//create tables with fields
foreach (var type in RecordMetadata)
{
if (!nonMetaDataService.RecordTypeExists(type.SchemaName))
{
nonMetaDataService.CreateOrUpdate(type);
}
foreach (var field in type.Fields)
{
nonMetaDataService.CreateOrUpdate(field, type.SchemaName);
}
}
//PRIMARY KEY INDEX
foreach (var type in RecordMetadata)
{
var primaryKey = this.GetPrimaryKey(type.SchemaName);
if (!primaryKey.IsNullOrWhiteSpace())
{
var sql =
string.Format(
"CREATE UNIQUE CLUSTERED INDEX [idx_{0}_Clustered] ON {0} ( {1} ASC )", type.SchemaName, primaryKey);
ExecuteSql(sql);
}
}
//FOREIGN KEYS
foreach (var type in RecordMetadata)
{
foreach (var field in type.Fields.Where(f => f is LookupFieldMetadata).Cast <LookupFieldMetadata>())
{
var foreignKeyName = string.Format("FK_{0}_{1}", type.SchemaName, field.SchemaName);
var sql = new StringBuilder();
sql.AppendLine(
string.Format(
"ALTER TABLE {0} WITH CHECK ADD CONSTRAINT {1} FOREIGN KEY({2})"
, ToIdentifier(type.SchemaName), ToIdentifier(foreignKeyName), ToIdentifier(field.SchemaName)));
sql.AppendLine(string.Format("REFERENCES {0} ({1})", ToIdentifier(field.ReferencedRecordType),
ToIdentifier(this.GetPrimaryKey(field.ReferencedRecordType))));
ExecuteSql(sql.ToString());
var postSql =
string.Format(
"ALTER TABLE {0} CHECK CONSTRAINT {1}",
ToIdentifier(type.SchemaName), ToIdentifier(foreignKeyName));
ExecuteSql(postSql);
}
}
//DELETE TRIGGERS
foreach (var type in RecordMetadata)
{
var keyTypes = RecordMetadata
.ToDictionary(r => r.SchemaName, r => r.Fields
.Where(f => f is LookupFieldMetadata)
.Cast <LookupFieldMetadata>()
.Where(f => f.ReferencedRecordType == type.SchemaName)
.Where(f => f.OnDeleteBehaviour != CascadeBehaviour.None))
.ToArray();
if (keyTypes.SelectMany(r => r.Value).Any())
{
var sql = new StringBuilder();
sql.AppendLine(string.Format("CREATE TRIGGER [trg_{0}_Delete]", type.SchemaName));
sql.AppendLine(string.Format("ON {0}", ToIdentifier(type.SchemaName)));
sql.AppendLine(string.Format("INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON;"));
foreach (var keyType in keyTypes)
{
foreach (var key in keyType.Value)
{
if (key.OnDeleteBehaviour == CascadeBehaviour.Cascade)
{
sql.AppendLine(string.Format("DELETE FROM {0} WHERE {1} IN (SELECT Id FROM DELETED)",
ToIdentifier(keyType.Key), ToIdentifier(key.SchemaName)));
}
else if (key.OnDeleteBehaviour == CascadeBehaviour.SetNull)
{
sql.AppendLine(string.Format("UPDATE {0} SET {1} = null WHERE {1} IN (SELECT Id FROM DELETED)",
ToIdentifier(keyType.Key), ToIdentifier(key.SchemaName)));
}
}
}
sql.AppendLine(string.Format("DELETE FROM {0} WHERE {1} IN (SELECT Id FROM DELETED)",
ToIdentifier(type.SchemaName), ToIdentifier(this.GetPrimaryKey(type.SchemaName))));
sql.AppendLine("END");
ExecuteSql(sql.ToString());
}
}
}
public IsValidResponse Validate()
{
var service = new SqlRecordService(this);
return(service.VerifyConnection());
}
public static void ExecuteSql(this SqlRecordConnection connection, string sql)
{
var sqlService = new SqlRecordService(connection);
sqlService.ExecuteSql(sql);
}
