public static void RunCreateDatabaseTableTest(MainForm frm) { PFMsAccess access = null; string dbPath = frm.txtDatabasePath.Text; string createScript = string.Empty; string tableName = "TestTabX1"; string tableName2 = "TestTabX2"; AccessVersion dbVersion = AccessVersion.Access2003; PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; try { _msg.Length = 0; _msg.Append("RunAdoxAdorTest started ..."); Program._messageLog.WriteLine(_msg.ToString()); if (Path.GetExtension(dbPath) == ".accdb") { dbVersion = AccessVersion.Access2007; } else { dbVersion = AccessVersion.Access2003; } access = PFMsAccess.CreateDatabase(dbPath, dbVersion, frm.chkOverwriteExistingDb.Checked, "admin", string.Empty); /* * DataTable dt = new DataTable(tableName); * DataColumn k1 = new DataColumn("K1",Type.GetType("System.Int32")); * dt.Columns.Add(k1); * DataColumn f1 = new DataColumn("F1", Type.GetType("System.String")); * f1.MaxLength = 50; * dt.Columns.Add(f1); * 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); * * access.CreateTable(dt); * */ if (access.IsConnected == false) { access.OpenConnection(); } IDatabaseProvider db = (IDatabaseProvider)access; unitTestDt01 = new PFUnitTestDataTable(db, "", tableName, true); unitTestDt02 = new PFUnitTestDataTable(db, "", tableName2, true); access.ReopenConnection(); _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; _msg.Length = 0; _msg.Append("CreateScript:\r\n"); _msg.Append(createScript); Program._messageLog.WriteLine(_msg.ToString()); //import data to database access.ReopenConnection(); _msg.Length = 0; _msg.Append("Importing data to TestTable01"); Program._messageLog.WriteLine(_msg.ToString()); unitTestDt01.ImportTableToDatabase(); access.ReopenConnection(); _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 StringBuilder sql = new StringBuilder(); 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.FormatErrorMessageWithStackTrace(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { if (access != null) { if (access.IsConnected) { access.CloseConnection(); } } access = null; System.GC.Collect(); _msg.Length = 0; _msg.Append("... RunCreateDatabaseTableTest finished."); Program._messageLog.WriteLine(_msg.ToString()); } }
public static void CreateTableTest(MainForm frm) { PFSQLServerCE35 sqlce = new PFSQLServerCE35(); string dataSource = frm.txtDataSource.Text; string connectionString = string.Empty; string tableName = "TestTabX1"; string tableName2 = "TestTabX2"; string createScript = string.Empty; PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; try { _msg.Length = 0; _msg.Append("CreateTableTest started ...\r\n"); Program._messageLog.WriteLine(_msg.ToString()); sqlce.DatabasePath = dataSource; sqlce.DatabasePassword = frm.txtPassword.Text; connectionString = sqlce.ConnectionString; if (connectionString.Length == 0) { throw new System.Exception("sqlce.ConnectionString is empty."); } if (File.Exists(dataSource) == false) { _msg.Length = 0; _msg.Append(dataSource); _msg.Append(" does not exist.\r\n"); throw new System.Exception(_msg.ToString()); } sqlce.OpenConnection(); _msg.Length = 0; _msg.Append("Table "); _msg.Append(tableName); if (sqlce.TableExists(tableName)) { if (sqlce.DropTable(tableName)) { _msg.Append(" dropped."); } else { _msg.Append(" drop failed."); } } else { _msg.Append(" does not exist."); } Program._messageLog.WriteLine(_msg.ToString()); _msg.Length = 0; _msg.Append("Table2 "); _msg.Append(tableName2); if (sqlce.TableExists(tableName2)) { if (sqlce.DropTable(tableName2)) { _msg.Append(" dropped."); } else { _msg.Append(" drop failed."); } } else { _msg.Append(" does not exist."); } Program._messageLog.WriteLine(_msg.ToString()); IDatabaseProvider db = (IDatabaseProvider)sqlce; unitTestDt01 = new PFUnitTestDataTable(db, string.Empty, tableName, true); unitTestDt02 = new PFUnitTestDataTable(db, string.Empty, tableName2, 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[]", "ABCDEFGHIJKLMNOPQRSTUVWXYZ")); 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 StringBuilder sql = new StringBuilder(); 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()); } /* * Program._messageLog.WriteLine("\r\nCreating 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); * * createScript = string.Empty; * bool result = sqlce.CreateTable(dt, out createScript); * * _msg.Length = 0; * _msg.Append("Table "); * _msg.Append(tableName); * if (sqlce.TableExists(tableName)) * { * _msg.Append(" created."); * } * else * { * _msg.Append(" create failed."); * } * 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 (sqlce != null) { if (sqlce.IsConnected) { sqlce.CloseConnection(); } } sqlce = null; _msg.Length = 0; _msg.Append("Create Table Script: \r\n"); _msg.Append(createScript); Program._messageLog.WriteLine(_msg.ToString()); _msg.Length = 0; _msg.Append("\r\n... CreateTableTest finished."); Program._messageLog.WriteLine(_msg.ToString()); } }
public static void CreateTableTest() { PFSQLAnywhereUL sqlu = new PFSQLAnywhereUL(); string connectionString = string.Empty; string createScript = string.Empty; string tableName = "TestTable01"; string tableName2 = "TestTable02"; StringBuilder sql = new StringBuilder(); PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; try { sqlu.DatabasePath = _frm.txtDatabaseFile.Text; sqlu.DatabaseName = _frm.txtDatabaseName.Text; sqlu.Username = _frm.txtUsername.Text; sqlu.Password = _frm.txtPassword.Text; sqlu.DatabaseKey = _frm.txtDatabaseKey.Text; if (_frm.txtTableName.Text.Length > 0) { tableName = _frm.txtTableName.Text; tableName2 = _frm.txtTableName.Text + "_02"; } connectionString = sqlu.ConnectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); sqlu.OpenConnection(); //first delete table if it already exists Program._messageLog.WriteLine("\r\nDropping old table if it exists ..."); _msg.Length = 0; _msg.Append("Table "); _msg.Append(tableName); if (sqlu.TableExists(tableName)) { sqlu.DropTable(tableName); if (sqlu.TableExists(tableName) == false) { _msg.Append(" dropped."); } else { _msg.Append(" drop failed."); } } else { _msg.Append(" does not exist."); } Program._messageLog.WriteLine(_msg.ToString()); _msg.Length = 0; _msg.Append("Table "); _msg.Append(tableName2); if (sqlu.TableExists(tableName2)) { sqlu.DropTable(tableName2); if (sqlu.TableExists(tableName2) == false) { _msg.Append(" dropped."); } else { _msg.Append(" drop failed."); } } else { _msg.Append(" does not exist."); } Program._messageLog.WriteLine(_msg.ToString()); IDatabaseProvider db = (IDatabaseProvider)sqlu; unitTestDt01 = new PFUnitTestDataTable(db, string.Empty, tableName, true); unitTestDt02 = new PFUnitTestDataTable(db, string.Empty, tableName2, 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", "X")); 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[]", "LMNOPQRSTUVWZYZ")); 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()); } /* * 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 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); * * dt.PrimaryKey = new DataColumn[] { k1 }; * * createScript = string.Empty; * bool result = schemaRoot.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 { _msg.Length = 0; _msg.Append("SQL Script: \r\n"); _msg.Append(createScript); Program._messageLog.WriteLine(_msg.ToString()); sqlu.CloseConnection(); sqlu = null; } }
public static void CreateTableTest() { PFSQLServer sqlserv = new PFSQLServer(); string connectionString = string.Empty; string createScript = string.Empty; string tableName = "TestTable01"; string schemaName = "dbo"; StringBuilder sql = new StringBuilder(); PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; try { sqlserv.ServerName = _frm.txtServerName.Text; sqlserv.DatabaseName = _frm.txtDatabaseName.Text; sqlserv.UseIntegratedSecurity = _frm.chkUseIntegratedSecurity.Checked; sqlserv.AsynchronousProcessing = _frm.chkUseAsyncProcessing.Checked; sqlserv.ApplicationName = _frm.txtApplicationName.Text; sqlserv.WorkstationId = _frm.txtWorkstationId.Text; sqlserv.Username = _frm.txtUsername.Text; sqlserv.Password = _frm.txtPassword.Text; if (_frm.txtSchemaName.Text.Length > 0) { schemaName = _frm.txtSchemaName.Text; } if (_frm.txtTableName.Text.Length > 0) { tableName = _frm.txtTableName.Text; } connectionString = sqlserv.ConnectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); sqlserv.OpenConnection(); IDatabaseProvider db = (IDatabaseProvider)sqlserv; unitTestDt01 = new PFUnitTestDataTable(db, "dbo", "TestTable01", true); unitTestDt02 = new PFUnitTestDataTable(db, "dbo", "TestTable02", 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; //DataTable testTab = sqlserv.RunQueryDataTable("select * from TestTable01", CommandType.Text); //_msg.Length = 0; //_msg.Append("testTab row count: "); //_msg.Append(testTab.Rows.Count.ToString()); //Program._messageLog.WriteLine(_msg.ToString()); //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 TestTable01\r\n"); sql.Length = 0; sql.Append("select * from "); sql.Append("dbo.TestTable01"); 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()); } /* * //first delete table if it already exists * Program._messageLog.WriteLine("\r\nDropping old table if it exists ..."); * * _msg.Length = 0; * _msg.Append("Table "); * _msg.Append(schemaName); * _msg.Append("."); * _msg.Append(tableName); * if (sqlserv.TableExists(schemaName, tableName)) * { * sqlserv.DropTable(schemaName, tableName); * if (sqlserv.TableExists(schemaName, tableName) == false) * _msg.Append(" dropped."); * else * _msg.Append(" drop failed."); * } * else * { * _msg.Append(" does not exist."); * } * * Program._messageLog.WriteLine(_msg.ToString()); * * //sql.Length = 0; * //sql.Append("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'"); * //sql.Append(tableName); * //sql.Append("') AND type in (N'U'))"); * //sql.Append("\r\n"); * //sql.Append("DROP TABLE "); * //sql.Append(tableName); * //sql.Append(";"); * * //Program._messageLog.WriteLine(sql.ToString()); * * //int numRows = sqlserv.RunNonQuery(sql.ToString(),CommandType.Text); * * //_msg.Length = 0; * //_msg.Append("Num rows affected on drop table: "); * //_msg.Append(numRows.ToString()); * //Program._messageLog.WriteLine(_msg.ToString()); * * Program._messageLog.WriteLine("\r\nCreating a table in the database ..."); * DataTable dt = new DataTable(schemaName + "." + 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 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 f8a = new DataColumn("F8A", Type.GetType("System.Char[]")); * dt.Columns.Add(f8a); * 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 f9b = new DataColumn("F9B", Type.GetType("System.Byte[]")); * dt.Columns.Add(f9b); * 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); * * createScript = string.Empty; * bool result = sqlserv.CreateTable(dt, out createScript); * * int maxColInx = dt.Columns.Count - 1; * sql.Length = 0; * sql.Append("insert into "); * sql.Append(schemaName + "." + tableName); * sql.Append(" ("); * for (int colInx = 0; colInx <= maxColInx; colInx++) * { * sql.Append(dt.Columns[colInx].ColumnName); * if (colInx < maxColInx) * sql.Append(", "); * } * sql.Append(")"); * sql.Append(" VALUES ("); * sql.Append("1, 'short string value', 'longer string value', 1123456789, 3123456789, "); * sql.Append("23123456789, 8881234567889, 11123, 52432, 123456.7654, 321.234, 2123456789.22, "); * sql.Append("'A', 'ABCDEFGH', 254, 125, CONVERT(varbinary(5),'UVWZYZ'), 1, 'This is an object: be careful!', '5/31/2013 13:54:25', '58a4a08d-6101-4393-86dc-b2a8db46ec0f'"); * sql.Append(");"); * * _msg.Length = 0; * _msg.Append("Insert statement: "); * _msg.Append(sql.ToString()); * Program._messageLog.WriteLine(_msg.ToString()); * * * int numRows = sqlserv.RunNonQuery(sql.ToString(), CommandType.Text); * * _msg.Length = 0; * _msg.Append("Num rows inserted: "); * _msg.Append(numRows.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 { _msg.Length = 0; _msg.Append("SQL Script: \r\n"); _msg.Append(createScript); Program._messageLog.WriteLine(_msg.ToString()); sqlserv.CloseConnection(); sqlserv = null; } }
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()); } }
public static void CreateTableTest(MainForm frm) { PFMsOracle oracle = new PFMsOracle(); string connectionString = string.Empty; string createScript = string.Empty; string tableName = "HR.TestTable01"; StringBuilder sql = new StringBuilder(); PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; try { oracle.DataSource = frm.txtDataSource.Text; oracle.UseIntegratedSecurity = frm.chkUseIntegratedSecurity.Checked; oracle.Username = frm.txtUserId.Text; oracle.Password = frm.txtPassword.Text; if (frm.txtTableName.Text.Length > 0) { tableName = frm.txtTableName.Text; } connectionString = oracle.ConnectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); oracle.OpenConnection(); IDatabaseProvider db = (IDatabaseProvider)oracle; string schemaName = "SYS"; string tabName = "InvalidTableName"; string[] parsedTableName = tableName.Split('.'); if (parsedTableName.Length == 2) { schemaName = parsedTableName[0]; tabName = parsedTableName[1]; } else if (parsedTableName.Length == 1) { tabName = parsedTableName[0]; } else { tabName = "InvalidTableName"; } if (oracle.TableExists(schemaName, tabName)) { bool dropped = oracle.DropTable(schemaName, tabName); if (dropped == false) { _msg.Length = 0; _msg.Append("Unable to drop table "); _msg.Append(schemaName); _msg.Append("."); _msg.Append(tabName); throw new DataException(_msg.ToString()); } } if (oracle.TableExists(schemaName, tabName + "_02")) { bool dropped = oracle.DropTable(schemaName, tabName + "_02"); if (dropped == false) { _msg.Length = 0; _msg.Append("Unable to drop table "); _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")); //MsOracle provider does not support writing guids to Oracle database4; first convert to string //PFMsOracle will convert the data type to a string when it creates the table unitTestDt01.SetDataTypesToInclude(dataTypesToInclude); unitTestDt01.SetDataTypeOptions("System.String", false, true, 75); unitTestDt01.SetDataTypeOptions("F13", "System.String", false, true, 36); unitTestDt02.SetDataTypesToInclude(dataTypesToInclude); unitTestDt02.SetDataTypeOptions("System.String", false, true, 75000); unitTestDt02.SetDataTypeOptions("F13", "System.String", false, true, 36); //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 Test Table 01"); Program._messageLog.WriteLine(_msg.ToString()); unitTestDt01.ImportTableToDatabase(); _msg.Length = 0; _msg.Append("Importing data to Test Table 02"); 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()); } /* * //first delete table if it already exists * Program._messageLog.WriteLine("\r\nDropping old table if it exists ..."); * * string schemaName = "SYS"; * string tabName = "InvalidTableName"; * string[] parsedTableName = tableName.Split('.'); * if (parsedTableName.Length == 2) * { * schemaName = parsedTableName[0]; * tabName = parsedTableName[1]; * } * else if (parsedTableName.Length == 1) * tabName = parsedTableName[0]; * else * ; * * if (oracle.TableExists(schemaName, tabName)) * { * bool dropped = oracle.DropTable(schemaName, tabName); * if (dropped == false) * { * _msg.Length = 0; * _msg.Append("Unable to drop table "); * _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 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); * * createScript = string.Empty; * bool result = oracle.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 { _msg.Length = 0; _msg.Append("SQL Script: \r\n"); _msg.Append(createScript); Program._messageLog.WriteLine(_msg.ToString()); oracle.CloseConnection(); oracle = null; } }
public static void CreateTableTest(MainForm frm) { PFMySQL mysql = new PFMySQL(); string connectionString = string.Empty; string createScript = string.Empty; string schemaName = "SAKILA"; string tableName = "TestTable01"; string tableName2 = "TestTable02"; StringBuilder sql = new StringBuilder(); PFUnitTestDataTable unitTestDt01 = null; PFUnitTestDataTable unitTestDt02 = null; try { mysql.ServerName = frm.txtServerName.Text; mysql.DatabaseName = frm.txtDatabaseName.Text; mysql.Username = frm.txtUsername.Text; mysql.Password = frm.txtPassword.Text; mysql.PortNumber = frm.txtPortNumber.Text; if (frm.txtTableName.Text.Length > 0) { tableName = frm.txtTableName.Text; } connectionString = mysql.ConnectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); mysql.OpenConnection(); //first delete table if it already exists Program._messageLog.WriteLine("\r\nDropping old table if it exists ..."); //string schemaName = "information_schema"; //string tabName = "InvalidTableName"; //string[] parsedTableName = tableName.Split('.'); //if (parsedTableName.Length == 2) //{ // schemaName = parsedTableName[0]; // tabName = parsedTableName[1]; //} //else if (parsedTableName.Length == 1) //{ // schemaName = mysql.DatabaseName; // tabName = parsedTableName[0]; //} //else // ; if (mysql.TableExists(schemaName, tableName)) { bool dropped = mysql.DropTable(schemaName, tableName); if (dropped == false) { _msg.Length = 0; _msg.Append("Unable to drop table "); _msg.Append(schemaName); _msg.Append("."); _msg.Append(tableName); throw new DataException(_msg.ToString()); } else { _msg.Length = 0; _msg.Append("Old table dropped: "); _msg.Append(schemaName); _msg.Append("."); _msg.Append(tableName); Program._messageLog.WriteLine(_msg.ToString()); } } if (mysql.TableExists(schemaName, tableName2)) { bool dropped = mysql.DropTable(schemaName, tableName2); if (dropped == false) { _msg.Length = 0; _msg.Append("Unable to drop table "); _msg.Append(schemaName); _msg.Append("."); _msg.Append(tableName2); throw new DataException(_msg.ToString()); } else { _msg.Length = 0; _msg.Append("Old table dropped: "); _msg.Append(schemaName); _msg.Append("."); _msg.Append(tableName2); Program._messageLog.WriteLine(_msg.ToString()); } } IDatabaseProvider db = (IDatabaseProvider)mysql; unitTestDt01 = new PFUnitTestDataTable(db, schemaName, tableName, true); unitTestDt02 = new PFUnitTestDataTable(db, schemaName, tableName2, 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")); //MySQL mishandles char[]: will be reset to a byte[] by PFMYSQL routine that imports data. 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[]", "LMNOPQRSTUVWZYZ")); 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")); //MySql cannot handle guid datatype; type will be changed to string for output purposes. 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()); } /* * 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 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 BEST", 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 LAST", Type.GetType("System.Guid")); * dt.Columns.Add(f13); * * createScript = string.Empty; * bool result = mysql.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 { _msg.Length = 0; _msg.Append("SQL Script: \r\n"); _msg.Append(createScript); Program._messageLog.WriteLine(_msg.ToString()); mysql.CloseConnection(); mysql = null; } }
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()); } }