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());
}
}
/// <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);
}
//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());
}
}