Ejemplo n.º 1
0
        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();
            }
        }
Ejemplo n.º 2
0
        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.");
            }
        }
Ejemplo n.º 3
0
        //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.");
            }
        }
Ejemplo n.º 4
0
        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());
            }
        }
Ejemplo n.º 5
0
        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.");
            }
        }
Ejemplo n.º 6
0
        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;
            }
        }