//properties
//methods
/// <summary>
/// Saves the public property values contained in the current instance to the database specified by the connection string.
/// </summary>
/// <param name="kvlist">Key/Value list object to be saved to a database.</param>
/// <param name="connectionString">Contains information needed to open the database.</param>
/// <param name="listName">Name of the list in the database.</param>
public void SaveToDatabase(PFKeyValueList <K, V> kvlist, string connectionString, string listName)
{
string sqlStmt = string.Empty;
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
int numRecsAffected = 0;
DateTime currdate = DateTime.Now;
string currBatchId = string.Empty;
string listObject = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
//create batch id for this list
currBatchId = "'" + Guid.NewGuid().ToString().Trim() + "'";
listObject = kvlist.ToXmlString().Replace("'", ""); //get rid of any single quotes in the object. they will mess up the sql syntax e.g. values(1, 'two' ,'this is the 'object'')
//insert current list to the database
sqlStmt = _listsInsertSQL.Replace("<listname>", listName).Replace("<id>", currBatchId).Replace("<listobject>", listObject);
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
//get rid of any previous PFListEx objects in the database
sqlStmt = _listsDeleteOldSQL.Replace("<listname>", listName).Replace("<id>", currBatchId);
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
db.CloseConnection();
db = null;
}
/// <summary>
/// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
/// </summary>
/// <param name="connectionString">Connection parameters for the database.</param>
/// <param name="taskName">Name of the the to retrieve.</param>
/// <param name="actualStartTime">Actual start time for the history entry to be retrieved.</param>
/// <returns>TaskHistoryEntry object.</returns>
public static PFTaskHistoryEntry LoadFromDatabase(string connectionString, string taskName, DateTime actualStartTime)
{
string sqlStmt = string.Empty;
PFTaskHistoryEntry objectInstance = null;
PFTaskHistoryEntry tempObjectInstance = new PFTaskHistoryEntry();
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
string taskHistoryDefXml = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
sqlStmt = tempObjectInstance._taskHistoryDefinitionsSelectTaskEntrySQL.Replace("<taskname>", taskName);
sqlStmt = sqlStmt.Replace("<rundate>", actualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
while (rdr.Read())
{
taskHistoryDefXml = rdr.GetString(0);
objectInstance = PFTaskHistoryEntry.LoadFromXmlString(taskHistoryDefXml);
break; //should be only one record
}
return(objectInstance);
}
/// <summary>
/// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
/// </summary>
/// <param name="connectionString">Connection parameters for the database.</param>
/// <param name="listName">Name of the list in the database.</param>
/// <returns>PFListEx object.</returns>
public PFKeyValueList <K, V> LoadFromDatabase(string connectionString, string listName)
{
string sqlStmt = string.Empty;
PFKeyValueList <K, V> objectInstance = null;
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
string pfKeyValueListExXml = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
sqlStmt = _listsSelectSQL.Replace("<listname>", listName);
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
while (rdr.Read())
{
pfKeyValueListExXml = rdr.GetString(0);
objectInstance = PFKeyValueList <K, V> .LoadFromXmlString(pfKeyValueListExXml);
break; //should be only one record
}
db.CloseConnection();
db = null;
if (objectInstance == null)
{
objectInstance = new PFKeyValueList <K, V>();
}
return(objectInstance);
}
private void VerifyConnectionString()
{
if (this.txtConnectionString.Text.Trim() == string.Empty)
{
this.ConnectionString = BuildConnectionString();
if (this.txtConnectionString.Text.Trim() == string.Empty)
{
AppMessages.DisplayWarningMessage("You must specify a connection string. Fill in input fields and then use Build button to transform input fields into a connection string.");
return;
}
}
try
{
DisableFormControls();
this.Cursor = Cursors.WaitCursor;
_db.ConnectionString = this.ConnectionString;
_db.OpenConnection();
_msg.Length = 0;
if (_db.IsConnected)
{
this._connectionAccessStatus = enConnectionAccessStatus.IsAccessible;
_msg.Append("Connection successful!");
AppMessages.DisplayInfoMessage(_msg.ToString());
}
else
{
this._connectionAccessStatus = enConnectionAccessStatus.NotAccessible;
_msg.Append("Connection failed.");
AppMessages.DisplayErrorMessage(_msg.ToString());
}
}
catch (System.Exception ex)
{
this._connectionAccessStatus = enConnectionAccessStatus.Unknown;
_msg.Length = 0;
_msg.Append("Attempt to connect to database failed.");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
AppMessages.DisplayErrorMessage(_msg.ToString());
}
finally
{
if (_db.IsConnected)
{
_db.CloseConnection();
}
EnableFormControls();
this.Cursor = Cursors.Default;
this.Focus();
}
}
//methods
/// <summary>
/// Routine to return a list of table names for the database pointed to by the current connection string.
/// </summary>
/// <returns>Object containing list of table definitions.</returns>
public PFList <PFTableDef> GetTableList(string tablesToIncludePattern, string tablesToExcludePattern)
{
PFList <PFTableDef> tableList = null;
PFDatabase db = null;
string[] includePatterns = { string.Empty };
string[] excludePatterns = { string.Empty };
if (this.DbPlatform == DatabasePlatform.Unknown)
{
return(null);
}
if (this.ConnectionString.Length == 0)
{
return(null);
}
try
{
includePatterns[0] = tablesToIncludePattern.Trim();
excludePatterns[0] = tablesToExcludePattern.Trim();
db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
tableList = db.GetTableList(includePatterns, excludePatterns);
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to retrieve list of table names failed: ");
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
}
return(tableList);
}
/// <summary>
/// Saves the public property values contained in the current instance to the database specified by the connection string.
/// </summary>
/// <param name="connectionString">Contains information needed to open the database.</param>
/// <remarks>Task name must be unique in the database. SQL Server CE 3.5 local file used for database storage.</remarks>
public void SaveToDatabase(string connectionString)
{
string sqlStmt = string.Empty;
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
int numRecsFound = 0;
int numRecsAffected = 0;
db.ConnectionString = connectionString;
db.OpenConnection();
//check if already exists
sqlStmt = _taskHistoryDefinitionsIfTaskHistoryExistsSQL.Replace("<taskname>", this.TaskName);
sqlStmt = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
numRecsFound = 0;
while (rdr.Read())
{
numRecsFound = rdr.GetInt32(0);
break; //should be only one record
}
// if exists update it
if (numRecsFound > 0)
{
//update the record
sqlStmt = _taskHistoryDefinitionsUpdateSQL.Replace("<taskname>", this.TaskName);
sqlStmt = sqlStmt.Replace("<taskobject>", this.ToXmlString());
sqlStmt = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
}
else
{
//insert the new record
sqlStmt = _taskHistoryDefinitionsInsertSQL.Replace("<taskname>", this.TaskName);
sqlStmt = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
sqlStmt = sqlStmt.Replace("<taskobject>", this.ToXmlString());
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
}
db.CloseConnection();
}
private PFList <PFTaskHistoryEntry> GetTaskListDatabase(string taskName)
{
PFList <PFTaskHistoryEntry> taskHistoryEntryList = new PFList <PFTaskHistoryEntry>();
PFDatabase db = null;
string sqlStmt = string.Empty;
try
{
db = new PFDatabase(DatabasePlatform.SQLServerCE35);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
sqlStmt = _taskHistoryDefinitionsSelectTaskSQL.Replace("<taskname>", taskName);
DbDataReader rdr = db.RunQueryDataReader(sqlStmt, System.Data.CommandType.Text);
while (rdr.Read())
{
string str = rdr["TaskHistoryObject"].ToString();
PFTaskHistoryEntry the = PFTaskHistoryEntry.LoadFromXmlString(str);
taskHistoryEntryList.Add(the);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
}
return(taskHistoryEntryList);
}
private PFList <PFSchedule> GetScheduleListDatabase()
{
PFList <PFSchedule> scheduleList = new PFList <PFSchedule>();
PFDatabase db = null;
try
{
db = new PFDatabase(DatabasePlatform.SQLServerCE35);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
DbDataReader rdr = db.RunQueryDataReader(_scheduleDefinitionsSelectAllSQL, System.Data.CommandType.Text);
while (rdr.Read())
{
string str = rdr["ScheduleObject"].ToString();
PFSchedule schedule = PFSchedule.LoadFromXmlString(str);
scheduleList.Add(schedule);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
}
return(scheduleList);
}
private PFDatabase GetDbObject(DatabasePlatform dbPlatform, string dbConnectionString)
{
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
try
{
dbPlatformDesc = dbPlatform.ToString();
connStr = dbConnectionString;
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();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
;
}
return(db);
}
/// <summary>
/// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
/// </summary>
/// <param name="connectionString">Connection parameters for the database.</param>
/// <param name="taskName">Name of the task to retrieve.</param>
/// <returns>Task object.</returns>
public static PFTask LoadFromDatabase(string connectionString, string taskName)
{
string sqlStmt = string.Empty;
PFTask objectInstance = null;
PFTask tempObjectInstance = new PFTask();
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
string taskDefXml = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
sqlStmt = tempObjectInstance._taskDefinitionsSelectTaskSQL.Replace("<taskname>", taskName);
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
while (rdr.Read())
{
taskDefXml = rdr.GetString(0);
objectInstance = PFTask.LoadFromXmlString(taskDefXml);
break; //should be only one record
}
return(objectInstance);
}
/// <summary>
/// Routine to execute the specified query.
/// </summary>
/// <param name="queryDef">File containing the query definition.</param>
/// <param name="randomizeOutput">Set to true to enable randomizing of output.</param>
/// <param name="showRowNumber">Set to true to add a row number to the output generated by the query.</param>
public void RunQuery(pfQueryDef queryDef, bool randomizeOutput, bool showRowNumber)
{
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
{
_msg.Length = 0;
_msg.Append("RunQuery started at ");
_msg.Append(DateTime.Now.ToString());
WriteMessageToLog(_msg.ToString());
sw.Start();
if (queryDef.DatabaseType == DatabasePlatform.Unknown ||
queryDef.ConnectionString.Trim().Length == 0)
{
_msg.Length = 0;
_msg.Append("You must specify both a data source and a connection string for the query.");
throw new System.Exception(_msg.ToString());
}
if (queryDef.Query.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
dbPlatformDesc = queryDef.DatabaseType.ToString();
connStr = queryDef.ConnectionString;
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();
db.SQLQuery = queryDef.Query;
db.CommandType = CommandType.Text;
DataTable tab = db.RunQueryDataTable();
tab.TableName = queryDef.QueryName;
if (randomizeOutput)
{
PFList <DataTableRandomizerColumnSpec> colSpecs = queryDef.RandomizerColSpecs;
PFList <DataTableRandomizerColumnSpec> saveOrigColSpecs = queryDef.RandomizerColSpecs;
SyncColSpecsWithDataSchema(queryDef, ref colSpecs);
queryDef.RandomizerColSpecs = colSpecs;
DataTableRandomizer dtr = new DataTableRandomizer();
dtr.MessageLogUI = this.MessageLogUI;
dtr.RandomizeDataTableValues(tab, queryDef.RandomizerColSpecs, this.BatchSizeForRandomDataGeneration);
queryDef.RandomizerColSpecs = saveOrigColSpecs; //get rid of any changes made during runtime: some of the colspecs fields are used as work fields by the randomizer routines.
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Total time to execute the query: ");
_msg.Append(sw.FormattedElapsedTime);
WriteMessageToLog(_msg.ToString());
OutputResultToGrid(tab, showRowNumber);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (sw.StopwatchIsRunning)
{
sw.Stop();
}
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
_msg.Length = 0;
_msg.Append("RunQuery ended at ");
_msg.Append(DateTime.Now.ToString());
WriteMessageToLog(_msg.ToString());
}
}
/// <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);
}
//test routines
public static void ConnectionTest(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;
try
{
_msg.Length = 0;
_msg.Append("ConnectionTest 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);
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];
_msg.Length = 0;
_msg.Append("Platform: ");
_msg.Append(dbPlatformDesc);
_msg.Append("\r\nConnectionString: ");
_msg.Append(connStr);
_msg.Append("\r\nNamespace: ");
_msg.Append(nmSpace);
_msg.Append("\r\nClassName: ");
_msg.Append(clsName);
_msg.Append("\r\nDLL Path: ");
_msg.Append(dllPath);
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append("Attempting to connect to ");
_msg.Append(dbPlatformDesc);
Program._messageLog.WriteLine(_msg.ToString());
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
_msg.Length = 0;
if (db.IsConnected)
{
_msg.Append("Connection successful.");
}
else
{
_msg.Append("**Connection failed.");
}
Program._messageLog.WriteLine(_msg.ToString());
Program._messageLog.WriteLine(Environment.NewLine);
foreach (stKeyValuePair <string, string> kv in db.ConnectionStringKeyVals)
{
_msg.Length = 0;
_msg.Append(kv.Key + "=" + kv.Value);
Program._messageLog.WriteLine(_msg.ToString());
}
Program._messageLog.WriteLine(Environment.NewLine);
}
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 (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... ConnectionTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void DataSetTest(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
{
_msg.Length = 0;
_msg.Append("DataSetTest 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];
if (frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
_msg.Length = 0;
_msg.Append("Connecting to ");
_msg.Append(dbPlatformDesc);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
db.SQLQuery = frm.txtSQLQuery.Text;
db.CommandType = CommandType.Text;
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
DataSet ds1 = db.RunQueryDataSet();
db.returnResult += new PFDatabase.ResultDelegate(OutputResults);
db.ProcessDataSet(ds1);
sw.Stop();
_msg.Length = 0;
_msg.Append("Process Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
//Run data extract test
Program._messageLog.WriteLine("\r\nRunning data extract tests ...\r\n");
db.returnResultAsString += new PFDatabase.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\DatasetDelimitedTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
sw.Start();
DataSet ds = db.RunQueryDataSet();
db.ExtractDelimitedDataFromDataSet(ds, "~", "\r\n", true);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Delimiated Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\DatasetFixedLengthTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
ds = null;
sw.Start();
ds = db.RunQueryDataSet();
db.ExtractFixedLengthDataFromDataSet(ds, true, true, false);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Fixed Length Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
db.SaveDataSetToXmlSchemaFile(ds, @"c:\temp\Testds.xsd");
db.SaveDataSetToXmlFile(ds, @"c:\temp\Testds.xml");
db.SaveDataSetWithSchemaToXmlFile(ds, @"c:\temp\Testdsplus.xml");
DataSet ds2 = db.LoadXmlFileToDataSet(@"c:\temp\Testds.xml");;
int numRows = ds2.Tables[0].Rows.Count;
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataSetToXmlDocument(ds);
Program._messageLog.WriteLine(xmlDoc.OuterXml);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
ds = null;
}
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 (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... DataSetTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void DataReaderToDataTableTest(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
{
_msg.Length = 0;
_msg.Append("DataReaderToDataTableTest 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];
if (frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
_msg.Length = 0;
_msg.Append("Connecting to ");
_msg.Append(dbPlatformDesc);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
db.SQLQuery = frm.txtSQLQuery.Text;
db.CommandType = CommandType.Text;
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
DbDataReader rdr = (DbDataReader)db.RunQueryDataReader();
DataTable tab = db.ConvertDataReaderToDataTable(rdr);
Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString());
rdr.Close();
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow r = tab.Rows[i];
_msg.Length = 0;
int maxColInx = tab.Columns.Count - 1;
for (int ci = 0; ci <= maxColInx; ci++)
{
_msg.Append(tab.Columns[ci].ColumnName);
_msg.Append(": ");
_msg.Append(r[ci].ToString());
if (ci < maxColInx)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Table read time: ");
_msg.Append(sw.FormattedElapsedTime);
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 (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... DataReaderToDataTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
private bool WriteMessageToDatabaseLog(PFLogMessage logMessage, bool saveFailedWriteToRetryQueue)
{
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
bool logWriteSucceeded = false;
string sqlStmt = string.Empty;
string messageText = string.Empty;
string logObject = string.Empty;
try
{
db.ConnectionString = _logFileConnectionString;
db.OpenConnection();
if (logMessage.LogMessageDateTime == DateTime.MinValue)
{
logMessage.LogMessageDateTime = DateTime.Now;
}
messageText = logMessage.MessageText.Replace("'", "");
logObject = logMessage.ToXmlString().Replace("'", "");
sqlStmt = _dbLogInsertStatement.Replace("<LogEntryDateTime>", "'" + logMessage.LogMessageDateTime.ToString("MM/dd/yyyy HH:mm:ss") + "'")
.Replace("<ApplicationName>", "'" + logMessage.ApplicationName + "'")
.Replace("<MachineName>", "'" + logMessage.MachineName + "'")
.Replace("<Username>", "'" + logMessage.Username + "'")
.Replace("<MessageLevel>", "'" + logMessage.LogMessageType.ToString() + "'")
.Replace("<MessageText>", "'" + messageText + "'")
.Replace("<LogMessageObject>", "'" + logObject + "'")
;
int numRecsAffected = db.RunNonQuery(sqlStmt, System.Data.CommandType.Text);
if (numRecsAffected > 0)
{
logWriteSucceeded = true;
}
else
{
logWriteSucceeded = false;
}
}
catch
{
logWriteSucceeded = false;
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
}
if (!logWriteSucceeded && saveFailedWriteToRetryQueue)
{
SaveLogMessageToRetryQueue(logMessage);
}
return(logWriteSucceeded);
}
/// <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);
}
public static void CreateTableTest(MainForm frm)
{
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
DatabasePlatform dbPlatform = DatabasePlatform.Unknown;
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
Stopwatch sw = new Stopwatch();
string createScript = string.Empty;
string tableName = string.Empty;
StringBuilder sql = new StringBuilder();
PFUnitTestDataTable unitTestDt01 = null;
PFUnitTestDataTable unitTestDt02 = null;
try
{
_msg.Length = 0;
_msg.Append("CreateTableTest 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];
if (frm.txtTableName.Text.Length == 0)
{
throw new System.Exception("You must specify a table name.");
}
_msg.Length = 0;
_msg.Append("Connecting to ");
_msg.Append(dbPlatformDesc);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
dbPlatform = db.DbPlatform;
db.ConnectionString = connStr;
db.OpenConnection();
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
tableName = frm.txtTableName.Text;
string catalogName = string.Empty;
string schemaName = string.Empty;
string tabName = string.Empty;
string[] parsedTableName = tableName.Split('.');
if (parsedTableName.Length == 2)
{
schemaName = parsedTableName[0];
tabName = parsedTableName[1];
}
else if (parsedTableName.Length == 1)
{
tabName = parsedTableName[0];
}
else if (parsedTableName.Length == 3)
{
catalogName = parsedTableName[0];
schemaName = parsedTableName[1];
tabName = parsedTableName[2];
}
else
{
tabName = string.Empty;
}
if (db.TableExists(catalogName, schemaName, tabName))
{
bool dropped = db.DropTable(catalogName, schemaName, tabName);
if (dropped == false)
{
_msg.Length = 0;
_msg.Append("Unable to drop table ");
if (catalogName != string.Empty)
{
_msg.Append(catalogName);
_msg.Append(".");
}
if (schemaName != string.Empty)
{
_msg.Append(schemaName);
_msg.Append(".");
}
_msg.Append(tabName);
throw new DataException(_msg.ToString());
}
}
if (db.TableExists(catalogName, schemaName, tabName + "_02"))
{
bool dropped = db.DropTable(catalogName, schemaName, tabName + "_02");
if (dropped == false)
{
_msg.Length = 0;
_msg.Append("Unable to drop table ");
if (catalogName != string.Empty)
{
_msg.Append(catalogName);
_msg.Append(".");
}
if (schemaName != string.Empty)
{
_msg.Append(schemaName);
_msg.Append(".");
}
_msg.Append(tabName + "_02");
throw new DataException(_msg.ToString());
}
}
unitTestDt01 = new PFUnitTestDataTable(db, schemaName, tabName, true);
unitTestDt02 = new PFUnitTestDataTable(db, schemaName, tabName + "_02", true);
//---
_msg.Length = 0;
_msg.Append("Initializing TableColumns");
Program._messageLog.WriteLine(_msg.ToString());
//select which data types to include
List <KeyValuePair <string, string> > dataTypesToInclude = new List <KeyValuePair <string, string> >();
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int32", "1"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.String", "this is a string value"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int32", "1123456789"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt32", "3123456789"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int64", "23123456789"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt64", "8881234567889"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int16", "11123"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt16", "52432"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Double", "123456.7654"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Single", "321.234"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Decimal", "2123456789.22"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Char", "A"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Char[]", "ABCDEFGH"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Byte", "254"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.SByte", "125"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Byte[]", "UVWZYZ));"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Boolean", "true"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Object", "This is an object: be careful!"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.DateTime", "5/31/2013 13:54:25"));
dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Guid", "58a4a08d-6101-4393-86dc-b2a8db46ec0f"));
unitTestDt01.SetDataTypesToInclude(dataTypesToInclude);
unitTestDt01.SetDataTypeOptions("System.String", false, true, 75);
unitTestDt02.SetDataTypesToInclude(dataTypesToInclude);
unitTestDt02.SetDataTypeOptions("System.String", false, true, 75000);
//create the table
_msg.Length = 0;
_msg.Append("Creating tables");
Program._messageLog.WriteLine(_msg.ToString());
unitTestDt01.CreateTableFromTableColumns();
unitTestDt02.CreateTableFromTableColumns();
createScript = unitTestDt01.TableCreateScript;
//import data to database
_msg.Length = 0;
_msg.Append("Importing data to TestTable01");
Program._messageLog.WriteLine(_msg.ToString());
unitTestDt01.ImportTableToDatabase();
_msg.Length = 0;
_msg.Append("Importing data to TestTable02");
Program._messageLog.WriteLine(_msg.ToString());
unitTestDt02.ImportTableToDatabase();
//retrieve just created table and see what data types get assigned to data table columns
Program._messageLog.WriteLine("\r\nRead row just created for " + tableName + "\r\n");
sql.Length = 0;
sql.Append("select * from ");
sql.Append(tableName);
DataTable testTab = db.RunQueryDataTable(sql.ToString(), CommandType.Text);
for (int c = 0; c < testTab.Columns.Count; c++)
{
_msg.Length = 0;
_msg.Append(testTab.Columns[c].ColumnName);
_msg.Append(", ");
_msg.Append(testTab.Columns[c].DataType.FullName);
_msg.Append(", ");
_msg.Append(testTab.Columns[c].MaxLength.ToString());
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 (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... CreateTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public void TestRowFilter()
{
string tableName = "DimCustomer";
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
DataTable dt = null;
string configValue = string.Empty;
string configKey = string.Empty;
try
{
_msg.Length = 0;
_msg.Append("TestRowFilter started ...\r\n");
WriteToMessageLog(_msg.ToString());
//
configValue = AppConfig.GetStringValueFromConfigFile("DefaultConnection_SQLServerCE35", string.Empty);
if (configValue == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find config.sys entry for DefaultConnection_SQLServerCE35");
throw new System.Exception(_msg.ToString());
}
dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
connStr = configValue;
dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
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();
configKey = dbPlatformDesc + "_" + tableName;
configValue = AppConfig.GetStringValueFromConfigFile(configKey, string.Empty);
if (configValue == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find ");
_msg.Append(configKey);
_msg.Append(" in config.sys.");
AppMessages.DisplayErrorMessage(_msg.ToString());
return;
}
//db.SQLQuery = "select CustomerKey ,GeographyKey ,CustomerAlternateKey ,Title ,FirstName ,MiddleName ,LastName ,NameStyle ,BirthDate ,MaritalStatus ,Suffix ,Gender ,EmailAddress ,YearlyIncome ,TotalChildren ,NumberChildrenAtHome ,EnglishEducation ,SpanishEducation ,FrenchEducation ,EnglishOccupation ,SpanishOccupation ,FrenchOccupation ,HouseOwnerFlag ,NumberCarsOwned ,AddressLine1 ,AddressLine2 ,Phone ,DateFirstPurchase ,CommuteDistance from DimCustomer where 1=0";
db.SQLQuery = configValue;
db.CommandType = CommandType.Text;
//dt = db.GetQueryDataSchema();
dt = db.RunQueryDataTable();
DataView dv = new DataView(dt);
dv.RowFilter = "CustomerKey NOT < 11200 and CustomerKey NOT > 11600";
_msg.Length = 0;
_msg.Append("Num rows before view: ");
_msg.Append(dt.Rows.Count.ToString("#,##0"));
_msg.Append(Environment.NewLine);
_msg.Append("Num rows after view: ");
_msg.Append(dv.Count.ToString("#,##0"));
_msg.Append(Environment.NewLine);
WriteToMessageLog(_msg.ToString());
DataTable dt2 = dt.AsEnumerable().Skip(0).Take(50).CopyToDataTable();
_msg.Length = 0;
_msg.Append("Num rows after AsEnumerable.Take: ");
_msg.Append(dt2.Rows.Count.ToString("#,##0"));
_msg.Append(Environment.NewLine);
WriteToMessageLog(_msg.ToString());
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteToMessageLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
_msg.Length = 0;
_msg.Append("\r\n... TestRowFilter finished.");
WriteToMessageLog(_msg.ToString());
}
}
//application routines
public void ShowFixedLenInputColSpecForm(string tableName)
{
PFFixedLenColDefsInputForm frm = new PFFixedLenColDefsInputForm();
DialogResult res = DialogResult.None;
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
DataTable dt = null;
PFColumnDefinitionsExt colDefsExt = null;
string configValue = string.Empty;
string configKey = string.Empty;
try
{
_msg.Length = 0;
_msg.Append("ShowFixedLenInputColSpecForm started ...\r\n");
WriteToMessageLog(_msg.ToString());
configValue = AppConfig.GetStringValueFromConfigFile("DefaultConnection_SQLServerCE35", string.Empty);
if (configValue == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find config.sys entry for DefaultConnection_SQLServerCE35");
throw new System.Exception(_msg.ToString());
}
dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
connStr = configValue;
dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
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();
configKey = dbPlatformDesc + "_" + tableName;
configValue = AppConfig.GetStringValueFromConfigFile(configKey, string.Empty);
if (configValue == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find ");
_msg.Append(configKey);
_msg.Append(" in config.sys.");
AppMessages.DisplayErrorMessage(_msg.ToString());
return;
}
//db.SQLQuery = "select CustomerKey ,GeographyKey ,CustomerAlternateKey ,Title ,FirstName ,MiddleName ,LastName ,NameStyle ,BirthDate ,MaritalStatus ,Suffix ,Gender ,EmailAddress ,YearlyIncome ,TotalChildren ,NumberChildrenAtHome ,EnglishEducation ,SpanishEducation ,FrenchEducation ,EnglishOccupation ,SpanishOccupation ,FrenchOccupation ,HouseOwnerFlag ,NumberCarsOwned ,AddressLine1 ,AddressLine2 ,Phone ,DateFirstPurchase ,CommuteDistance from DimCustomer where 1=0";
db.SQLQuery = configValue;
db.CommandType = CommandType.Text;
dt = db.GetQueryDataSchema();
//colDefsExt = PFColumnDefinitionsExt.GetColumnDefinitionsExt(dt);
colDefsExt = new PFColumnDefinitionsExt(1);
frm.ColDefs = colDefsExt;
frm.MessageLogUI = _messageLog;
res = frm.ShowDialog();
colDefsExt = frm.ColDefs;
_msg.Length = 0;
_msg.Append("Form closed with DialogResult = ");
_msg.Append(res.ToString());
WriteToMessageLog(_msg.ToString());
if (res == DialogResult.OK)
{
_msg.Length = 0;
_msg.Append(Environment.NewLine);
_msg.Append("COLUMN DEFINITIONS:\r\n\r\n");
_msg.Append(colDefsExt.ToXmlString());
_msg.Append(Environment.NewLine);
_msg.Append(Environment.NewLine);
WriteToMessageLog(_msg.ToString());
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteToMessageLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
if (frm != null)
{
if (FormIsOpen(frm.Name))
{
frm.Close();
}
}
frm = null;
_msg.Length = 0;
_msg.Append("\r\n... ShowFixedLenInputColSpecForm finished.");
WriteToMessageLog(_msg.ToString());
}
}
public static void GetQueryDataSchema(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
{
_msg.Length = 0;
_msg.Append("GetQueryDataSchema 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];
if (frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
_msg.Length = 0;
_msg.Append("Connecting to ");
_msg.Append(dbPlatformDesc);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
db.SQLQuery = frm.txtSQLQuery.Text;
db.CommandType = CommandType.Text;
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
DataTable tab = db.RunQueryDataTable();
foreach (DataColumn col in tab.Columns)
{
_msg.Length = 0;
_msg.Append(col.ColumnName);
_msg.Append(", ");
_msg.Append(col.DataType.ToString());
Program._messageLog.WriteLine(_msg.ToString());
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Get Query Data Schema time: ");
_msg.Append(sw.FormattedElapsedTime);
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 (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... GetQueryDataSchema finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
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());
}
}
/// <summary>
/// Generates a custom random value list based on the criteria supplied in the dataRequest object.
/// </summary>
/// <param name="dataRequest">Object containing criteria for generating random data values.</param>
/// <param name="generateXmlFile">If true, an Xml file will be created containing the generated data values. If false, only the generateSQL and customListDataTableValues will be generated.</param>
/// <param name="generatedSQL">Will contain the SQL statement used to generrate the random data values.</param>
/// <param name="customListDataTableValues">DataTable containing the custom data values and their frequencies.</param>
/// <returns>True if successful.</returns>
public bool GenerateCustomRandomDataList(RandomCustomValuesDataRequest dataRequest, bool generateXmlFile, out string generatedSQL, out DataTable customListDataTableValues)
{
bool success = false;
PFDatabase db = null;
string sqlStatement = string.Empty;
DataTable dt = null;
int valueInx = 0;
int frequencyInx = 1;
int adjustedFrequencyInx = 2;
int adjustmentNumberInx = 3;
//_msg.Length = 0;
//_msg.Append("GenerateCustomRandomDataList not yet implemented.");
//_msg.Append(Environment.NewLine);
//_msg.Append("+ " + dataRequest.ListName + Environment.NewLine);
//_msg.Append("+ " + dataRequest.CustomDataListFolder + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbPlatform.ToString() + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbConnectionString + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbTableName + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbFieldName + Environment.NewLine);
//AppMessages.DisplayAlertMessage(_msg.ToString());
//connect to database
//build sql statement (include adjustedfrequency and adjustmentnumber columns)
//exec sql statement
//get total from data table for adjustedfrequency
//start with divide by 10 and continue adding 10 until sum of adjustedfrequency is <=1500
// if divide results in zero, set to 1
//Update adjustedfrequency and adjustmentnumber on each row after sum <= 1500 found
//write out .xml file with each value repeated ajustedfrequency times
//save summary table to an access .rdatasum file (xml format) (or an Access .mdb or an text file (delimited or fixedlength))
//save request definition to a .rdatadef file (xml format)
this.DbPlatform = dataRequest.DbPlatform;
this.ConnectionString = dataRequest.DbConnectionString;
generatedSQL = string.Empty;
customListDataTableValues = null;
if (this.DbPlatform == DatabasePlatform.Unknown)
{
return(false);
}
if (this.ConnectionString.Length == 0)
{
return(false);
}
try
{
db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
sqlStatement = BuildSQLStatement(dataRequest);
generatedSQL = sqlStatement;
dt = db.RunQueryDataTable(sqlStatement, CommandType.Text);
dt.TableName = dataRequest.ListName;
if (dt.Rows.Count < 1)
{
_msg.Length = 0;
_msg.Append("No rows returned from query for random data: ");
_msg.Append(Environment.NewLine);
_msg.Append(sqlStatement);
_msg.Append(Environment.NewLine);
//AppMessages.DisplayWarningMessage(_msg.ToString());
throw new System.Exception(_msg.ToString());
}
AdjustFrequencies(ref dt, valueInx, frequencyInx, adjustedFrequencyInx, adjustmentNumberInx);
if (generateXmlFile)
{
OutputRandomDataFile(dataRequest, ref dt, sqlStatement, valueInx, frequencyInx, adjustedFrequencyInx, adjustmentNumberInx);
}
customListDataTableValues = dt;
db.CloseConnection();
success = true;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Error occurred while attempting to create custom random data file: ");
_msg.Append(Environment.NewLine);
_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>
/// Retrieves a DataTable object containing the query schema for the specified query.
/// </summary>
/// <param name="queryDef">File containing query definition.</param>
/// <returns>DataTable object containing the schema information.</returns>
public DataTable GetQueryDefSchema(pfQueryDef queryDef)
{
DataTable dt = null;
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
DatabasePlatform dbPlat = DatabasePlatform.Unknown;
string dbConnStr = string.Empty;
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
try
{
dbPlat = queryDef.DatabaseType;
if (queryDef.DatabaseType == DatabasePlatform.Unknown ||
queryDef.ConnectionString.Trim().Length == 0)
{
_msg.Length = 0;
_msg.Append("You must specify both a data source and a connection string for the query.");
throw new System.Exception(_msg.ToString());
}
if (queryDef.Query.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
dbPlatformDesc = queryDef.DatabaseType.ToString();
connStr = queryDef.ConnectionString;
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();
db.SQLQuery = queryDef.Query;
db.CommandType = CommandType.Text;
dt = db.GetQueryDataSchema(queryDef.Query, CommandType.Text);
dt.TableName = queryDef.QueryName;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
}
return(dt);
}
public void ColSpecForm()
{
DataTable dt = null;
TEST_DataTableRandomizer dtr = null;
string dbConnStr = string.Empty;
DatabasePlatform dbPlat = DatabasePlatform.Unknown;
PFDatabase db = null;
PFList <DataTableRandomizerColumnSpec> colSpecs = null;
RandomNamesAndLocationsDataRequest randomizerNameSpecs = null;
try
{
_msg.Length = 0;
_msg.Append("ColSpecForm started ...\r\n");
_messageLog.WriteLine(_msg.ToString());
dbPlat = DatabasePlatform.SQLServerCE35;
db = GetPFDatabaseObject(dbPlat);
db.ConnectionString = GetConnectionString(dbPlat);
randomizerNameSpecs = RandomNamesAndLocationsDataRequest.LoadFromXmlFile(@"C:\Testfiles\Randomizer\CountryRequestPersonsOnly.xml");
db.OpenConnection();
string sqlQuery = "select * from RandomNameData";
//listTable = db.RunQueryDataTable(sqlQuery, CommandType.Text);
dt = db.GetQueryDataSchema(sqlQuery, CommandType.Text);
dtr = new TEST_DataTableRandomizer();
colSpecs = dtr.GetInitColSpecListFromDataTable(dt);
PFRandomDataForms.DataTableRandomizerColumnSpecForm frm = new PFRandomDataForms.DataTableRandomizerColumnSpecForm(colSpecs);
DialogResult res = frm.ShowDialog();
if (res == DialogResult.OK)
{
//time to do something
_msg.Length = 0;
_msg.Append(frm.ColSpecs.ToXmlString());
_messageLog.WriteLine(_msg.ToString());
}
else
{
_msg.Length = 0;
_msg.Append("Column spec edit cancelled.");
_messageLog.WriteLine(_msg.ToString());
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
_messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("\r\n... ColSpecForm finished.");
_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());
}
}
/// <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);
}
