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>
/// 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);
}
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 static void DataTableTest(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("DataTableTest 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 tab1 = db.RunQueryDataTable();
db.returnResult += new PFDatabase.ResultDelegate(OutputResults);
db.ProcessDataTable(tab1);
sw.Stop();
_msg.Length = 0;
_msg.Append("Process Table time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\TableTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
//sw.Start();
DataTable tab = db.RunQueryDataTable();
//db.returnResultAsString += new PFdb.ResultAsStringDelegate(OutputExtractFormattedData);
db.returnResultAsString += new PFDatabase.ResultAsStringDelegate(OutputResultsToFile);
db.ExtractDelimitedDataFromTable(tab, ",", "\r\n", true);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Delimiated Table time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
tab = null;
sw.Start();
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\TableTestExtractFXL.txt", PFFileOpenOperation.OpenFileForWrite);
tab = db.RunQueryDataTable();
db.ExtractFixedLengthDataFromTable(tab, true, true, false);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Fixed Length Table time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
db.SaveDataTableToXmlSchemaFile(tab, @"c:\temp\Testtab.xsd");
db.SaveDataTableToXmlFile(tab, @"c:\temp\Testtab.xml");
db.SaveDataTableWithSchemaToXmlFile(tab, @"c:\temp\Testtabplus.xml");
DataTable tab2 = db.LoadXmlFileToDataTable(@"c:\temp\Testtabplus.xml");;
int numRows = tab2.Rows.Count;
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(tab);
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
}
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... DataTableTest finished.");
Program._messageLog.WriteLine(_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());
}
}
private void CopyTables(PFDatabase sourceDb, PFDatabase destinationDb, bool replaceExistingTables, int batchSizeForDataWrites, string outputTablesSchema)
{
string sourceQuery = string.Empty;
string destinationTableName = string.Empty;
int numTableCreateErrors = 0;
int numImportErrors = 0;
Stopwatch sw = new Stopwatch();
try
{
if (sourceDb.DbPlatform == DatabasePlatform.SQLServerCE35)
{
sourceQuery = _sourceQuery.Replace("dbo.", string.Empty);
}
else
{
sourceQuery = _sourceQuery;
}
_msg.Length = 0;
_msg.Append("Output Database Platform: ");
_msg.Append(destinationDb.DbPlatform.ToString());
_msg.Append(Environment.NewLine);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
foreach (stTableInfo tabInfo in _tableInfo)
{
//load data from source database into DataTable
string qry = sourceQuery.Replace(@"<tablename>", tabInfo.tableName);
string configValue = AppConfig.GetStringValueFromConfigFile(destinationDb.DbPlatform.ToString() + "_" + tabInfo.tableName, string.Empty);
if (configValue.Length > 0)
{
qry = configValue;
}
sourceDb.SQLQuery = qry;
sourceDb.CommandType = CommandType.Text;
DataTable dt = sourceDb.RunQueryDataTable();
_msg.Length = 0;
_msg.Append("Query text: ");
_msg.Append(sourceDb.SQLQuery);
_msg.Append(Environment.NewLine);
_msg.Append("Expected number of rows: ");
_msg.Append(tabInfo.numRows.ToString("#,##0"));
_msg.Append(Environment.NewLine);
_msg.Append("Number of rows returned: ");
_msg.Append(dt.Rows.Count.ToString("#,##0"));
Program._messageLog.WriteLine(_msg.ToString());
//import data from DataTable into destination database
if (outputTablesSchema.Trim().Length > 0)
{
destinationTableName = outputTablesSchema + "." + tabInfo.tableName;
}
else
{
destinationTableName = tabInfo.tableName;
}
//workaround to fix issue with Oracle limit on identifier lengths
if (destinationDb.DbPlatform == DatabasePlatform.OracleNative || destinationDb.DbPlatform == DatabasePlatform.MSOracle)
{
if (tabInfo.tableName == "FactAdditionalInternationalProductDescription")
{
if (outputTablesSchema.Trim().Length > 0)
{
destinationTableName = outputTablesSchema + "." + "FactProductDescriptionExt";
}
else
{
destinationTableName = "FactProductDescriptionExt";
}
}
}
dt.TableName = destinationTableName;
_msg.Length = 0;
_msg.Append(destinationTableName);
if (replaceExistingTables)
{
if (destinationDb.TableExists(destinationTableName))
{
destinationDb.DropTable(destinationTableName);
if (destinationDb.TableExists(destinationTableName) == false)
{
_msg.Append(" dropped.");
}
else
{
_msg.Append(" drop failed.");
}
}
else
{
_msg.Append(" does not exist.");
}
}
else
{
_msg.Append(": No check was made to determine if table already existed.");
}
Program._messageLog.WriteLine(_msg.ToString());
Program._messageLog.WriteLine("Creating table in the database ...");
//create the table
string createScript = string.Empty;
string errorMessages = string.Empty;
bool createSucceeded = true;
createSucceeded = destinationDb.CreateTable(dt, out createScript, out errorMessages);
_msg.Length = 0;
_msg.Append("Create table result: ");
_msg.Append(createSucceeded.ToString());
_msg.Append(Environment.NewLine);
if (errorMessages.Trim().Length > 0)
{
_msg.Append("Error Messages: ");
_msg.Append(Environment.NewLine);
_msg.Append(errorMessages);
_msg.Append(Environment.NewLine);
}
_msg.Append("Create table statement: ");
_msg.Append(Environment.NewLine);
_msg.Append(createScript);
_msg.Append(Environment.NewLine);
Program._messageLog.WriteLine(_msg.ToString());
if (createSucceeded == false)
{
numTableCreateErrors++;
}
else
{
Program._messageLog.WriteLine("Importing data table to the database ...");
_msg.Length = 0;
_msg.Append("Data Write batch size: ");
_msg.Append(batchSizeForDataWrites.ToString());
Program._messageLog.WriteLine(_msg.ToString());
try
{
if (batchSizeForDataWrites == 1)
{
destinationDb.ImportDataFromDataTable(dt);
}
else
{
destinationDb.ImportDataFromDataTable(dt, batchSizeForDataWrites);
}
_msg.Length = 0;
_msg.Append("Table imported: ");
_msg.Append(dt.TableName);
_msg.Append(Environment.NewLine);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
numImportErrors++;
_msg.Length = 0;
_msg.Append("ERROR: Data import failed:");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
}
finally
{
;
}
}
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
if (numTableCreateErrors > 0)
{
_msg.Append("One or more table create errors were reported: ");
_msg.Append(numTableCreateErrors.ToString());
_msg.Append(Environment.NewLine);
}
if (numImportErrors > 0)
{
_msg.Append("One or more data import errors were reported: ");
_msg.Append(numImportErrors.ToString());
_msg.Append(Environment.NewLine);
}
if (_msg.Length > 0)
{
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString());
}
}
}
