public static void CreateTableTest(MainForm frm)
{
PFSQLServerCE40 sqlce = new PFSQLServerCE40();
string dataSource = frm.txtDataSource.Text;
string connectionString = string.Empty;
string tableName = "TestTabX01";
string tableName2 = "TestTabX02";
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 ABCDEFGH"));
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"));
//sqlce does not load char[] correctly. it mangles the characters when they are put into an ntext database column. image works but requires conversion to byte array first.
//All char[] values are turned into byte[] values by the import data table routine.
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());
}
}