private void VerifyConnectionString()
{
if (this.txtConnectionString.Text.Trim() == string.Empty)
{
this.ConnectionString = BuildConnectionString();
if (this.txtConnectionString.Text.Trim() == string.Empty)
{
AppMessages.DisplayWarningMessage("You must specify a connection string. Fill in input fields and then use Build button to transform input fields into a connection string.");
return;
}
}
try
{
DisableFormControls();
this.Cursor = Cursors.WaitCursor;
_db.ConnectionString = this.ConnectionString;
_db.OpenConnection();
_msg.Length = 0;
if (_db.IsConnected)
{
this._connectionAccessStatus = enConnectionAccessStatus.IsAccessible;
_msg.Append("Connection successful!");
AppMessages.DisplayInfoMessage(_msg.ToString());
}
else
{
this._connectionAccessStatus = enConnectionAccessStatus.NotAccessible;
_msg.Append("Connection failed.");
AppMessages.DisplayErrorMessage(_msg.ToString());
}
}
catch (System.Exception ex)
{
this._connectionAccessStatus = enConnectionAccessStatus.Unknown;
_msg.Length = 0;
_msg.Append("Attempt to connect to database failed.");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
AppMessages.DisplayErrorMessage(_msg.ToString());
}
finally
{
if (_db.IsConnected)
{
_db.CloseConnection();
}
EnableFormControls();
this.Cursor = Cursors.Default;
this.Focus();
}
}
public static void GetQueryDataSchema(MainForm frm)
{
PFOdbc db = new PFOdbc();
Program._messageLog.WriteLine("GetQueryDataSchema started ...");
try
{
db.ConnectionString = frm.cboConnectionString.Text;
db.OpenConnection();
_msg.Length = 0;
_msg.Append("Connection string is ");
_msg.Append(db.ConnectionString);
_msg.Append("\r\n");
_msg.Append("Connection state is ");
_msg.Append(db.Connection.State.ToString());
_msg.Append("\r\n");
_msg.Append("Query text is:\r\n");
_msg.Append(frm.txtSqlQuery.Text.ToString());
Program._messageLog.WriteLine(_msg.ToString());
db.SQLQuery = frm.txtSqlQuery.Text;
db.CommandType = CommandType.Text;
DataTable tab = db.GetQueryDataSchema();
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());
}
}
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.Connection.State == ConnectionState.Open)
{
db.CloseConnection();
}
Program._messageLog.WriteLine("... GetQueryDataSchema finished.");
}
}
//tests
public static void RunConnectionTest(MainForm frm)
{
PFOdbc db = new PFOdbc();
Program._messageLog.WriteLine("RunConnectionTest started ...");
try
{
db.ConnectionString = frm.cboConnectionString.Text;
db.OpenConnection();
_msg.Length = 0;
_msg.Append("Connection string is ");
_msg.Append(db.ConnectionString);
_msg.Append("\r\n");
_msg.Append("Connection state is ");
_msg.Append(db.Connection.State.ToString());
_msg.Append("\r\n");
_msg.Append("Driver is ");
_msg.Append(db.Driver);
_msg.Append("\r\n");
_msg.Append("Dsn is ");
_msg.Append(db.Dsn);
_msg.Append("\r\n");
Program._messageLog.WriteLine(_msg.ToString());
foreach (stKeyValuePair <string, string> kv in db.ConnectionStringKeyVals)
{
_msg.Length = 0;
_msg.Append(kv.Key + "=" + kv.Value);
Program._messageLog.WriteLine(_msg.ToString());
}
Program._messageLog.WriteLine(Environment.NewLine);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db.Connection.State == ConnectionState.Open)
{
db.CloseConnection();
}
Program._messageLog.WriteLine("... RunConnectionTest finished.");
}
}
public static void ImportDataTableTest(MainForm frm)
{
PFOdbc db = new PFOdbc();
string connectionString = string.Empty;
string originalTableName = string.Empty;
DataTable dt = null;
try
{
_msg.Length = 0;
_msg.Append("ImportDataTableTest started ...");
Program._messageLog.WriteLine(_msg.ToString());
connectionString = frm.cboConnectionString.Text;
db.ConnectionString = connectionString;
db.OpenConnection();
StringBuilder sql = new StringBuilder();
dt = frm.keyValsDataSet.Tables["KeyValTable"];
originalTableName = dt.TableName;
string tableName = frm.txtTableName.Text;
//first delete table if it already exists
Program._messageLog.WriteLine("\r\nDropping old table if it exists ...");
string catalogName = string.Empty;
string schemaName = string.Empty;
string tabName = string.Empty;
catalogName = frm.txtCatalogName.Text;
schemaName = frm.txtSchemaName.Text;
tabName = "KeyValTable";
if (schemaName.Trim().Length > 0)
{
dt.TableName = schemaName + "." + tabName;
}
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.Trim().Length > 0)
{
_msg.Append(catalogName);
_msg.Append(".");
}
if (schemaName.Trim().Length > 0)
{
_msg.Append(schemaName);
_msg.Append(".");
}
_msg.Append(tabName);
throw new DataException(_msg.ToString());
}
else
{
_msg.Length = 0;
_msg.Append("Old table dropped: ");
if (catalogName.Trim().Length > 0)
{
_msg.Append(catalogName);
_msg.Append(".");
}
if (schemaName.Trim().Length > 0)
{
_msg.Append(schemaName);
_msg.Append(".");
}
_msg.Append(tabName);
Program._messageLog.WriteLine(_msg.ToString());
}
}
Program._messageLog.WriteLine("\r\nCreating a table in the database ...");
//create the table
bool tableCreated = db.CreateTable(dt);
if (tableCreated)
{
db.ImportDataFromDataTable(dt);
}
else
{
_msg.Length = 0;
_msg.Append("CreateTable for ");
_msg.Append(dt.TableName);
_msg.Append(" failed.");
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
{
if (dt != null)
{
if (originalTableName.Length > 0)
{
dt.TableName = originalTableName;
}
}
_msg.Length = 0;
_msg.Append("... ImportDataTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void RunDataReaderTest(MainForm frm)
{
PFOdbc db = new PFOdbc();
Program._messageLog.WriteLine("RunDataReaderTest started ...");
try
{
db.ConnectionString = frm.cboConnectionString.Text;
db.OpenConnection();
_msg.Length = 0;
_msg.Append("Connection string is ");
_msg.Append(db.ConnectionString);
_msg.Append("\r\n");
_msg.Append("Connection state is ");
_msg.Append(db.Connection.State.ToString());
_msg.Append("\r\n");
_msg.Append("Query text is:\r\n");
_msg.Append(frm.txtSqlQuery.Text.ToString());
Program._messageLog.WriteLine(_msg.ToString());
db.returnResult += new PFOdbc.ResultDelegate(OutputResults);
db.SQLQuery = frm.txtSqlQuery.Text;
db.CommandType = CommandType.Text;
OdbcDataReader rdr = (OdbcDataReader)db.RunQueryDataReader();
db.ProcessDataReader(rdr);
rdr.Close();
db.returnResult -= OutputResults;
db.returnResultAsString += new PFOdbc.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\OdbcRdrDelimited.txt", PFFileOpenOperation.OpenFileForWrite);
rdr = (OdbcDataReader)db.RunQueryDataReader();
db.ExtractDelimitedDataFromDataReader(rdr, ",", "\r\n", true);
rdr.Close();
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\OdbcRdrFixedLength.txt", PFFileOpenOperation.OpenFileForWrite);
rdr = (OdbcDataReader)db.RunQueryDataReader();
db.ExtractFixedLengthDataFromDataReader(rdr, true, true, false);
rdr.Close();
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
rdr = (OdbcDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlFile(rdr, @"c:\temp\OdbcTestrdr.xml");
rdr.Close();
rdr = (OdbcDataReader)db.RunQueryDataReader();
db.SaveDataReaderWithSchemaToXmlFile(rdr, @"c:\temp\OdbcTestrdrplus.xml");
rdr.Close();
rdr = (OdbcDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlSchemaFile(rdr, @"c:\temp\OdbcTestrdr.xsd");
rdr.Close();
rdr = (OdbcDataReader)db.RunQueryDataReader();
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(db.ConvertDataReaderToDataTable(rdr));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
rdr.Close();
}
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 (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
if (db.Connection.State == ConnectionState.Open)
{
db.CloseConnection();
}
Program._messageLog.WriteLine("... RunDataReaderTest finished.");
}
}
public static void DataReaderToDataTableTest(MainForm frm)
{
PFOdbc db = new PFOdbc();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
connectionString = frm.cboConnectionString.Text;
db.ConnectionString = connectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (frm.txtSqlQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
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();
OdbcDataReader rdr = (OdbcDataReader)db.RunQueryDataReader();
DataTable tab = db.ConvertDataReaderToDataTable(rdr);
Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString());
rdr.Close();
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow r = tab.Rows[i];
_msg.Length = 0;
int maxColInx = tab.Columns.Count - 1;
for (int ci = 0; ci <= maxColInx; ci++)
{
_msg.Append(tab.Columns[ci].ColumnName);
_msg.Append(": ");
_msg.Append(r[ci].ToString());
if (ci < maxColInx)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Table read time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
db.CloseConnection();
db = null;
}
}