private void OnCreateRCADDB() { if (ConnectionValid && _desiredServer != null && !string.IsNullOrEmpty(Database)) { if (LocalDatabases.Contains(Database)) { IMessageVisualizer mesg = Resolve <IMessageVisualizer>(); mesg.Show("Error", string.Format("Error: Database with name {0} exists on selected instance, please select another name or delete the existing database first.", Database), MessageButtons.OK); Database = null; return; } CreatingDB = true; rCADConnection ssisOleConn = new rCADConnection(); ssisOleConn.Database = Database; ssisOleConn.Instance = Instance; ssisOleConn.Host = Host; ssisOleConn.SecurityType = SecurityType.WindowsAuthentication; ssisOleConn.Provider = "SQLNCLI10.1"; if (!UsingSQLExpress) { CreateRCADDBArgs args = new CreateRCADDBArgs() { TargetInstance = _desiredServer, RCADConnectionString = ssisOleConn.BuildConnectionString(), InstallDirectory = _installDirectory, DataDirectory = _dataDirectory, LoadRCADDBPackage = _rCADLoaderSSISPkg, RCADDatabaseName = Database }; StatusMessage = CREATING_RCAD_DB_MESSAGE; _workerDTS.RunWorkerAsync(args); } else { CreateRCADDBArgs args = new CreateRCADDBArgs() { TargetInstance = _desiredServer, RCADDatabaseName = Database, TargetSQLFile = ScriptTargetFile, InstallDirectory = _installDirectory, DataDirectory = _dataDirectory }; StatusMessage = CREATING_RCAD_DB_INSTALLSCRIPT; _workerSQLCMD.RunWorkerAsync(args); } } }
private void BackgroundCreateRCADDB_DTS(object sender, DoWorkEventArgs args) { if (args.Argument == null) { return; } CreateRCADDBArgs createArgs = args.Argument as CreateRCADDBArgs; bool? retValue = null; if (createArgs != null) { if (createArgs.TargetInstance == null || createArgs.RCADDatabaseName == null || createArgs.LoadRCADDBPackage == null || createArgs.InstallDirectory == null || createArgs.RCADConnectionString == null || createArgs.DataDirectory == null) { args.Cancel = true; return; } Database rCADDB = null; try { //Create the new rCAD Database rCADDB = new Database(createArgs.TargetInstance, createArgs.RCADDatabaseName); rCADDB.Create(); //Run the SSIS Package Application ssisRCADLoaderApp = new Application(); SSISEventListener eventListener = new SSISEventListener(); Package ssisRCADLoader = ssisRCADLoaderApp.LoadPackage(createArgs.LoadRCADDBPackage, eventListener); ssisRCADLoader.Variables["rCADInstallDirectory"].Value = createArgs.InstallDirectory; ssisRCADLoader.Variables["rCADConnectionString"].Value = createArgs.RCADConnectionString; ssisRCADLoader.Variables["rCADDataDirectory"].Value = createArgs.DataDirectory; DTSExecResult result = ssisRCADLoader.Execute(null, null, eventListener, null, null); if (result == DTSExecResult.Success) { retValue = true; } else { using (StreamWriter output = new StreamWriter(new FileStream(Path.GetTempPath() + "rCADCreator.ssis_errors.out", FileMode.Create))) { output.Write(eventListener.ErrorLog); output.Flush(); } retValue = false; //rCADDB.Drop(); } } catch { retValue = false; //rCADDB.Drop(); } args.Result = retValue; } }
private void BackgroundCreateRCADDB_SQLCMD(object sender, DoWorkEventArgs args) { if (args.Argument == null) { return; } CreateRCADDBArgs createArgs = args.Argument as CreateRCADDBArgs; bool? retValue = null; if (createArgs != null) { if (createArgs.TargetInstance == null || createArgs.RCADDatabaseName == null || createArgs.InstallDirectory == null || createArgs.DataDirectory == null || createArgs.TargetSQLFile == null) { args.Cancel = true; return; } XDocument creatorXML = null; try { //Create the new rCAD Database Database rCADDB = new Database(createArgs.TargetInstance, createArgs.RCADDatabaseName); rCADDB.Create(); using (var reader = XmlReader.Create(createArgs.InstallDirectory + RCADCREATOR_XMLFILE)) { creatorXML = XDocument.Load(reader); IEnumerable <string> createTablesFiles = from item in creatorXML.Descendants("TableFile") select(createArgs.InstallDirectory + item.Value); IEnumerable <string> createIndicesFiles = from item in creatorXML.Descendants("IndexFile") select(createArgs.InstallDirectory + item.Value); IEnumerable <string> createViewsFiles = from item in creatorXML.Descendants("ViewFile") select(createArgs.InstallDirectory + item.Value); IEnumerable <string> createUDFFiles = from item in creatorXML.Descendants("UDFFile") select(createArgs.InstallDirectory + item.Value); IEnumerable <string> batchOpsFiles = from item in creatorXML.Descendants("QueryFile") select(createArgs.InstallDirectory + item.Value); //Build up rCAD script: using (var targetScriptFile = File.CreateText(createArgs.TargetSQLFile)) { targetScriptFile.WriteLine(@"USE {0};", createArgs.RCADDatabaseName); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); //1. Create rCAD Tables targetScriptFile.WriteLine(@"-- Creating rCAD Tables"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"PRINT '*** Creating rCAD Tables';"); targetScriptFile.WriteLine(@"GO"); foreach (var createTableSQLFile in createTablesFiles) { using (var sql = File.OpenText(createTableSQLFile)) { string line = sql.ReadLine(); while (!string.IsNullOrEmpty(line)) { targetScriptFile.WriteLine(line); line = sql.ReadLine(); } } targetScriptFile.WriteLine(targetScriptFile.NewLine); } //2. Bulk Load Sequence Metadata Lookup Tables targetScriptFile.WriteLine(@"-- Loading CellLocationInfo"); targetScriptFile.WriteLine(@"PRINT '*** Loading CellLocationInfo';"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(@"BULK INSERT CellLocationInfo FROM N'{0}{1}\CellLocationInfo.dat'", createArgs.DataDirectory, SEQUENCE_METADATA_DIR); targetScriptFile.WriteLine(@"WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FORMATFILE=N'{0}{1}\Bulkload.CellLocationInfo.xml');", createArgs.DataDirectory, SEQUENCE_METADATA_DIR); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"-- Loading SequenceType"); targetScriptFile.WriteLine(@"PRINT '*** Loading SequenceType';"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(@"BULK INSERT SequenceType FROM N'{0}{1}\SequenceType.dat'", createArgs.DataDirectory, SEQUENCE_METADATA_DIR); targetScriptFile.WriteLine(@"WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FORMATFILE=N'{0}{1}\Bulkload.SequenceType.xml');", createArgs.DataDirectory, SEQUENCE_METADATA_DIR); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"-- Loading SecondaryStructureExtentTypes"); targetScriptFile.WriteLine(@"PRINT '*** Loading SecondaryStructureExtentTypes';"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(@"BULK INSERT SecondaryStructureExtentTypes FROM N'{0}{1}\SecondaryStructureExtentTypes.dat'", createArgs.DataDirectory, STRUCTURAL_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FORMATFILE=N'{0}{1}\Bulkload.SecondaryStructureExtentTypes.xml');", createArgs.DataDirectory, STRUCTURAL_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); //3. Bulk Load Evolutionary Relationships Tables targetScriptFile.WriteLine(@"-- Loading Taxonomy"); targetScriptFile.WriteLine(@"PRINT '*** Loading Taxonomy';"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(@"BULK INSERT Taxonomy FROM N'{0}{1}\Taxonomy.dat'", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FORMATFILE=N'{0}{1}\Bulkload.Taxonomy.xml');", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"UPDATE Taxonomy SET ParentTaxID = 0 WHERE TaxID = 1;"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"-- Loading NameClasses"); targetScriptFile.WriteLine(@"PRINT '*** Loading NameClasses';"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(@"BULK INSERT NameClasses FROM N'{0}{1}\NameClasses.dat'", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FORMATFILE=N'{0}{1}\Bulkload.NameClasses.xml');", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"-- Loading TaxonomyNames"); targetScriptFile.WriteLine(@"PRINT '*** Loading TaxonomyNames';"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(@"BULK INSERT TaxonomyNames FROM N'{0}{1}\TaxonomyNames.dat'", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FORMATFILE=N'{0}{1}\Bulkload.TaxonomyNames.xml');", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"-- Loading AlternateNames"); targetScriptFile.WriteLine(@"PRINT '*** Loading AlternateNames';"); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(@"BULK INSERT AlternateNames FROM N'{0}{1}\AlternateNames.dat'", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FORMATFILE=N'{0}{1}\Bulkload.AlternateNames.xml');", createArgs.DataDirectory, EVOLUTIONARY_RELATIONSHIPS_DIR); targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); //4. Create rCAD Indices targetScriptFile.WriteLine(@"-- Creating rCAD Indices"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"PRINT '*** Creating rCAD Indices';"); targetScriptFile.WriteLine(@"GO"); foreach (var createIndexSQLFile in createIndicesFiles) { using (var sql = File.OpenText(createIndexSQLFile)) { string line = sql.ReadLine(); while (!string.IsNullOrEmpty(line)) { targetScriptFile.WriteLine(line); line = sql.ReadLine(); } } targetScriptFile.WriteLine(targetScriptFile.NewLine); } //5. Create rCAD Views targetScriptFile.WriteLine(@"-- Creating rCAD Views"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"PRINT '*** Creating rCAD Views';"); targetScriptFile.WriteLine(@"GO"); foreach (var createViewSQLFile in createViewsFiles) { using (var sql = File.OpenText(createViewSQLFile)) { string line = sql.ReadLine(); while (!string.IsNullOrEmpty(line)) { targetScriptFile.WriteLine(line); line = sql.ReadLine(); } } targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); } //6. Create rCAD UDFs targetScriptFile.WriteLine(@"-- Creating rCAD UDFs"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"PRINT '*** Creating rCAD UDFs';"); targetScriptFile.WriteLine(@"GO"); foreach (var createUDFSQLFile in createUDFFiles) { using (var sql = File.OpenText(createUDFSQLFile)) { string line = sql.ReadLine(); while (!string.IsNullOrEmpty(line)) { targetScriptFile.WriteLine(line); line = sql.ReadLine(); } } targetScriptFile.WriteLine(@"GO"); targetScriptFile.WriteLine(targetScriptFile.NewLine); } //7. BatchOps targetScriptFile.WriteLine(@"-- Batch Operations"); targetScriptFile.WriteLine(targetScriptFile.NewLine); targetScriptFile.WriteLine(@"PRINT '*** Executing Batch Operations';"); targetScriptFile.WriteLine(@"GO"); foreach (var batchOpSQLFile in batchOpsFiles) { using (var sql = File.OpenText(batchOpSQLFile)) { string line = sql.ReadLine(); while (!string.IsNullOrEmpty(line)) { targetScriptFile.WriteLine(line); line = sql.ReadLine(); } } targetScriptFile.WriteLine(targetScriptFile.NewLine); } targetScriptFile.Flush(); } } retValue = true; } catch { retValue = false; } args.Result = retValue; } }