public static void TablePatternMatchTest(MainForm frm)
{
string sourceConnectionString = @"Data Source=PROFASTWS3; Initial Catalog=AdventureWorksDW2008R2; Integrated Security=True; Application Name=TestprogGetSchemas; Workstation ID=PROFASTWS5;";
PFSQLServer sourceDb = new PFSQLServer();
PFTableDefinitions tabdefs = new PFTableDefinitions();
try
{
_msg.Length = 0;
_msg.Append("TablePatternMatchTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
sourceDb.ConnectionString = sourceConnectionString;
sourceDb.OpenConnection();
Program._messageLog.WriteLine("Get all table names:\r\n");
//PFList<PFTableDef> tableDefs = sourceDb.GetTableList();
//PFList<PFTableDef> tableDefs = tabdefs.GetTableList(sourceDb, "*.DimC*", string.Empty);
PFList <PFTableDef> tableDefs = tabdefs.GetTableList(sourceDb);
PFTableDef td = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
Program._messageLog.WriteLine(_msg.ToString());
}
_msg.Length = 0;
_msg.Append("\r\nInclude: ");
_msg.Append("*.DimC*");
_msg.Append("\r\n");
Program._messageLog.WriteLine(_msg.ToString());
tableDefs = tabdefs.GetTableList(sourceDb, new string[1] {
"*.DimC*"
}, null);
td = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
Program._messageLog.WriteLine(_msg.ToString());
}
_msg.Length = 0;
_msg.Append("\r\nexclude: ");
_msg.Append("dbo.Fact*");
_msg.Append("\r\n");
Program._messageLog.WriteLine(_msg.ToString());
tableDefs = tabdefs.GetTableList(sourceDb, null, new string[1] {
"dbo.Fact*"
});
td = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
Program._messageLog.WriteLine(_msg.ToString());
}
string[] includes = new string[3];
includes[0] = "dbo.DimDate";
includes[1] = "dbo.DimC*";
includes[2] = "dbo.FactFinance";
string[] excludes = new string[5];
excludes[0] = "dbo.DimC*";
excludes[1] = "dbo.DimGeography";
excludes[2] = "dbo.FactFinance";
excludes[3] = "dbo.MikeTab01";
excludes[4] = "dbo.TestTable01";
_msg.Length = 0;
_msg.Append("\r\n<includes>: ");
_msg.Append("\r\n");
Program._messageLog.WriteLine(_msg.ToString());
tableDefs = tabdefs.GetTableList(sourceDb, includes, null);
td = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
Program._messageLog.WriteLine(_msg.ToString());
}
_msg.Length = 0;
_msg.Append("\r\n<excludes>: ");
_msg.Append("\r\n");
Program._messageLog.WriteLine(_msg.ToString());
tableDefs = tabdefs.GetTableList(sourceDb, null, excludes);
td = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
Program._messageLog.WriteLine(_msg.ToString());
}
includes = new string[3];
includes[0] = "dbo.DimDate";
includes[1] = "dbo.DimC*";
includes[2] = "dbo.FactS*";
excludes = new string[2];
excludes[0] = "dbo.DimCurrency";
excludes[1] = "dbo.FactSurveyResponse";
_msg.Length = 0;
_msg.Append("\r\n<includes/excludes>: ");
_msg.Append("\r\n");
Program._messageLog.WriteLine(_msg.ToString());
tableDefs = tabdefs.GetTableList(sourceDb, includes, excludes);
td = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
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
{
_msg.Length = 0;
_msg.Append("\r\n... TablePatternMatchTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
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());
}
}