Example #1
0
        public static void CreateTableTest(MainForm frm)
        {
            PFOdbc              odbc             = new PFOdbc();
            IDatabaseProvider   db               = odbc;
            string              connectionString = string.Empty;
            string              createScript     = string.Empty;
            string              tableName        = "TestTable01";
            StringBuilder       sql              = new StringBuilder();
            PFUnitTestDataTable unitTestDt01     = null;
            PFUnitTestDataTable unitTestDt02     = null;
            DataTable           dt               = null;
            DataTable           dt2              = null;

            try
            {
                db.ConnectionString = frm.cboConnectionString.Text;
                if (frm.txtTableName.Text.Length > 0)
                {
                    tableName = frm.txtTableName.Text;
                }
                else
                {
                    _msg.Length = 0;
                    _msg.Append("You must specify a table name for the CreateTableTest.");
                    throw new System.Exception(_msg.ToString());
                }

                connectionString = db.ConnectionString;

                _msg.Length = 0;
                _msg.Append("Connection string is: \r\n");
                _msg.Append(connectionString);
                Program._messageLog.WriteLine(_msg.ToString());

                db.OpenConnection();


                //Get a list of the data mappings supported by the selected driver
                PFTableBuilder         tabBuilder       = new PFTableBuilder(DatabasePlatform.ODBC);
                List <DataTypeMapping> dataTypeMappings = tabBuilder.GetGenericDataTypeMappingsEx(db.ConnectionString);

                Program._messageLog.WriteLine("\r\nData Type Mappings:\r\n");
                if (dataTypeMappings != null)
                {
                    for (int i = 0; i < dataTypeMappings.Count; i++)
                    {
                        _msg.Length = 0;
                        _msg.Append(dataTypeMappings[i].DatabaseDataType);
                        _msg.Append(" is mapped to ");
                        _msg.Append(dataTypeMappings[i].DotNetDataType);
                        _msg.Append(" (");
                        _msg.Append(dataTypeMappings[i].dataTypeCategory);
                        _msg.Append(") MaxLen = ");
                        _msg.Append(dataTypeMappings[i].MaxLength.ToString());
                        Program._messageLog.WriteLine(_msg.ToString());
                    }
                }

                Program._messageLog.WriteLine(string.Empty);


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

                bool result = false;
                if (frm.chkUseOdbcBuilder.Checked)
                {
                    dt     = unitTestDt01.GetDataTableFromTableColumns(true);
                    result = odbc.CreateTableUsingOdbcSyntax(dt, out createScript);
                    dt2    = unitTestDt02.GetDataTableFromTableColumns(true);
                    result = odbc.CreateTableUsingOdbcSyntax(dt2, out createScript);
                }
                else if (frm.chkUseCustomSQL.Checked)
                {
                    result       = odbc.CreateTableUsingCustomScript(frm.txtSqlQuery.Text);
                    createScript = frm.txtSqlQuery.Text;
                }
                else
                {
                    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());

                if (frm.chkUseOdbcBuilder.Checked)
                {
                    odbc.ImportDataFromDataTable(dt);
                }
                else if (frm.chkUseCustomSQL.Checked)
                {
                    _msg.Length = 0;
                    _msg.Append("No import done for test table 01 created by custom script");
                    Program._messageLog.WriteLine(_msg.ToString());
                }
                else
                {
                    unitTestDt01.ImportTableToDatabase();
                }

                _msg.Length = 0;
                _msg.Append("Importing data to TestTable02");
                Program._messageLog.WriteLine(_msg.ToString());

                if (frm.chkUseOdbcBuilder.Checked)
                {
                    odbc.ImportDataFromDataTable(dt2);
                }
                else if (frm.chkUseCustomSQL.Checked)
                {
                    _msg.Length = 0;
                    _msg.Append("No import done for test table 02 created by custom script");
                    Program._messageLog.WriteLine(_msg.ToString());
                }
                else
                {
                    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());
                }



                /*
                 * if (schemaRoot.TableExists(catalogName, schemaName, tabName))
                 * {
                 *  bool dropped = schemaRoot.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());
                 *  }
                 * }
                 *
                 *
                 *
                 * Program._messageLog.WriteLine("\r\nCreating a table in the database ...");
                 * DataTable dt = new DataTable(tableName);
                 * DataColumn k1 = new DataColumn("K1", Type.GetType("System.Int32"));
                 * k1.AllowDBNull = false;
                 * dt.Columns.Add(k1);
                 * DataColumn f1 = new DataColumn("F1", Type.GetType("System.String"));
                 * f1.MaxLength = 50;
                 * dt.Columns.Add(f1);
                 * DataColumn f1x = new DataColumn("F1X", Type.GetType("System.String"));
                 * f1x.MaxLength = 500;
                 * dt.Columns.Add(f1x);
                 * DataColumn f1a = new DataColumn("F1A", Type.GetType("System.String"));
                 * f1a.MaxLength = 50000;
                 * dt.Columns.Add(f1a);
                 * DataColumn f2 = new DataColumn("F2", Type.GetType("System.Int32"));
                 * dt.Columns.Add(f2);
                 * DataColumn f2a = new DataColumn("F2A", Type.GetType("System.UInt32"));
                 * dt.Columns.Add(f2a);
                 * DataColumn f3 = new DataColumn("F3", Type.GetType("System.Int64"));
                 * dt.Columns.Add(f3);
                 * DataColumn f3a = new DataColumn("F3A", Type.GetType("System.UInt64"));
                 * dt.Columns.Add(f3a);
                 * DataColumn f4 = new DataColumn("F4", Type.GetType("System.Int16"));
                 * dt.Columns.Add(f4);
                 * DataColumn f4a = new DataColumn("F4A", Type.GetType("System.UInt16"));
                 * dt.Columns.Add(f4a);
                 * DataColumn f5 = new DataColumn("F5", Type.GetType("System.Double"));
                 * dt.Columns.Add(f5);
                 * DataColumn f6 = new DataColumn("F6", Type.GetType("System.Single"));
                 * dt.Columns.Add(f6);
                 * DataColumn f7 = new DataColumn("F7", Type.GetType("System.Decimal"));
                 * dt.Columns.Add(f7);
                 * DataColumn f8 = new DataColumn("F8", Type.GetType("System.Char"));
                 * dt.Columns.Add(f8);
                 * DataColumn f9 = new DataColumn("F9", Type.GetType("System.Byte"));
                 * dt.Columns.Add(f9);
                 * DataColumn f9a = new DataColumn("F9A", Type.GetType("System.SByte"));
                 * dt.Columns.Add(f9a);
                 * DataColumn f10 = new DataColumn("F10", Type.GetType("System.Boolean"));
                 * dt.Columns.Add(f10);
                 * DataColumn f11 = new DataColumn("F11", Type.GetType("System.Object"));
                 * dt.Columns.Add(f11);
                 * DataColumn f12 = new DataColumn("F12", Type.GetType("System.DateTime"));
                 * dt.Columns.Add(f12);
                 * DataColumn f13 = new DataColumn("F13", Type.GetType("System.Guid"));
                 * dt.Columns.Add(f13);
                 * DataColumn f14 = new DataColumn("F14", Type.GetType("System.Char[]"));
                 * dt.Columns.Add(f14);
                 * DataColumn f15 = new DataColumn("F15", Type.GetType("System.Byte[]"));
                 * dt.Columns.Add(f15);
                 * DataColumn f16 = new DataColumn("F16", Type.GetType("System.Char[]"));
                 * dt.Columns.Add(f16);
                 * DataColumn f17 = new DataColumn("F17", Type.GetType("System.Byte[]"));
                 * dt.Columns.Add(f17);
                 *
                 *
                 * //DatabasePlatform dbplat = DatabasePlatform.Unknown;
                 *
                 * //if (connectionString.Contains("Driver={Microsoft Access Driver"))
                 * //{
                 * //    dbplat = DatabasePlatform.MSAccess;
                 * //}
                 * //else if (connectionString.Contains("Driver={Oracle"))
                 * //{
                 * //    dbplat = DatabasePlatform.OracleNative;
                 * //}
                 * //else if (connectionString.Contains("Driver={Microsoft ODBC for Oracle"))
                 * //{
                 * //    dbplat = DatabasePlatform.MSOracle;
                 * //}
                 * //else if (connectionString.Contains("Driver={SQL Server")
                 * //         || connectionString.Contains("SQL Server"))
                 * //{
                 * //    dbplat = DatabasePlatform.MSSQLServer;
                 * //}
                 * //else if (connectionString.Contains("Driver={IBM DB2")
                 * //        || connectionString.Contains("IBM DB2"))
                 * //{
                 * //    dbplat = DatabasePlatform.DB2;
                 * //}
                 * //else if (connectionString.Contains("Driver={MySQL"))
                 * //{
                 * //    dbplat = DatabasePlatform.MySQL;
                 * //}
                 * //else
                 * //{
                 * //    dbplat = DatabasePlatform.ODBC;
                 * //}
                 *
                 *
                 *
                 * //PFTableBuilder tb = null;
                 * //if (frm.chkUseOdbcBuilder.Checked)
                 * //{
                 * //    tb = new PFTableBuilder(DatabasePlatform.ODBC);
                 * //    createScript = tb.GenericTableCreateStatement(dt, connectionString);
                 * //}
                 * //else if (dbplat != DatabasePlatform.MSAccess)
                 * //{
                 * //    tb = new PFTableBuilder(dbplat);
                 * //    createScript = tb.BuildTableCreateStatement(dt, connectionString);
                 * //}
                 * //else
                 * //{
                 * //    createScript = string.Empty;
                 * //}
                 *
                 *
                 * bool result = false;
                 * if (frm.chkUseOdbcBuilder.Checked)
                 * {
                 *  result = odbc.CreateTableUsingOdbcSyntax(dt, out createScript);
                 * }
                 * else if (frm.chkUseCustomSQL.Checked)
                 * {
                 *  result = odbc.CreateTableUsingCustomScript(frm.txtSqlQuery.Text);
                 * }
                 * else
                 * {
                 *  result = odbc.CreateTable(dt, out createScript);
                 * }
                 *
                 *
                 *
                 */
            }
            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("SQL Script: \r\n");
                _msg.Append(createScript);
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }