private void InitDbDef(DatabasePlatform dbPlatform)
{
int rowInx = 0;
int maxRowInx = _dbNamesAndLocations.GetLength(0) - 1;
_dbPlatform = dbPlatform;
_dbPlatformDesc = dbPlatform.ToString();
string configValue = AppConfig.GetStringValueFromConfigFile(_dbPlatformDesc, string.Empty);
if (configValue.Length > 0)
{
string[] parsedConfig = configValue.Split('|');
_dbNamespace = parsedConfig[0];
_dbClassName = parsedConfig[1];
_dbDllPath = parsedConfig[2];
}
else
{
for (rowInx = 0; rowInx <= maxRowInx; rowInx++)
{
if (_dbNamesAndLocations[rowInx, _dbPlatformDescInx].ToLower() == _dbPlatformDesc.ToLower())
{
_dbNamespace = _dbNamesAndLocations[rowInx, _dbNamespaceInx];
_dbClassName = _dbNamesAndLocations[rowInx, _dbClassNameInx];
_dbDllPath = _dbNamesAndLocations[rowInx, _dbDllPathInx];
}
}
}
}
private void DefineConnectionString()
{
PFConnectionManager connMgr = null;
DatabasePlatform dbPlat = DatabasePlatform.Unknown;
ConnectionStringPrompt cp = null;
try
{
dbPlat = (DatabasePlatform)Enum.Parse(typeof(DatabasePlatform), this.cboDatabaseTypeForOutputTables.Text);
connMgr = new PFConnectionManager();
cp = new ConnectionStringPrompt(dbPlat, connMgr);
cp.ConnectionString = this.txtConnectionStringForOutputTables.Text;
System.Windows.Forms.DialogResult res = cp.ShowConnectionPrompt();
if (res == DialogResult.OK)
{
this.txtConnectionStringForOutputTables.Text = cp.ConnectionString;
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
;
}
}
/// <summary>
/// Gets migrator dialect for specified <paramref name="platform"/>.
/// </summary>
/// <param name="platform">Database platform.</param>
/// <returns>Migrator dialect for specified <paramref name="platform"/>.</returns>
public static String GetDialect(DatabasePlatform platform)
{
var dialect = String.Empty;
if (migratorDialects.ContainsKey(platform))
{
dialect = migratorDialects[platform];
}
return dialect;
}
private void GetPlatformPropertiesAndKeys()
{
DatabasePlatform dbPlat = _frm.DbPlatform;
PFDatabase db = InitDatabaseObject(dbPlat);
db.ConnectionString = _frm.ConnectionString;
_connectionDefinition.DbPlatformConnectionStringProperties = db.GetPropertiesForPlatform();
_connectionDefinition.ConnectionKeyElements = db.ConnectionStringKeyVals;
}
/// <summary>
/// Gets migrator dialect for specified <paramref name="platform"/>.
/// </summary>
/// <param name="platform">Database platform.</param>
/// <returns>Migrator dialect for specified <paramref name="platform"/>.</returns>
public static String GetDialect(DatabasePlatform platform)
{
var dialect = String.Empty;
if (migratorDialects.ContainsKey(platform))
{
dialect = migratorDialects[platform];
}
return(dialect);
}
/// <summary>
/// Routine to create an instance of the PFQueryBuilder class.
/// </summary>
/// <param name="dbPlat">Database platform to use.</param>
/// <param name="connStr">Connection string for the query.</param>
/// <param name="ansiLevel">AnsiSQLLevel for the query builder to use.</param>
/// <returns>Object that implements ISQLBuilder interface.</returns>
public static ISQLBuilder CreateQueryBuilderObject(DatabasePlatform dbPlat, string connStr, AnsiSQLLevel ansiLevel)
{
PFQueryBuilder qbf = new PFQueryBuilder();
qbf.DatabasePlatform = qbf.ConvertDbPlatformToQueryBuilderPlatform(dbPlat);
qbf.ConnectionString = connStr;
qbf.AnsiSQLVersion = ansiLevel;
return(qbf);
}
private void RefreshProviderOption(DatabasePlatform dbPlat, RadioButton opt)
{
PFConnectionManager connMgr = new PFConnectionManager();
//test
//if (this.chkShowInstalledProvidersOnly.Checked)
//{
// if (dbPlat == DatabasePlatform.Informix
// || dbPlat == DatabasePlatform.SQLAnywhereUltraLite
// || dbPlat == DatabasePlatform.SQLServerCE40)
// {
// opt.Visible = false;
// return;
// }
//}
//end test
if (connMgr.ProviderDefinitions.Count == 0)
{
connMgr.CreateProviderDefinitions();
}
else
{
connMgr.UpdateAllProvidersInstallationStatus();
}
if (this.chkShowInstalledProvidersOnly.Checked)
{
for (int i = 0; i < connMgr.ProviderDefinitions.Count; i++)
{
PFProviderDefinition provDef = connMgr.ProviderDefinitions[i].Value;
if (provDef.DbPlatform == dbPlat)
{
if (provDef.InstallationStatus == enProviderInstallationStatus.IsInstalled)
{
opt.Visible = true;
}
else
{
opt.Visible = false;
}
break;
}
}//end for loop
}
else
{
opt.Visible = true;
}
}
//application routines
private void RunCopy()
{
try
{
DisableFormControls();
_appProcessor.SaveErrorMessagesToAppLog = _saveErrorMessagesToAppLog;
this.Cursor = Cursors.WaitCursor;
if (this.chkEraseOutputBeforeEachTest.Checked)
{
Program._messageLog.Clear();
}
if (optSQLServerSV2.Checked)
{
_sourceDbPlatform = DatabasePlatform.MSSQLServer;
_sourceConnectionString = _sv2ConnectionString;
}
else if (optSQLServerWS3.Checked)
{
_sourceDbPlatform = DatabasePlatform.MSSQLServer;
_sourceConnectionString = _ws3ConnectionString;
}
else
{
_sourceDbPlatform = DatabasePlatform.SQLServerCE35;
_sourceConnectionString = _ce35ConnectionString;
}
DatabasePlatform destDbPlatform = (DatabasePlatform)Enum.Parse(typeof(DatabasePlatform), this.cboDatabaseTypeForOutputTables.Text);
_appProcessor.CopyAdventureWorksDW(_sourceDbPlatform, _sourceConnectionString,
destDbPlatform, this.txtConnectionStringForOutputTables.Text,
this.chkReplaceExistingTables.Checked, PFTextProcessor.ConvertStringToInt(this.txtOutputBatchSize.Text, 120), this.txtOutputTablesSchema.Text);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
EnableFormControls();
this.Cursor = Cursors.Default;
this.Focus();
}
}
/// <summary>
/// Converts a DatabasePlatform enum value to its equivalent QueryBuilderDatabasePlatform enum value.
/// </summary>
/// <param name="dbPlat">DatabasePlatform enum value to convert.</param>
/// <returns>QueryBuilderDatabasePlatform enum value.</returns>
public QueryBuilderDatabasePlatform ConvertDbPlatformToQueryBuilderPlatform(DatabasePlatform dbPlat)
{
QueryBuilderDatabasePlatform qbPlat = QueryBuilderDatabasePlatform.Unknown;
switch (dbPlat)
{
case PFDataAccessObjects.DatabasePlatform.SQLAnywhereUltraLite:
qbPlat = QueryBuilderDatabasePlatform.SQLAnywhereUL;
break;
default:
qbPlat = QueryBuilderDatabasePlatform.Universal;
break;
}
return(qbPlat);
}
/// <summary>
/// Converts a DatabasePlatform enum value to its equivalent QueryBuilderDatabasePlatform enum value.
/// </summary>
/// <param name="dbPlat">DatabasePlatform enum value to convert.</param>
/// <returns>QueryBuilderDatabasePlatform enum value.</returns>
public QueryBuilderDatabasePlatform ConvertDbPlatformToQueryBuilderPlatform(DatabasePlatform dbPlat)
{
QueryBuilderDatabasePlatform qbPlat = QueryBuilderDatabasePlatform.Unknown;
switch (dbPlat)
{
case PFDataAccessObjects.DatabasePlatform.OracleNative:
qbPlat = QueryBuilderDatabasePlatform.Oracle;
break;
default:
qbPlat = QueryBuilderDatabasePlatform.Universal;
break;
}
return(qbPlat);
}
}//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);
}
private void DefineConnectionString(ComboBox cboDatabaseType, TextBox txtConnectionString)
{
PFConnectionManager connMgr = null;
DatabasePlatform dbPlat = DatabasePlatform.Unknown;
ConnectionStringPrompt cp = null;
if (cboDatabaseType.Text.Trim().Length == 0)
{
_msg.Length = 0;
_msg.Append("You must specify a database type before defining a connection string.");
AppMessages.DisplayWarningMessage(_msg.ToString());
return;
}
try
{
dbPlat = (DatabasePlatform)Enum.Parse(typeof(DatabasePlatform), cboDatabaseType.Text);
connMgr = new PFConnectionManager();
cp = new ConnectionStringPrompt(dbPlat, connMgr);
cp.ConnectionString = txtConnectionString.Text;
System.Windows.Forms.DialogResult res = cp.ShowConnectionPrompt();
if (res == DialogResult.OK)
{
txtConnectionString.Text = cp.ConnectionString;
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToErrorLog);
}
finally
{
;
}
}
/// <summary>
/// Converts a DatabasePlatform enum value to its equivalent QueryBuilderDatabasePlatform enum value.
/// </summary>
/// <param name="dbPlat">DatabasePlatform enum value to convert.</param>
/// <returns>QueryBuilderDatabasePlatform enum value.</returns>
public QueryBuilderDatabasePlatform ConvertDbPlatformToQueryBuilderPlatform(DatabasePlatform dbPlat)
{
QueryBuilderDatabasePlatform qbPlat = QueryBuilderDatabasePlatform.Unknown;
switch (dbPlat)
{
case PFDataAccessObjects.DatabasePlatform.MSSQLServer:
qbPlat = QueryBuilderDatabasePlatform.MSSQLServer;
break;
case PFDataAccessObjects.DatabasePlatform.MSAccess:
qbPlat = QueryBuilderDatabasePlatform.MSAccess;
break;
case PFDataAccessObjects.DatabasePlatform.SQLServerCE35:
qbPlat = QueryBuilderDatabasePlatform.SQLServerCE; //does not work
break;
case PFDataAccessObjects.DatabasePlatform.ODBC:
qbPlat = QueryBuilderDatabasePlatform.ODBC;
break;
case PFDataAccessObjects.DatabasePlatform.OLEDB:
qbPlat = QueryBuilderDatabasePlatform.OLEDB;
break;
case PFDataAccessObjects.DatabasePlatform.MSOracle:
qbPlat = QueryBuilderDatabasePlatform.Oracle;
break;
default:
qbPlat = QueryBuilderDatabasePlatform.Universal;
break;
}
return(qbPlat);
}
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);
}
private string GetConnectionString(DatabasePlatform dbPlat)
{
string connStr = string.Empty;
string configKey = "DefaultConnection_";
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
dbPlatformDesc = dbPlat.ToString();
configKey = configKey + dbPlatformDesc;
string configValue = AppConfig.GetStringValueFromConfigFile(configKey, string.Empty);
if (configValue.Trim() == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find config entry for ");
_msg.Append(configKey);
throw new System.Exception(_msg.ToString());
}
connStr = configValue;
return(connStr);
}
/// <summary>
/// Allows renaming of connection definitions from list of saved definitions.
/// </summary>
/// <param name="dbPlat">Database platform for which the connections are defined.</param>
public void RenameConnectionDefinition(DatabasePlatform dbPlat)
{
PFNameListRenamePrompt frm = null;
try
{
frm = new PFNameListRenamePrompt();
frm.Caption = "Rename Connection String Definition";
frm.SourceFolder = Path.Combine(_connectionManager.ConnectionDefintionsLocation, dbPlat.ToString());
frm.ShowDialog();
frm.Close();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("FileRename on RandomBooleansForm failed.\r\n");
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
AppMessages.DisplayErrorMessage(_msg.ToString());
}
finally
{
frm = null;
}
}
/// <summary>
/// Allows deleting of connection definitions from list of saved definitions.
/// </summary>
/// <param name="dbPlat">Database platform for which the connections are defined.</param>
public void DeleteConnectionDefinition(DatabasePlatform dbPlat)
{
PFNameListDeleteListPrompt frm = null;
try
{
frm = new PFNameListDeleteListPrompt();
frm.Caption = "Delete Connection String Definition";
frm.ListBoxLabel = "Select one or more items to delete:";
frm.SourceFolder = Path.Combine(_connectionManager.ConnectionDefintionsLocation, dbPlat.ToString());
frm.ShowDialog();
frm.Close();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
AppMessages.DisplayErrorMessage(_msg.ToString());
}
finally
{
frm = null;
}
}
private PFDatabase InitDatabaseObject(DatabasePlatform dbPlat)
{
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
PFDatabase db = null;
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlat.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(dbPlat.ToString(), dllPath, nmSpace + "." + clsName);
}
else
{
db = new PFDatabase(dbPlat);
}
return(db);
}//end method
/// <summary>
/// Retrieves connection definitions defined and saved by current user for the specified database platform.
/// </summary>
/// <param name="dbPlat">DatabasePlatform used in the connection strings to be retrieved.</param>
/// <returns>PFConnectionDefinition object.</returns>
public PFConnectionDefinition GetConnectionDefinition(DatabasePlatform dbPlat)
{
PFConnectionDefinition conndef = default(PFConnectionDefinition);
string pathToDefFiles = Path.Combine(_connectionManager.ConnectionDefintionsLocation, dbPlat.ToString());
PFNameListPrompt nlp = new PFNameListPrompt();
string[] conndefFiles = Directory.GetFiles(pathToDefFiles, "*.xml", SearchOption.AllDirectories);
if (conndefFiles.Length == 0)
{
_msg.Length = 0;
_msg.Append("Unabled to find any saved connection definitions in ");
_msg.Append(pathToDefFiles);
_msg.Append(".");
AppMessages.DisplayAlertMessage(_msg.ToString());
return(conndef);
}
nlp.Text = "Connection Definition Chooser ...";
nlp.lblSelect.Text = "Select connection definition to open from list below:";
foreach (string condefFile in conndefFiles)
{
string conndefName = Path.GetFileNameWithoutExtension(condefFile);
nlp.lstNames.Items.Add(conndefName);
}
DialogResult res = nlp.ShowDialog();
if (res == DialogResult.OK)
{
string selectedConnDefName = nlp.lstNames.SelectedItem.ToString();
conndef = PFConnectionDefinition.LoadFromXmlFile(Path.Combine(pathToDefFiles, selectedConnDefName + ".xml"));
}
return(conndef);
}
/// <summary>
/// Constructor.
/// </summary>
public PFProviderDefinition(DatabasePlatform databasePlatform)
{
_databasePlatform = databasePlatform;
_providerName = _databasePlatform.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>
/// Constructor.
/// </summary>
public CustomDataListGenerator(DatabasePlatform dbPlatform)
{
InitDbDef(dbPlatform);
}
/// <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);
}
//methods
/// <summary>
/// Routine to create an instance of the PFQueryBuilder class.
/// </summary>
/// <param name="dbPlat">Database platform to use.</param>
/// <param name="connStr">Connection string for the query.</param>
/// <returns>Object that implements ISQLBuilder interface.</returns>
/// <remarks>Default AnsiSQLLevel SQL92 will be used. </remarks>
public static ISQLBuilder CreateQueryBuilderObject(DatabasePlatform dbPlat, string connStr)
{
return(CreateQueryBuilderObject(dbPlat, connStr, AnsiSQLLevel.SQL92));
}
/// <summary>
/// Writes data contained in DataTable to table stored in TableName property.
/// </summary>
/// <param name="dtList">List of temp file names containing data tables with grid rows to be output.</param>
/// <returns>True if output operation is successful. False if write fails.</returns>
public bool WriteDataToOutput(PFList <string> dtList)
{
bool success = true;
PFDatabase db = null;
bool createTable = false;
DataTable dt = new DataTable();
string tableName = string.Empty;
bool isOracleOdbc = false;
bool isOracleOledb = false;
try
{
if (dtList.Count == 0)
{
_msg.Length = 0;
_msg.Append("WriteDataToOutput for list of temp files has failed.");
_msg.Append(Environment.NewLine);
_msg.Append("Temp file name list is empty.");
throw new System.Exception(_msg.ToString());
}
//Load first temp file in list to get data table schema
dt.Rows.Clear();
dt.ReadXml(dtList[0]);
db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
db.ConnectionString = this.ConnectionString;
tableName = this.TableName;
//workaround for oracle odbc and oledb driver problems with inserts
if (this.DbPlatform == DatabasePlatform.ODBC)
{
PFOdbc odbcDb = new PFOdbc();
odbcDb.ConnectionString = this.ConnectionString;
DatabasePlatform odbcDbPlat = odbcDb.GetDatabasePlatform();
if (odbcDbPlat == DatabasePlatform.OracleNative || odbcDbPlat == DatabasePlatform.MSOracle)
{
isOracleOdbc = true;
}
odbcDb = null;
}
if (this.DbPlatform == DatabasePlatform.OLEDB)
{
PFOleDb oledbDb = new PFOleDb();
oledbDb.ConnectionString = this.ConnectionString;
DatabasePlatform oledbDbPlat = oledbDb.GetDatabasePlatform();
if (oledbDbPlat == DatabasePlatform.OracleNative || oledbDbPlat == DatabasePlatform.MSOracle)
{
isOracleOledb = true;
}
oledbDb = null;
}
//set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
if (isOracleOdbc || isOracleOledb)
{
tableName = this.TableName.ToUpper();
for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
}
}
else
{
tableName = this.TableName;
}
//end workaround for oracle odbc and oledb
dt.TableName = tableName;
if (db.TableExists(tableName))
{
if (this.ReplaceExistingTable)
{
db.OpenConnection();
db.DropTable(tableName);
db.CloseConnection();
createTable = true;
}
else
{
createTable = false; //existing table will be imported into
}
}
else
{
createTable = true;
}
if (createTable)
{
db.OpenConnection();
string createScript = string.Empty;
string errorMessages = string.Empty;
bool tabCreated = db.CreateTable(dt, out createScript, out errorMessages);
if (tabCreated == false)
{
_msg.Length = 0;
_msg.Append("Unable to create table: ");
_msg.Append(tableName);
_msg.Append(Environment.NewLine);
_msg.Append("Error Messages: ");
_msg.Append(errorMessages);
_msg.Append(Environment.NewLine);
_msg.Append("Create Script: ");
_msg.Append(createScript);
throw new System.Exception(_msg.ToString());
}
db.CloseConnection();
}
for (int dtInx = 0; dtInx < dtList.Count; dtInx++)
{
_msg.Length = 0;
_msg.Append("List # ");
_msg.Append(dtInx.ToString());
Console.WriteLine(_msg.ToString());
dt = new DataTable();
dt.TableName = this.TableName; //original table name should be in xml file definition
dt.Rows.Clear();
dt.ReadXml(dtList[dtInx]);
db.OpenConnection();
//set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
if (isOracleOdbc || isOracleOledb)
{
dt.TableName = this.TableName.ToUpper();
for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
}
}
db.ImportDataFromDataTable(dt, this.OutputBatchSize);
db.CloseConnection();
dt = null;
}
}
catch (System.Exception ex)
{
success = false;
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
}
return(success);
}
/// <summary>
/// Constructor.
/// </summary>
public ConnectionStringPrompt(DatabasePlatform dbPlatform, PFConnectionManager connMgr)
{
_dbPlatform = dbPlatform;
_connectionManager = connMgr;
}
/// <summary>
/// Constructor.
/// </summary>
public DatabaseOutputProcessor(DatabasePlatform dbPlatform)
{
InitDbDef(dbPlatform);
}
//Application routines
private void ShowConnectionStringForm()
{
PFConnectionManager connMgr = null;
DatabasePlatform dbPlat = DatabasePlatform.Unknown;
ConnectionStringPrompt cp = null;
try
{
if (this.optSQLServer.Checked)
{
dbPlat = DatabasePlatform.MSSQLServer;
}
else if (this.optSQLServerCE35.Checked)
{
dbPlat = DatabasePlatform.SQLServerCE35;
}
else if (this.optSQLServerCE40.Checked)
{
dbPlat = DatabasePlatform.SQLServerCE40;
}
else if (this.optMicrosoftAccess.Checked)
{
dbPlat = DatabasePlatform.MSAccess;
}
else if (this.optODBC.Checked)
{
dbPlat = DatabasePlatform.ODBC;
}
else if (this.optOLEDB.Checked)
{
dbPlat = DatabasePlatform.OLEDB;
}
else if (this.optOracleNative.Checked)
{
dbPlat = DatabasePlatform.OracleNative;
}
else if (this.optMySQL.Checked)
{
dbPlat = DatabasePlatform.MySQL;
}
else if (this.optDB2.Checked)
{
dbPlat = DatabasePlatform.DB2;
}
else if (this.optInformix.Checked)
{
dbPlat = DatabasePlatform.Informix;
}
else if (this.optSybase.Checked)
{
dbPlat = DatabasePlatform.Sybase;
}
else if (this.optSQLAnywhere.Checked)
{
dbPlat = DatabasePlatform.SQLAnywhere;
}
else if (this.optSQLAnywhereUL.Checked)
{
dbPlat = DatabasePlatform.SQLAnywhereUltraLite;
}
else if (this.optMSOracle.Checked)
{
dbPlat = DatabasePlatform.MSOracle;
}
else
{
dbPlat = DatabasePlatform.SQLServerCE35;
}
connMgr = new PFConnectionManager();
cp = new ConnectionStringPrompt(dbPlat, connMgr);
cp.ConnectionString = string.Empty;
System.Windows.Forms.DialogResult res = cp.ShowConnectionPrompt();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
AppMessages.DisplayErrorMessage(_msg.ToString());
}
finally
{
;
}
}
public static void ShowConnectionPromptForm(MainForm frm)
{
PFConnectionManager connMgr = null;
DatabasePlatform dbPlat = DatabasePlatform.MSSQLServer;
bool runTests = false;
try
{
_msg.Length = 0;
_msg.Append("ShowConnectionPromptForm started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
connMgr = new PFConnectionManager();
ConnectionStringPrompt cp = new ConnectionStringPrompt(dbPlat, connMgr);
System.Windows.Forms.DialogResult res = cp.ShowConnectionPrompt();
if (res == System.Windows.Forms.DialogResult.OK)
{
runTests = true;
_msg.Length = 0;
_msg.Append("Connection name: ");
_msg.Append(cp.ConnectionName);
_msg.Append(Environment.NewLine);
_msg.Append("Connection string: ");
_msg.Append(cp.ConnectionString);
_msg.Append(Environment.NewLine);
_msg.Append("Connection Definition: ");
_msg.Append(Environment.NewLine);
if (cp.ConnectionDefinition != null)
{
_msg.Append(cp.ConnectionDefinition.ToString());
_msg.Append(Environment.NewLine);
}
Program._messageLog.WriteLine(_msg.ToString());
}
else
{
_msg.Length = 0;
_msg.Append("User cancelled connection string request: ");
_msg.Append(res.ToString());
Program._messageLog.WriteLine(_msg.ToString());
}
if (runTests)
{
//cp.ConnectionDefinition.SaveToXmlFile(@"c:\temp\TestConnectionDefinition.xml");
//PFConnectionDefinition newCP = PFConnectionDefinition.LoadFromXmlFile(@"c:\temp\TestConnectionDefinition.xml");
//cp.ConnectionDefinition.ConnectionKeyElements.SaveToXmlFile(@"c:\temp\ConnectionKeyElements.xml");
//PFList<stKeyValuePair<string, string>> elementsList = cp.ConnectionDefinition.ConnectionKeyElements.ConvertThisToPFKeyValueListEx();
//elementsList.SaveToXmlFile(@"c:\temp\ConnectionKeyElements_PFList.xml");
//Program._messageLog.WriteLine(Environment.NewLine + "newCP TO STRING:" + Environment.NewLine + newCP.ToString() + Environment.NewLine + "END newCP TO STRING" + Environment.NewLine);
//PFKeyValueList<string, string> testlist = PFKeyValueList<string, string>.LoadFromXmlString(cp.ConnectionDefinition.DbPlatformConnectionStringProperties[15].Value);
//Program._messageLog.WriteLine(testlist.ToXmlString());
//cp.ConnectionDefinition.DbPlatformConnectionStringProperties.SaveToXmlFile(@"c:\temp\DbPlatformConnectionStringProperties.xml");
//Program._messageLog.WriteLine(Environment.NewLine + "-----------");
//string dumpOutput = PFObjectDumper.Write(cp, 0);
//Program._messageLog.WriteLine(dumpOutput);
//Program._messageLog.WriteLine(Environment.NewLine + "-----------");
//cp = null;
//newCP = null;
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("\r\n... ShowConnectionPromptForm finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public void ColSpecForm()
{
DataTable dt = null;
TEST_DataTableRandomizer dtr = null;
string dbConnStr = string.Empty;
DatabasePlatform dbPlat = DatabasePlatform.Unknown;
PFDatabase db = null;
PFList <DataTableRandomizerColumnSpec> colSpecs = null;
RandomNamesAndLocationsDataRequest randomizerNameSpecs = null;
try
{
_msg.Length = 0;
_msg.Append("ColSpecForm started ...\r\n");
_messageLog.WriteLine(_msg.ToString());
dbPlat = DatabasePlatform.SQLServerCE35;
db = GetPFDatabaseObject(dbPlat);
db.ConnectionString = GetConnectionString(dbPlat);
randomizerNameSpecs = RandomNamesAndLocationsDataRequest.LoadFromXmlFile(@"C:\Testfiles\Randomizer\CountryRequestPersonsOnly.xml");
db.OpenConnection();
string sqlQuery = "select * from RandomNameData";
//listTable = db.RunQueryDataTable(sqlQuery, CommandType.Text);
dt = db.GetQueryDataSchema(sqlQuery, CommandType.Text);
dtr = new TEST_DataTableRandomizer();
colSpecs = dtr.GetInitColSpecListFromDataTable(dt);
PFRandomDataForms.DataTableRandomizerColumnSpecForm frm = new PFRandomDataForms.DataTableRandomizerColumnSpecForm(colSpecs);
DialogResult res = frm.ShowDialog();
if (res == DialogResult.OK)
{
//time to do something
_msg.Length = 0;
_msg.Append(frm.ColSpecs.ToXmlString());
_messageLog.WriteLine(_msg.ToString());
}
else
{
_msg.Length = 0;
_msg.Append("Column spec edit cancelled.");
_messageLog.WriteLine(_msg.ToString());
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
_messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("\r\n... ColSpecForm finished.");
_messageLog.WriteLine(_msg.ToString());
}
}
public static void 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());
}
}
