public static void GetSQLServerTables(MainForm frm) { Stopwatch sw = new Stopwatch(); string sourceConnectionString = @"Data Source=PROFASTWS3; Initial Catalog=AdventureWorksDW2008R2; Integrated Security=True; Application Name=TestprogGetSchemas; Workstation ID=PROFASTWS5;"; PFSQLServer sourceDb = new PFSQLServer(); string destinationConnectionString = @"Data Source=PROFASTSV2; Initial Catalog=AWTest; Integrated Security=True; Application Name=TestprogGetSchemas; Workstation ID=PROFASTWS5;"; PFSQLServer destDb = new PFSQLServer(); PFTableDefinitions tabdefs = new PFTableDefinitions(); try { _msg.Length = 0; _msg.Append("GetSQLServerTables started ...\r\n"); Program._messageLog.WriteLine(_msg.ToString()); sourceDb.ConnectionString = sourceConnectionString; sourceDb.OpenConnection(); Program._messageLog.WriteLine("Get all table names:\r\n"); string[] includes = new string[4]; includes[0] = "dbo.DimDate"; includes[1] = "dbo.DimC*"; includes[2] = "dbo.DimGeography"; includes[3] = "dbo.FactCurrencyRate"; //PFList<PFTableDef> tableDefs = sourceDb.GetTableList(); PFList <PFTableDef> tableDefs = tabdefs.GetTableList(sourceDb); //PFList<PFTableDef> tableDefs = tabdefs.GetTableList(sourceDb); PFTableDef td = null; tableDefs.SetToBOF(); while ((td = tableDefs.NextItem) != null) { _msg.Length = 0; _msg.Append(td.TableFullName); _msg.Append(":\r\n"); _msg.Append(td.TableCreateStatement); _msg.Append("\r\n"); Program._messageLog.WriteLine(_msg.ToString()); } _msg.Length = 0; _msg.Append("\r\nConverted Table Defs follow: \r\n"); Program._messageLog.WriteLine(_msg.ToString()); destDb.ConnectionString = destinationConnectionString; destDb.OpenConnection(); PFList <PFTableDef> newTableDefs = destDb.ConvertTableDefs(tableDefs, "xyz"); PFTableDef newtd = null; newTableDefs.SetToBOF(); while ((newtd = newTableDefs.NextItem) != null) { _msg.Length = 0; _msg.Append(newtd.TableFullName); _msg.Append(":\r\n"); _msg.Append(newtd.TableCreateStatement); _msg.Append("\r\n"); Program._messageLog.WriteLine(_msg.ToString()); } _msg.Length = 0; _msg.Append("\r\nTesting table creates: \r\n"); Program._messageLog.WriteLine(_msg.ToString()); sw.Start(); newTableDefs.SetToBOF(); int numTabsCreated = destDb.CreateTablesFromTableDefs(newTableDefs, true); sw.Stop(); _msg.Length = 0; _msg.Append("\r\nNumber of tables created: \r\n"); _msg.Append(numTabsCreated.ToString()); _msg.Append("\r\n"); _msg.Append("Elapsed time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); _msg.Length = 0; _msg.Append("\r\nTesting table copies: \r\n"); Program._messageLog.WriteLine(_msg.ToString()); sw.Start(); newTableDefs.SetToBOF(); PFDatabase dbtemp = new PFDatabase(DatabasePlatform.MSSQLServer); dbtemp.ConnectionString = sourceDb.ConnectionString; dbtemp.OpenConnection(); //PFList<TableCopyDetails> tableCopyLog = destDb.CopyTableDataFromTableDefs(dbtemp, includes, null, "xyz", true); PFList <TableCopyDetails> tableCopyLog = destDb.CopyTableDataFromTableDefs(dbtemp, null, null, "xyz", true); dbtemp.CloseConnection(); sw.Stop(); _msg.Length = 0; _msg.Append("\r\nTable copies finished: \r\n"); _msg.Append("Elapsed time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); tableCopyLog.SetToBOF(); TableCopyDetails tcdetails = null; while ((tcdetails = tableCopyLog.NextItem) != null) { _msg.Length = 0; _msg.Append("Table: "); _msg.Append(tcdetails.destinationTableName); _msg.Append(", NumRowsCopied: "); _msg.Append(tcdetails.numRowsCopied.ToString("#,##0")); if (tcdetails.result != TableCopyResult.Success) { _msg.Append("\r\n "); _msg.Append("Result: "); _msg.Append(tcdetails.result.ToString()); _msg.Append(" Messages: "); _msg.Append(tcdetails.messages); } Program._messageLog.WriteLine(_msg.ToString()); } } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { if (sourceDb.IsConnected) { sourceDb.CloseConnection(); } if (destDb.IsConnected) { destDb.CloseConnection(); } sourceDb = null; destDb = null; _msg.Length = 0; _msg.Append("\r\n... GetSQLServerTables finished."); Program._messageLog.WriteLine(_msg.ToString()); } }
public static void ConvertTableDefs(MainForm frm) { Stopwatch sw = new Stopwatch(); string dbAssemblyPath = string.Empty; PFDatabase sourceDb = null; string sourceConnectionString = frm.cboSourceDbConnectionString.Text; PFDatabase destDb = null; string destinationConnectionString = frm.cboDestinationDbConnectionString.Text; PFList <PFTableDef> tabDefList = null; string[] includes = null; string[] excludes = null; string[] lineTerminators = { "\r\n", Environment.NewLine }; try { _msg.Length = 0; _msg.Append("ConvertTableDefs started ...\r\n"); Program._messageLog.WriteLine(_msg.ToString()); if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString()) { dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty); sourceDb = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath); } else if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString()) { dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty); sourceDb = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath); } else { sourceDb = new PFDatabase(frm.cboSourceDbPlatform.Text); } if (frm.cboDestinationDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString()) { dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty); destDb = new PFDatabase(frm.cboDestinationDbPlatform.Text, dbAssemblyPath); } else if (frm.cboDestinationDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString()) { dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty); destDb = new PFDatabase(frm.cboDestinationDbPlatform.Text, dbAssemblyPath); } else { destDb = new PFDatabase(frm.cboDestinationDbPlatform.Text); } sourceDb.ConnectionString = sourceConnectionString; sourceDb.OpenConnection(); if (frm.txtIncludePatterns.Text.Trim().Length > 0) { includes = frm.txtIncludePatterns.Text.Split(lineTerminators, StringSplitOptions.None); } if (frm.txtExcludePatterns.Text.Trim().Length > 0) { excludes = frm.txtExcludePatterns.Text.Split(lineTerminators, StringSplitOptions.None); } tabDefList = sourceDb.GetTableList(includes, excludes); destDb.ConnectionString = destinationConnectionString; destDb.OpenConnection(); PFList <PFTableDef> newTableDefs = destDb.ConvertTableDefs(tabDefList, frm.txtNewSchema.Text.Trim()); PFTableDef newtd = null; newTableDefs.SetToBOF(); while ((newtd = newTableDefs.NextItem) != null) { _msg.Length = 0; _msg.Append(newtd.TableFullName); if (frm.chkShowTableCreateStatements.Checked) { _msg.Append(":\r\n"); _msg.Append(newtd.TableCreateStatement); _msg.Append("\r\n"); } Program._messageLog.WriteLine(_msg.ToString()); } if (frm.chkRunConvertedTableCreateStatements.Checked) { sw.Start(); newTableDefs.SetToBOF(); int numTabsCreated = destDb.CreateTablesFromTableDefs(newTableDefs, true); sw.Stop(); _msg.Length = 0; _msg.Append("\r\nNumber of tables created: \r\n"); _msg.Append(numTabsCreated.ToString()); _msg.Append("\r\n"); _msg.Append("Elapsed time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); sw.Stop(); if (frm.chkImportDataFromSourceToDestination.Checked) { sw.Start(); PFList <TableCopyDetails> tableCopyLog = destDb.CopyTableDataFromTableDefs(sourceDb, includes, null, frm.txtNewSchema.Text.Trim(), true); sw.Stop(); tableCopyLog.SetToBOF(); TableCopyDetails tcdetails = null; while ((tcdetails = tableCopyLog.NextItem) != null) { _msg.Length = 0; _msg.Append("Table: "); _msg.Append(tcdetails.destinationTableName); _msg.Append(", NumRowsCopied: "); _msg.Append(tcdetails.numRowsCopied.ToString("#,##0")); if (tcdetails.result != TableCopyResult.Success) { _msg.Append("\r\n "); _msg.Append("Result: "); _msg.Append(tcdetails.result.ToString()); _msg.Append(" Messages: "); _msg.Append(tcdetails.messages); } Program._messageLog.WriteLine(_msg.ToString()); } _msg.Length = 0; _msg.Append("Elapsed time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); } //end import data routine } //end table create routine } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { if (sourceDb != null) { if (sourceDb.IsConnected) { sourceDb.CloseConnection(); } } if (destDb != null) { if (destDb.IsConnected) { destDb.CloseConnection(); } } sourceDb = null; destDb = null; _msg.Length = 0; _msg.Append("\r\n... ConvertTableDefs finished."); Program._messageLog.WriteLine(_msg.ToString()); } }