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); }