/// <summary>
/// Routine to copy data from a fixed length data text file to a database table.
/// </summary>
/// <param name="dataFileName">Path to file containing the data.</param>
/// <param name="lineDefFileName">Path to file containing the PFFixedLengthDataLine object that encapsulates the line formatting information for the specified data file.</param>
/// <param name="db">Database object for the target database.</param>
/// <param name="tableName">Name of table in database to which data is to be copied.</param>
/// <param name="updateBatchSize">Number of individual SQL modification statements to include in a table modification operation.</param>
/// <returns>Number of rows uploaded to the database.</returns>
public int ImportFixedLengthTextFileToDatabase(string dataFileName, string lineDefFileName, PFDatabase db, string tableName, int updateBatchSize)
{
DataTable dt = ImportFixedLengthTextFileToDataTable(dataFileName, lineDefFileName);
dt.TableName = tableName;
db.ImportDataFromDataTable(dt, updateBatchSize);
return(dt.Rows.Count);
}
/// <summary>
/// Writes data contained in ADO.NET DataTable object to path stored in OutputFileName property.
/// </summary>
/// <param name="dt">DataTable object containing data to be output.</param>
/// <returns>True if output operation is successful. False if write fails.</returns>
public bool WriteDataToOutput(DataTable dt)
{
bool success = true;
IDesktopDatabaseProvider db = null;
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
string templateFile = string.Empty;
try
{
if (File.Exists(_outputFileName))
{
if (_replaceExistingFile)
{
try
{
File.SetAttributes(_outputFileName, FileAttributes.Normal);
File.Delete(_outputFileName);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Unable to delete old file. It may be locked by SQLAnywhere local server. Exit this application and try again. This should remove the lock.");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
}
else
{
_msg.Length = 0;
_msg.Append("File exists and ReplaceExistingFile set to False. Write to Output has failed.");
throw new System.Exception(_msg.ToString());
}
}
string oldLogFile = Path.Combine(Path.GetDirectoryName(_outputFileName), Path.GetFileNameWithoutExtension(_outputFileName) + ".Log");
if (File.Exists(oldLogFile))
{
File.SetAttributes(oldLogFile, FileAttributes.Normal);
File.Delete(oldLogFile);
}
if (this._desktopDbVersion == enDesktopDbVersion.SQLCE_Version40)
{
templateFile = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLCE40TemplateFile);
dbPlatformDesc = DatabasePlatform.SQLServerCE40.ToString();
}
else if (this._desktopDbVersion == enDesktopDbVersion.SQLAnywhere)
{
templateFile = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLAnywhereTemplateFile);
dbPlatformDesc = DatabasePlatform.SQLAnywhere.ToString();
}
else if (this._desktopDbVersion == enDesktopDbVersion.SQLAnywhere_UltraLite)
{
templateFile = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLAnywhereUltraLiteTemplateFile);
dbPlatformDesc = DatabasePlatform.SQLAnywhereUltraLite.ToString();
}
else
{
templateFile = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLCE35TemplateFile);
dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
}
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
string[] parsedConfig = configValue.Split('|');
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
if (_desktopDbVersion == enDesktopDbVersion.SQLCE_Version35 ||
_desktopDbVersion == enDesktopDbVersion.SQLCE_Version40)
{
db.DatabasePath = _outputFileName;
connStr = db.ConnectionString;
db.CreateDatabase(connStr);
}
else if (_desktopDbVersion == enDesktopDbVersion.SQLAnywhere_UltraLite)
{
connStr = OutputFileName;
db.CreateDatabase(connStr);
connStr = _defaultSQLAnywhereUltraLiteConnectionString.Replace("<filename>", OutputFileName);
db.ConnectionString = connStr;
}
else if (_desktopDbVersion == enDesktopDbVersion.SQLAnywhere)
{
if (File.Exists(templateFile))
{
connStr = _defaultSQLAnywhereConnectionString.Replace("<filename>", templateFile);
db.ConnectionString = connStr;
db.OpenConnection();
db.CreateDatabase(OutputFileName);
db.CloseConnection();
//db.CreateDatabase(OutputFileName, templateFile); //file copy create not working properly: transaction log lock outs.
connStr = _defaultSQLAnywhereConnectionString.Replace("<filename>", OutputFileName);
db.ConnectionString = connStr;
}
else
{
_msg.Length = 0;
_msg.Append("Unable to find template file for SQLAnywhere databases: ");
_msg.Append(templateFile);
_msg.Append(".");
throw new System.Exception(_msg.ToString());
}
}
else
{
_msg.Length = 0;
_msg.Append("Invalid or unexpected desktop database platform: ");
_msg.Append(_desktopDbVersion);
_msg.Append(".");
throw new System.Exception(_msg.ToString());
}
db.OpenConnection();
db.CreateTable(dt);
db.CloseConnection();
db.OpenConnection();
db.ImportDataFromDataTable(dt); //this is very slow for SQLAnywhere UltraLite
db.CloseConnection();
}
catch (System.Exception ex)
{
success = false;
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
}
return(success);
}
/// <summary>
/// Writes data contained in DataTable to table stored in TableName property.
/// </summary>
/// <param name="dtList">List of temp file names containing data tables with grid rows to be output.</param>
/// <returns>True if output operation is successful. False if write fails.</returns>
public bool WriteDataToOutput(PFList <string> dtList)
{
bool success = true;
PFDatabase db = null;
bool createTable = false;
DataTable dt = new DataTable();
string tableName = string.Empty;
bool isOracleOdbc = false;
bool isOracleOledb = false;
try
{
if (dtList.Count == 0)
{
_msg.Length = 0;
_msg.Append("WriteDataToOutput for list of temp files has failed.");
_msg.Append(Environment.NewLine);
_msg.Append("Temp file name list is empty.");
throw new System.Exception(_msg.ToString());
}
//Load first temp file in list to get data table schema
dt.Rows.Clear();
dt.ReadXml(dtList[0]);
db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
db.ConnectionString = this.ConnectionString;
tableName = this.TableName;
//workaround for oracle odbc and oledb driver problems with inserts
if (this.DbPlatform == DatabasePlatform.ODBC)
{
PFOdbc odbcDb = new PFOdbc();
odbcDb.ConnectionString = this.ConnectionString;
DatabasePlatform odbcDbPlat = odbcDb.GetDatabasePlatform();
if (odbcDbPlat == DatabasePlatform.OracleNative || odbcDbPlat == DatabasePlatform.MSOracle)
{
isOracleOdbc = true;
}
odbcDb = null;
}
if (this.DbPlatform == DatabasePlatform.OLEDB)
{
PFOleDb oledbDb = new PFOleDb();
oledbDb.ConnectionString = this.ConnectionString;
DatabasePlatform oledbDbPlat = oledbDb.GetDatabasePlatform();
if (oledbDbPlat == DatabasePlatform.OracleNative || oledbDbPlat == DatabasePlatform.MSOracle)
{
isOracleOledb = true;
}
oledbDb = null;
}
//set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
if (isOracleOdbc || isOracleOledb)
{
tableName = this.TableName.ToUpper();
for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
}
}
else
{
tableName = this.TableName;
}
//end workaround for oracle odbc and oledb
dt.TableName = tableName;
if (db.TableExists(tableName))
{
if (this.ReplaceExistingTable)
{
db.OpenConnection();
db.DropTable(tableName);
db.CloseConnection();
createTable = true;
}
else
{
createTable = false; //existing table will be imported into
}
}
else
{
createTable = true;
}
if (createTable)
{
db.OpenConnection();
string createScript = string.Empty;
string errorMessages = string.Empty;
bool tabCreated = db.CreateTable(dt, out createScript, out errorMessages);
if (tabCreated == false)
{
_msg.Length = 0;
_msg.Append("Unable to create table: ");
_msg.Append(tableName);
_msg.Append(Environment.NewLine);
_msg.Append("Error Messages: ");
_msg.Append(errorMessages);
_msg.Append(Environment.NewLine);
_msg.Append("Create Script: ");
_msg.Append(createScript);
throw new System.Exception(_msg.ToString());
}
db.CloseConnection();
}
for (int dtInx = 0; dtInx < dtList.Count; dtInx++)
{
_msg.Length = 0;
_msg.Append("List # ");
_msg.Append(dtInx.ToString());
Console.WriteLine(_msg.ToString());
dt = new DataTable();
dt.TableName = this.TableName; //original table name should be in xml file definition
dt.Rows.Clear();
dt.ReadXml(dtList[dtInx]);
db.OpenConnection();
//set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
if (isOracleOdbc || isOracleOledb)
{
dt.TableName = this.TableName.ToUpper();
for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
}
}
db.ImportDataFromDataTable(dt, this.OutputBatchSize);
db.CloseConnection();
dt = null;
}
}
catch (System.Exception ex)
{
success = false;
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
}
return(success);
}
/// <summary>
/// Writes data contained in DataTable to table stored in TableName property.
/// </summary>
/// <param name="dt">DataTable object containing data to be output.</param>
/// <returns>True if output operation is successful. False if write fails.</returns>
public bool WriteDataToOutput(DataTable dt)
{
bool success = true;
PFDatabase db = null;
bool createTable = false;
DataTable saveDtSchema = null;
string tableName = string.Empty;
bool isOracleOdbc = false;
bool isOracleOledb = false;
try
{
//save column names that may be changed during export processing so that they can be restored in finally block below
saveDtSchema = dt.Clone();
db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
db.ConnectionString = this.ConnectionString;
tableName = this.TableName;
//workaround for oracle odbc and oledb driver problems with inserts
if (this.DbPlatform == DatabasePlatform.ODBC)
{
PFOdbc odbcDb = new PFOdbc();
odbcDb.ConnectionString = this.ConnectionString;
DatabasePlatform odbcDbPlat = odbcDb.GetDatabasePlatform();
if (odbcDbPlat == DatabasePlatform.OracleNative || odbcDbPlat == DatabasePlatform.MSOracle)
{
isOracleOdbc = true;
}
odbcDb = null;
}
if (this.DbPlatform == DatabasePlatform.OLEDB)
{
PFOleDb oledbDb = new PFOleDb();
oledbDb.ConnectionString = this.ConnectionString;
DatabasePlatform oledbDbPlat = oledbDb.GetDatabasePlatform();
if (oledbDbPlat == DatabasePlatform.OracleNative || oledbDbPlat == DatabasePlatform.MSOracle)
{
isOracleOledb = true;
}
oledbDb = null;
}
//set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
if (isOracleOdbc || isOracleOledb)
{
tableName = this.TableName.ToUpper();
for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
}
}
//end workaround for oracle odbc and oledb
dt.TableName = tableName;
if (db.TableExists(tableName))
{
if (this.ReplaceExistingTable)
{
db.OpenConnection();
db.DropTable(tableName);
db.CloseConnection();
createTable = true;
}
else
{
createTable = false; //existing table will be imported into
}
}
else
{
createTable = true;
}
if (createTable)
{
db.OpenConnection();
string createScript = string.Empty;
string errorMessages = string.Empty;
bool tabCreated = db.CreateTable(dt, out createScript, out errorMessages);
if (tabCreated == false)
{
_msg.Length = 0;
_msg.Append("Unable to create table: ");
_msg.Append(tableName);
_msg.Append(Environment.NewLine);
_msg.Append("Error Messages: ");
_msg.Append(errorMessages);
_msg.Append(Environment.NewLine);
_msg.Append("Create Script: ");
_msg.Append(createScript);
throw new System.Exception(_msg.ToString());
}
db.CloseConnection();
}
db.OpenConnection();
db.ImportDataFromDataTable(dt, this.OutputBatchSize);
db.CloseConnection();
}
catch (System.Exception ex)
{
success = false;
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
//resstore any column names that may have been changed during export processing
for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
dt.Columns[colInx].ColumnName = saveDtSchema.Columns[colInx].ColumnName;
}
}
return(success);
}
public static void ImportTableTest(MainForm frm)
{
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
sw.Start();
_msg.Length = 0;
_msg.Append("ImportTableTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|');
dbPlatformDesc = parsedConnectionInfo[0];
connStr = parsedConnectionInfo[1];
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
string[] parsedConfig = configValue.Split('|');
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
string tableName = frm.txtKeyValsTableName.Text;
DataTable tab = frm.KeyValTable;
tab.TableName = tableName;
_msg.Length = 0;
_msg.Append(tableName);
if (db.TableExists(tableName))
{
db.DropTable(tableName);
if (db.TableExists(tableName) == false)
{
_msg.Append(" dropped.");
}
else
{
_msg.Append(" drop failed.");
}
}
else
{
_msg.Append(" does not exist.");
}
Program._messageLog.WriteLine("\r\nCreating a table in the database ...");
//create the table
db.CreateTable(tab);
Program._messageLog.WriteLine("\r\nImporting data table to the database ...");
int batchSize = Convert.ToInt32(frm.txtUpdateBatchSize.Text);
if (batchSize == 1)
{
db.ImportDataFromDataTable(tab);
}
else
{
db.ImportDataFromDataTable(tab, batchSize);
}
_msg.Length = 0;
_msg.Append("Table imported: ");
_msg.Append(tab.TableName);
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 (sw.StopwatchIsRunning)
{
sw.Stop();
sw.ShowMilliseconds = false;
_msg.Length = 0;
_msg.Append("Elapsed Time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw = null;
}
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... ImportTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
private void CopyTables(PFDatabase sourceDb, PFDatabase destinationDb, bool replaceExistingTables, int batchSizeForDataWrites, string outputTablesSchema)
{
string sourceQuery = string.Empty;
string destinationTableName = string.Empty;
int numTableCreateErrors = 0;
int numImportErrors = 0;
Stopwatch sw = new Stopwatch();
try
{
if (sourceDb.DbPlatform == DatabasePlatform.SQLServerCE35)
{
sourceQuery = _sourceQuery.Replace("dbo.", string.Empty);
}
else
{
sourceQuery = _sourceQuery;
}
_msg.Length = 0;
_msg.Append("Output Database Platform: ");
_msg.Append(destinationDb.DbPlatform.ToString());
_msg.Append(Environment.NewLine);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
foreach (stTableInfo tabInfo in _tableInfo)
{
//load data from source database into DataTable
string qry = sourceQuery.Replace(@"<tablename>", tabInfo.tableName);
string configValue = AppConfig.GetStringValueFromConfigFile(destinationDb.DbPlatform.ToString() + "_" + tabInfo.tableName, string.Empty);
if (configValue.Length > 0)
{
qry = configValue;
}
sourceDb.SQLQuery = qry;
sourceDb.CommandType = CommandType.Text;
DataTable dt = sourceDb.RunQueryDataTable();
_msg.Length = 0;
_msg.Append("Query text: ");
_msg.Append(sourceDb.SQLQuery);
_msg.Append(Environment.NewLine);
_msg.Append("Expected number of rows: ");
_msg.Append(tabInfo.numRows.ToString("#,##0"));
_msg.Append(Environment.NewLine);
_msg.Append("Number of rows returned: ");
_msg.Append(dt.Rows.Count.ToString("#,##0"));
Program._messageLog.WriteLine(_msg.ToString());
//import data from DataTable into destination database
if (outputTablesSchema.Trim().Length > 0)
{
destinationTableName = outputTablesSchema + "." + tabInfo.tableName;
}
else
{
destinationTableName = tabInfo.tableName;
}
//workaround to fix issue with Oracle limit on identifier lengths
if (destinationDb.DbPlatform == DatabasePlatform.OracleNative || destinationDb.DbPlatform == DatabasePlatform.MSOracle)
{
if (tabInfo.tableName == "FactAdditionalInternationalProductDescription")
{
if (outputTablesSchema.Trim().Length > 0)
{
destinationTableName = outputTablesSchema + "." + "FactProductDescriptionExt";
}
else
{
destinationTableName = "FactProductDescriptionExt";
}
}
}
dt.TableName = destinationTableName;
_msg.Length = 0;
_msg.Append(destinationTableName);
if (replaceExistingTables)
{
if (destinationDb.TableExists(destinationTableName))
{
destinationDb.DropTable(destinationTableName);
if (destinationDb.TableExists(destinationTableName) == false)
{
_msg.Append(" dropped.");
}
else
{
_msg.Append(" drop failed.");
}
}
else
{
_msg.Append(" does not exist.");
}
}
else
{
_msg.Append(": No check was made to determine if table already existed.");
}
Program._messageLog.WriteLine(_msg.ToString());
Program._messageLog.WriteLine("Creating table in the database ...");
//create the table
string createScript = string.Empty;
string errorMessages = string.Empty;
bool createSucceeded = true;
createSucceeded = destinationDb.CreateTable(dt, out createScript, out errorMessages);
_msg.Length = 0;
_msg.Append("Create table result: ");
_msg.Append(createSucceeded.ToString());
_msg.Append(Environment.NewLine);
if (errorMessages.Trim().Length > 0)
{
_msg.Append("Error Messages: ");
_msg.Append(Environment.NewLine);
_msg.Append(errorMessages);
_msg.Append(Environment.NewLine);
}
_msg.Append("Create table statement: ");
_msg.Append(Environment.NewLine);
_msg.Append(createScript);
_msg.Append(Environment.NewLine);
Program._messageLog.WriteLine(_msg.ToString());
if (createSucceeded == false)
{
numTableCreateErrors++;
}
else
{
Program._messageLog.WriteLine("Importing data table to the database ...");
_msg.Length = 0;
_msg.Append("Data Write batch size: ");
_msg.Append(batchSizeForDataWrites.ToString());
Program._messageLog.WriteLine(_msg.ToString());
try
{
if (batchSizeForDataWrites == 1)
{
destinationDb.ImportDataFromDataTable(dt);
}
else
{
destinationDb.ImportDataFromDataTable(dt, batchSizeForDataWrites);
}
_msg.Length = 0;
_msg.Append("Table imported: ");
_msg.Append(dt.TableName);
_msg.Append(Environment.NewLine);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
numImportErrors++;
_msg.Length = 0;
_msg.Append("ERROR: Data import failed:");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
}
finally
{
;
}
}
}
}
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;
if (numTableCreateErrors > 0)
{
_msg.Append("One or more table create errors were reported: ");
_msg.Append(numTableCreateErrors.ToString());
_msg.Append(Environment.NewLine);
}
if (numImportErrors > 0)
{
_msg.Append("One or more data import errors were reported: ");
_msg.Append(numImportErrors.ToString());
_msg.Append(Environment.NewLine);
}
if (_msg.Length > 0)
{
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString());
}
}
}