private bool WriteMessageToDatabaseRetryQueue(PFLogMessage logMessage)
{
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
bool logWriteSucceeded = false;
try
{
logWriteSucceeded = false;
}
catch
{
logWriteSucceeded = false;
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
}
if (!logWriteSucceeded)
{
SaveLogMessageToRetryQueue(logMessage);
}
return(logWriteSucceeded);
}
/// <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);
}
//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;
}
private bool CreateSdfFile(string filepath)
{
bool fileCreated = false;
PFDatabase db = null;
StringBuilder connectionString = new StringBuilder();
try
{
db = new PFDatabase(DatabasePlatform.SQLServerCE40);
//"data source='C:\Testfiles\Randomizer\TestTables.sdf';"
connectionString.Length = 0;
connectionString.Append("data source='");
connectionString.Append(filepath);
connectionString.Append("';");
fileCreated = db.CreateDatabase(connectionString.ToString());
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
AppMessages.DisplayErrorMessage(_msg.ToString());
}
finally
{
;
}
return(fileCreated);
}
private void InitDatabaseObject()
{
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
string configValue = AppConfig.GetStringValueFromConfigFile(_dbPlatform.ToString(), string.Empty);
if (configValue.Length > 0)
{
string[] parsedConfig = configValue.Split('|');
if (parsedConfig.Length != 3)
{
_msg.Length = 0;
_msg.Append("Invalid config entry items for ");
_msg.Append(_dbPlatform.ToString());
_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];
_db = new PFDatabase(_dbPlatform.ToString(), dllPath, nmSpace + "." + clsName);
}
else
{
_db = new PFDatabase(_dbPlatform);
}
}
private void GetPlatformPropertiesAndKeys()
{
DatabasePlatform dbPlat = _frm.DbPlatform;
PFDatabase db = InitDatabaseObject(dbPlat);
db.ConnectionString = _frm.ConnectionString;
_connectionDefinition.DbPlatformConnectionStringProperties = db.GetPropertiesForPlatform();
_connectionDefinition.ConnectionKeyElements = db.ConnectionStringKeyVals;
}
//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);
}
}//end method
private PFDatabase GetPFDatabaseObject(DatabasePlatform dbPlat)
{
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
dbPlatformDesc = dbPlat.ToString();
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
if (configValue.Trim() == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find config entry for ");
_msg.Append(dbPlatformDesc);
throw new System.Exception(_msg.ToString());
}
string[] parsedConfig = configValue.Split('|');
if (parsedConfig.Length != 3)
{
_msg.Length = 0;
_msg.Append("Invalid config entry items for ");
_msg.Append(dbPlatformDesc);
_msg.Append(". Number of items after parse: ");
_msg.Append(parsedConfig.Length.ToString());
_msg.Append(".");
throw new System.Exception(_msg.ToString());
}
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
return(db);
}
public static void GetSupportedDatabasesList(MainForm frm)
{
PFList <string> dblist = null;
try
{
_msg.Length = 0;
_msg.Append("GetSupportedDatabasesList started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
dblist = PFDatabase.GetListOfSupportedDatabases();
string tab = null;
dblist.SetToBOF();
while ((tab = dblist.NextItem) != null)
{
if (tab.ToUpper() != "UNKNOWN")
{
_msg.Length = 0;
_msg.Append(tab);
Program._messageLog.WriteLine(_msg.ToString());
}
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("\r\n... GetSupportedDatabasesList finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
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);
}
//tests
public static void GetTabDefList(MainForm frm)
{
//PFTableDefinitions tabDefs = new PFTableDefinitions();
PFList <PFTableDef> tabDefList = null;
PFDatabase db = null;
string dbAssemblyPath = string.Empty;
string[] includes = null;
string[] excludes = null;
string[] lineTerminators = { "\r\n", Environment.NewLine };
string dbPlatformDesc = frm.cboSourceDbPlatform.Text;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
try
{
_msg.Length = 0;
_msg.Append("GetTabDefList started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
if (configValue.Trim() == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find config entry for ");
_msg.Append(dbPlatformDesc);
throw new System.Exception(_msg.ToString());
}
string[] parsedConfig = configValue.Split('|');
if (parsedConfig.Length != 3)
{
_msg.Length = 0;
_msg.Append("Invalid config entry items for ");
_msg.Append(dbPlatformDesc);
_msg.Append(". Number of items after parse: ");
_msg.Append(parsedConfig.Length.ToString());
_msg.Append(".");
throw new System.Exception(_msg.ToString());
}
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty);
db = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath, nmSpace + "." + clsName);
}
else if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty);
db = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath, nmSpace + "." + clsName);
}
else
{
db = new PFDatabase(frm.cboSourceDbPlatform.Text, dllPath, nmSpace + "." + clsName);
}
db.ConnectionString = frm.cboSourceDbConnectionString.Text;
db.OpenConnection();
if (frm.txtIncludePatterns.Text.Trim().Length > 0)
{
includes = frm.txtIncludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
if (includes.Length > 0)
{
for (int i = 0; i < includes.Length; i++)
{
if (includes[i].Length == 0)
{
includes[i] = "ignore this include";
}
}
}
}
if (frm.txtExcludePatterns.Text.Trim().Length > 0)
{
excludes = frm.txtExcludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
if (excludes.Length > 1)
{
for (int i = 0; i < excludes.Length; i++)
{
if (excludes[i].Length == 0)
{
excludes[i] = "ignore this exclude";
}
}
}
}
tabDefList = db.GetTableList(includes, excludes);
PFTableDef td = null;
tabDefList.SetToBOF();
while ((td = tabDefList.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(td.TableFullName);
if (frm.chkShowTableCreateStatements.Checked)
{
_msg.Append(":\r\n");
_msg.Append(td.TableCreateStatement);
_msg.Append("\r\n");
}
Program._messageLog.WriteLine(_msg.ToString());
}
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
//tabDefs = null;
tabDefList = null;
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
_msg.Length = 0;
_msg.Append("\r\n... GetTabDefList finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
/// <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);
}
/// <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>
/// 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>
/// 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);
}
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());
}
}
/// <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);
}
/// <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 ConvertTableDefs(MainForm frm)
{
Stopwatch sw = new Stopwatch();
string dbAssemblyPath = string.Empty;
PFDatabase sourceDb = null;
string sourceConnectionString = frm.cboSourceDbConnectionString.Text;
PFDatabase destDb = null;
string destinationConnectionString = frm.cboDestinationDbConnectionString.Text;
PFList <PFTableDef> tabDefList = null;
string[] includes = null;
string[] excludes = null;
string[] lineTerminators = { "\r\n", Environment.NewLine };
try
{
_msg.Length = 0;
_msg.Append("ConvertTableDefs started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty);
sourceDb = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath);
}
else if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty);
sourceDb = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath);
}
else
{
sourceDb = new PFDatabase(frm.cboSourceDbPlatform.Text);
}
if (frm.cboDestinationDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty);
destDb = new PFDatabase(frm.cboDestinationDbPlatform.Text, dbAssemblyPath);
}
else if (frm.cboDestinationDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString())
{
dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty);
destDb = new PFDatabase(frm.cboDestinationDbPlatform.Text, dbAssemblyPath);
}
else
{
destDb = new PFDatabase(frm.cboDestinationDbPlatform.Text);
}
sourceDb.ConnectionString = sourceConnectionString;
sourceDb.OpenConnection();
if (frm.txtIncludePatterns.Text.Trim().Length > 0)
{
includes = frm.txtIncludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
}
if (frm.txtExcludePatterns.Text.Trim().Length > 0)
{
excludes = frm.txtExcludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
}
tabDefList = sourceDb.GetTableList(includes, excludes);
destDb.ConnectionString = destinationConnectionString;
destDb.OpenConnection();
PFList <PFTableDef> newTableDefs = destDb.ConvertTableDefs(tabDefList, frm.txtNewSchema.Text.Trim());
PFTableDef newtd = null;
newTableDefs.SetToBOF();
while ((newtd = newTableDefs.NextItem) != null)
{
_msg.Length = 0;
_msg.Append(newtd.TableFullName);
if (frm.chkShowTableCreateStatements.Checked)
{
_msg.Append(":\r\n");
_msg.Append(newtd.TableCreateStatement);
_msg.Append("\r\n");
}
Program._messageLog.WriteLine(_msg.ToString());
}
if (frm.chkRunConvertedTableCreateStatements.Checked)
{
sw.Start();
newTableDefs.SetToBOF();
int numTabsCreated = destDb.CreateTablesFromTableDefs(newTableDefs, true);
sw.Stop();
_msg.Length = 0;
_msg.Append("\r\nNumber of tables created: \r\n");
_msg.Append(numTabsCreated.ToString());
_msg.Append("\r\n");
_msg.Append("Elapsed time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Stop();
if (frm.chkImportDataFromSourceToDestination.Checked)
{
sw.Start();
PFList <TableCopyDetails> tableCopyLog = destDb.CopyTableDataFromTableDefs(sourceDb, includes, null, frm.txtNewSchema.Text.Trim(), true);
sw.Stop();
tableCopyLog.SetToBOF();
TableCopyDetails tcdetails = null;
while ((tcdetails = tableCopyLog.NextItem) != null)
{
_msg.Length = 0;
_msg.Append("Table: ");
_msg.Append(tcdetails.destinationTableName);
_msg.Append(", NumRowsCopied: ");
_msg.Append(tcdetails.numRowsCopied.ToString("#,##0"));
if (tcdetails.result != TableCopyResult.Success)
{
_msg.Append("\r\n ");
_msg.Append("Result: ");
_msg.Append(tcdetails.result.ToString());
_msg.Append(" Messages: ");
_msg.Append(tcdetails.messages);
}
Program._messageLog.WriteLine(_msg.ToString());
}
_msg.Length = 0;
_msg.Append("Elapsed time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
} //end import data routine
} //end table create routine
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (sourceDb != null)
{
if (sourceDb.IsConnected)
{
sourceDb.CloseConnection();
}
}
if (destDb != null)
{
if (destDb.IsConnected)
{
destDb.CloseConnection();
}
}
sourceDb = null;
destDb = null;
_msg.Length = 0;
_msg.Append("\r\n... ConvertTableDefs finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
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());
}
}
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);
}
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());
}
}
//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());
}
}
/// <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);
}
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());
}
}