public void CreateFromDir(bool overwrite, Action<TraceLevel, string> log = null) { if (log == null) log = (tl, s) => { }; if (DBHelper.DbExists(Connection)) { log(TraceLevel.Verbose, "Dropping existing database..."); DBHelper.DropDb(Connection); log(TraceLevel.Verbose, "Existing database dropped."); } log(TraceLevel.Info, "Creating database..."); //create database DBHelper.CreateDb(Connection); //run scripts if (File.Exists(Dir + "/props.sql")) { log(TraceLevel.Verbose, "Setting database properties..."); try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/props.sql")); } catch (SqlBatchException ex) { throw new SqlFileException(Dir + "/props.sql", ex); } // COLLATE can cause connection to be reset // so clear the pool so we get a new connection DBHelper.ClearPool(Connection); } if (File.Exists(Dir + "/schemas.sql")) { log(TraceLevel.Verbose, "Creating database schemas..."); try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/schemas.sql")); } catch (SqlBatchException ex) { throw new SqlFileException(Dir + "/schemas.sql", ex); } } log(TraceLevel.Info, "Creating database objects..."); // create db objects // resolve dependencies by trying over and over // if the number of failures stops decreasing then give up var scripts = GetScripts(); var errors = new List<SqlFileException>(); var prevCount = -1; while (scripts.Count > 0 && (prevCount == -1 || errors.Count < prevCount)) { if (errors.Count > 0) { prevCount = errors.Count; log(TraceLevel.Info, string.Format( "{0} errors occurred, retrying...", errors.Count)); } errors.Clear(); var index = 0; var total = scripts.Count; foreach (var f in scripts.ToArray()) { log(TraceLevel.Verbose, string.Format("Executing script {0} of {1}...{2}", ++index, total, index < total ? "\r" : string.Empty)); try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(f)); scripts.Remove(f); } catch (SqlBatchException ex) { errors.Add(new SqlFileException(f, ex)); //Console.WriteLine("Error occurred in {0}: {1}", f, ex); } } } if (prevCount > 0) log(TraceLevel.Info, errors.Any() ? string.Format("{0} errors unresolved. Details will follow later.", prevCount) : "All errors resolved, were probably dependency issues..."); log(TraceLevel.Info, string.Empty); ImportData(log); // load data if (Directory.Exists(Dir + "/after_data")) { log(TraceLevel.Verbose, "Executing after-data scripts..."); foreach (var f in Directory.GetFiles(Dir + "/after_data", "*.sql")) { try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(f)); } catch (SqlBatchException ex) { errors.Add(new SqlFileException(f, ex)); } } } // foreign keys if (Directory.Exists(Dir + "/foreign_keys")) { log(TraceLevel.Info, "Adding foreign key constraints..."); foreach (var f in Directory.GetFiles(Dir + "/foreign_keys", "*.sql")) { try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(f)); } catch (SqlBatchException ex) { //throw new SqlFileException(f, ex); errors.Add(new SqlFileException(f, ex)); } } } if (errors.Count > 0) { var ex = new BatchSqlFileException(); ex.Exceptions = errors; throw ex; } }
public void CreateFromDir(bool overwrite) { if (DBHelper.DbExists(Connection)) { DBHelper.DropDb(Connection); } Console.WriteLine("Creating database..."); //create database DBHelper.CreateDb(Connection); //run scripts if (File.Exists(Dir + "/props.sql")) { try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/props.sql")); } catch (SqlBatchException ex) { throw new SqlFileException(Dir + "/props.sql", ex); } // COLLATE can cause connection to be reset // so clear the pool so we get a new connection DBHelper.ClearPool(Connection); } if (File.Exists(Dir + "/schemas.sql")) { try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/schemas.sql")); } catch (SqlBatchException ex) { throw new SqlFileException(Dir + "/schemas.sql", ex); } } Console.WriteLine("Creating database objects..."); // create db objects // resolve dependencies by trying over and over // if the number of failures stops decreasing then give up List<string> scripts = GetScripts(); var errors = new List<SqlFileException>(); int prevCount = -1; while (scripts.Count > 0 && (prevCount == -1 || errors.Count < prevCount)) { if (errors.Count > 0) { prevCount = errors.Count; Console.WriteLine( "{0} errors occurred, retrying...", errors.Count); } errors.Clear(); foreach (string f in scripts.ToArray()) { try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(f)); scripts.Remove(f); } catch (SqlBatchException ex) { errors.Add(new SqlFileException(f, ex)); //Console.WriteLine("Error occurred in {0}: {1}", f, ex); } } } if (!errors.Any() && prevCount > 0) Console.WriteLine("All errors resolved, were probably dependency issues..."); Console.WriteLine(); Load(); // load the schema first so we can import data Console.WriteLine("Importing data..."); ImportData(); // load data Console.WriteLine("Data imported successfully."); if (Directory.Exists(Dir + "/after_data")) { foreach (string f in Directory.GetFiles(Dir + "/after_data", "*.sql")) { try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(f)); } catch (SqlBatchException ex) { errors.Add(new SqlFileException(f, ex)); } } } Console.WriteLine("Adding foreign key constraints..."); // foreign keys if (Directory.Exists(Dir + "/foreign_keys")) { foreach (string f in Directory.GetFiles(Dir + "/foreign_keys", "*.sql")) { try { DBHelper.ExecBatchSql(Connection, File.ReadAllText(f)); } catch (SqlBatchException ex) { //throw new SqlFileException(f, ex); errors.Add(new SqlFileException(f, ex)); } } } if (errors.Count > 0) { var ex = new BatchSqlFileException(); ex.Exceptions = errors; throw ex; } }