/// <summary>
/// Method to convert table definitions from another database format to the data format supported by the destination database class.
/// </summary>
/// <param name="tableDefs">Object containing the list of table definitions to be converted.</param>
/// <param name="destinationDatabase">Database object pointing to database that will define converted table definitions.</param>
/// <param name="newSchemaName">Specify a new schema (owner) name for the tables when they are recreated in the database managed by the current instance.</param>
/// <returns>Object containing the list of table definitions after they have been converted to match the data formats of the current instance.</returns>
public PFList <PFTableDef> ConvertTableDefs(PFList <PFTableDef> tableDefs, IDatabaseProvider destinationDatabase, string newSchemaName)
{
PFList <PFTableDef> newTableDefs = new PFList <PFTableDef>();
PFTableDef tabDef = null;
string tabName = string.Empty;
string schemaName = string.Empty;
string tabCreateStatement = string.Empty;
tableDefs.SetToBOF();
while ((tabDef = tableDefs.NextItem) != null)
{
string tabDefXmlString = tabDef.ToXmlString();
PFTableDef newTabDef = PFTableDef.LoadFromXmlString(tabDefXmlString);
tabName = destinationDatabase.RebuildFullTableName(tabDef, newSchemaName);
schemaName = String.IsNullOrEmpty(newSchemaName) == false ? newSchemaName : tabDef.TableOwner;
newTabDef.TableObject.TableName = tabName;
tabCreateStatement = destinationDatabase.BuildTableCreateStatement(newTabDef.TableObject);
newTabDef.DbPlatform = destinationDatabase.DbPlatform;
newTabDef.DbConnectionString = destinationDatabase.ConnectionString;
newTabDef.TableOwner = schemaName;
newTabDef.TableFullName = tabName;
newTabDef.TableCreateStatement = tabCreateStatement;
newTableDefs.Add(newTabDef);
}
return(newTableDefs);
}
/// <summary>
/// Runs the table create statements contained in the provided tableDefs object.
/// </summary>
/// <param name="destinationDatabase">Database object pointing to database where tables will be created.</param>
/// <param name="tableDefs">Object containing list of table definitions.</param>
/// <param name="dropBeforeCreate">If true and table already exists, table will be dropped and then recreated using the table definition in the supplied PFTableDefs list. If false, table create step is bypassed if table already exists.</param>
/// <returns>Number of tables created.</returns>
public int CreateTablesFromTableDefs(IDatabaseProvider destinationDatabase, PFList <PFTableDef> tableDefs, bool dropBeforeCreate)
{
int numTablesCreated = 0;
PFTableDef td = null;
string sqlStatement = string.Empty;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
if (destinationDatabase.TableExists(td) && dropBeforeCreate)
{
destinationDatabase.DropTable(td);
}
if (destinationDatabase.TableExists(td) == false)
{
sqlStatement = td.TableCreateStatement;
destinationDatabase.RunNonQuery(sqlStatement, CommandType.Text);
numTablesCreated++;
}
}
return(numTablesCreated);
}
public static void GetSupportedDatabasesList(MainForm frm)
{
PFList <string> dblist = null;
try
{
_msg.Length = 0;
_msg.Append("GetSupportedDatabasesList started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
dblist = PFDatabase.GetListOfSupportedDatabases();
string tab = null;
dblist.SetToBOF();
while ((tab = dblist.NextItem) != null)
{
if (tab.ToUpper() != "UNKNOWN")
{
_msg.Length = 0;
_msg.Append(tab);
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... GetSupportedDatabasesList finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
/// <summary>
/// Creates an ADO.NET DataTable object using the table definition contained in the collection of column definitions encapsulated by this instance.
/// </summary>
/// <param name="useDataTypeSubset">If true, then only a small subset of .NET data types will be used. This is needed when trying to create a table via generic OLEDB or ODBC syntax.</param>
/// <returns>DataTable object.</returns>
public DataTable GetDataTableFromTableColumns(bool useDataTypeSubset)
{
DataTable dt = new DataTable();
List <DataColumn> primaryKeys = new List <DataColumn>();
if (_schemaName == null)
{
_schemaName = string.Empty;
}
if (String.IsNullOrEmpty(_tableName))
{
_tableName = "TestTable01";
}
if (_schemaName.Trim().Length > 0)
{
dt.TableName = _schemaName.Trim() + "." + _tableName.Trim();
}
else
{
dt.TableName = TableName.Trim();
}
_tableColumns.SetToBOF();
TableColumn tc = null;
tc = _tableColumns.FirstItem;
while (tc != null)
{
if (tc.IncludeInOutput)
{
DataColumn dc = new DataColumn();
dc.ColumnName = tc.ColumnName;
//dc.DataType = Type.GetType(tc.ColumnType);
//if (tc.MaxLength > 0)
// dc.MaxLength = tc.MaxLength;
if (useDataTypeSubset == false)
{
dc.DataType = Type.GetType(tc.ColumnType);
if (tc.MaxLength > 0)
{
dc.MaxLength = tc.MaxLength;
}
}
else
{
SetDataTypeFromSubset(tc.ColumnType, tc.MaxLength, dc);
}
dc.AllowDBNull = tc.AllowsNulls;
if (tc.IsPrimaryKey)
{
primaryKeys.Add(dc);
}
dc.DefaultValue = GetColumnValue(dc.DataType, tc.ColumnValue);
dt.Columns.Add(dc);
}
tc = _tableColumns.NextItem;
}
if (primaryKeys.Count > 0)
{
dt.PrimaryKey = primaryKeys.ToArray();
}
// create a data row for the data table
DataRow dr = dt.NewRow();
dr.ItemArray = new object[dt.Columns.Count];
int maxColInx = dt.Columns.Count - 1;
for (int colInx = 0; colInx <= maxColInx; colInx++)
{
//dr.ItemArray[colInx] = GetColumnValue(dt.Columns[colInx]);
dr.ItemArray[colInx] = dt.Columns[colInx].DefaultValue;
}
dt.Rows.Add(dr);
return(dt);
}
/// <summary>
/// Runs the table create statements contained in the provided tableDefs object.
/// </summary>
/// <param name="sourceDatabase">Database containing source tables..</param>
/// <param name="tableIncludePatterns">Wildard pattern to use when selecting which tables to include. Specify * or null or empty string for pattern to include all tables.</param>
/// <param name="tableExcludePatterns">Wildard pattern to use when selecting which tables to exclude. Specify * for pattern to exclude all tables. Specify null or empty string to exclude no tables.</param>
/// <param name="destinationDatabase">Database were data will be copied to..</param>
/// <param name="newSchemaName">Schema to use for identifying the destination tables.</param>
/// <param name="dropBeforeCreate">If true and table already exists, table will be dropped and then recreated using the table definition in the supplied PFTableDefs list. If false, table create step is bypassed if table already exists.</param>
/// <returns>Number of tables created.</returns>
/// <remarks>Include and exclude patterns are the same as Windows File wildcards. * specifies zero or more characters. ? specifies one character.</remarks>
public PFList <TableCopyDetails> CopyTableDataFromTableDefs(IDatabaseProvider sourceDatabase, string[] tableIncludePatterns, string[] tableExcludePatterns,
IDatabaseProvider destinationDatabase, string newSchemaName, bool dropBeforeCreate)
{
PFList <TableCopyDetails> tableCopyLog = new PFList <TableCopyDetails>();
PFTableDef td = null;
string sqlStatement = string.Empty;
string selectStatement = "select * from <TableName>";
string newTableName = string.Empty;
string tdToXml = string.Empty;
PFTableDef newTd = null;
PFList <PFTableDef> tableDefs = sourceDatabase.GetTableList(tableIncludePatterns, tableExcludePatterns);
DataTable sourceData = null;
tableDefs.SetToBOF();
while ((td = tableDefs.NextItem) != null)
{
newTableName = td.TableName;
tdToXml = td.ToXmlString();
newTd = PFTableDef.LoadFromXmlString(tdToXml);
if (newSchemaName.Trim().Length > 0)
{
newTd.TableObject.TableName = newSchemaName + "." + newTableName;
}
else
{
newTd.TableObject.TableName = newTableName;
}
newTd.TableFullName = newTd.TableObject.TableName;
newTd.TableOwner = newSchemaName;
newTd.TableName = newTableName;
if (destinationDatabase.TableExists(newSchemaName, newTableName) && dropBeforeCreate)
{
destinationDatabase.DropTable(newSchemaName, newTableName);
}
if (destinationDatabase.TableExists(newSchemaName, newTableName) == false)
{
sqlStatement = destinationDatabase.BuildTableCreateStatement(newTd.TableObject);
destinationDatabase.RunNonQuery(sqlStatement, CommandType.Text);
}
TableCopyDetails tcdetails = new TableCopyDetails();
tcdetails.sourceTableName = td.TableFullName;
tcdetails.destinationTableName = newTd.TableFullName;
tcdetails.numSourceRows = -1;
tcdetails.numRowsCopied = -1;
try
{
sqlStatement = selectStatement.Replace("<TableName>", td.TableObject.TableName);
sourceData = sourceDatabase.RunQueryDataTable(sqlStatement, CommandType.Text);
sourceData.TableName = newTd.TableObject.TableName;
tcdetails.numSourceRows = sourceData.Rows.Count;
if (sourceData.Rows.Count > 0)
{
destinationDatabase.ImportDataFromDataTable(sourceData);
tcdetails.numRowsCopied = sourceData.Rows.Count;
tcdetails.result = TableCopyResult.Success;
tcdetails.messages = string.Empty;
}
else
{
tcdetails.numRowsCopied = 0;
tcdetails.result = TableCopyResult.Alert;
tcdetails.messages = "The were no rows in the source table.";
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to copy ");
_msg.Append(td.TableFullName);
_msg.Append(" to ");
_msg.Append(newTd.TableFullName);
_msg.Append(" failed. Error message: ");
_msg.Append(PFTextObjects.PFTextProcessor.FormatErrorMessage(ex));
tcdetails.messages = _msg.ToString();
tcdetails.numRowsCopied = -1;
tcdetails.result = TableCopyResult.Failure;
}
tableCopyLog.Add(tcdetails);
newTd = null;
sourceData = null;
}
return(tableCopyLog);
}
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());
}
}
//tests
public static void GetTabDefList(MainForm frm)
{
//PFTableDefinitions tabDefs = new PFTableDefinitions();
PFList <PFTableDef> tabDefList = null;
PFDatabase db = null;
string dbAssemblyPath = string.Empty;
string[] includes = null;
string[] excludes = null;
string[] lineTerminators = { "\r\n", Environment.NewLine };
string dbPlatformDesc = frm.cboSourceDbPlatform.Text;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
try
{
_msg.Length = 0;
_msg.Append("GetTabDefList started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
if (configValue.Trim() == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find config entry for ");
_msg.Append(dbPlatformDesc);
throw new System.Exception(_msg.ToString());
}
string[] parsedConfig = configValue.Split('|');
if (parsedConfig.Length != 3)
{
_msg.Length = 0;
_msg.Append("Invalid config entry items for ");
_msg.Append(dbPlatformDesc);
_msg.Append(". Number of items after parse: ");
_msg.Append(parsedConfig.Length.ToString());
_msg.Append(".");
throw new System.Exception(_msg.ToString());
}
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty);
db = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath, nmSpace + "." + clsName);
}
else if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty);
db = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath, nmSpace + "." + clsName);
}
else
{
db = new PFDatabase(frm.cboSourceDbPlatform.Text, dllPath, nmSpace + "." + clsName);
}
db.ConnectionString = frm.cboSourceDbConnectionString.Text;
db.OpenConnection();
if (frm.txtIncludePatterns.Text.Trim().Length > 0)
{
includes = frm.txtIncludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
if (includes.Length > 0)
{
for (int i = 0; i < includes.Length; i++)
{
if (includes[i].Length == 0)
{
includes[i] = "ignore this include";
}
}
}
}
if (frm.txtExcludePatterns.Text.Trim().Length > 0)
{
excludes = frm.txtExcludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
if (excludes.Length > 1)
{
for (int i = 0; i < excludes.Length; i++)
{
if (excludes[i].Length == 0)
{
excludes[i] = "ignore this exclude";
}
}
}
}
tabDefList = db.GetTableList(includes, excludes);
PFTableDef td = null;
tabDefList.SetToBOF();
while ((td = tabDefList.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
if (frm.chkShowTableCreateStatements.Checked)
{
_msg.Append(":\r\n");
_msg.Append(td.TableCreateStatement);
_msg.Append("\r\n");
}
Program._messageLog.WriteLine(_msg.ToString());
}
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
//tabDefs = null;
tabDefList = null;
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
_msg.Length = 0;
_msg.Append("\r\n... GetTabDefList 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());
}
}
