/// <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>
/// Retrieves list of tables and their associated schema information contained in the database pointed to by the current connection.
/// </summary>
/// <param name="sourceDatabase">Database from which the table list will be retrieved.</param>
/// <param name="includePatterns">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="excludePatterns">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>
/// <returns>Object containing the list of table definitions.</returns>
public PFList <PFTableDef> GetTableList(IDatabaseProvider sourceDatabase, string[] includePatterns, string[] excludePatterns)
{
PFList <PFTableDef> tableDefs = new PFList <PFTableDef>();
DataTable dt = null;
string sqlSelect = "select * from <tableName> where 1=0";
PFSearchPattern[] regexInclude = null;
PFSearchPattern[] regexExclude = null;
regexInclude = GetSearchPatternRegexObjects(includePatterns, "*");
regexExclude = GetSearchPatternRegexObjects(excludePatterns, string.Empty);
if (sourceDatabase.IsConnected == false)
{
_msg.Length = 0;
_msg.Append("You must be connected to a database to run GetTableList method.");
throw new System.Exception(_msg.ToString());
}
dt = sourceDatabase.Connection.GetSchema("Tables");
foreach (DataRow dr in dt.Rows)
{
if (sourceDatabase.TypeIsUserTable(dr))
{
string tabName = sourceDatabase.GetFullTableName(dr);
if (IsMatchToPattern(regexInclude, tabName) && IsMatchToPattern(regexExclude, tabName) == false)
{
string sql = sqlSelect.Replace("<tableName>", tabName);
try
{
dt = sourceDatabase.RunQueryDataTable(sql, CommandType.Text);
}
catch
{
dt = new DataTable(tabName);
}
string tabCreateStatement = string.Empty;
dt.TableName = tabName;
try
{
tabCreateStatement = sourceDatabase.BuildTableCreateStatement(dt);
}
catch
{
tabCreateStatement = "Unable to build create statement. Error occurred in BuildTableCreateStatement.";
}
PFTableDef tabDef = new PFTableDef();
tabDef.DbPlatform = sourceDatabase.DbPlatform;
tabDef.DbConnectionString = sourceDatabase.ConnectionString;
tabDef.TableCreateStatement = tabCreateStatement;
tabDef.TableObject = dt;
tabDef.TableFullName = tabName;
TableNameQualifiers tnq = sourceDatabase.GetTableNameQualifiers(dr);
tabDef.TableCatalog = tnq.TableCatalog;
tabDef.TableOwner = tnq.TableSchema;
tabDef.TableName = tnq.TableName;
tableDefs.Add(tabDef);
}
}
}
return(tableDefs);
}
/// <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);
}
