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