public static void RunDatasetTest(MainForm frm, PFSQLServerCE40 db)
{
string query = string.Empty;
DataSet ds;
int numRows = -1;
_msg.Length = 0;
_msg.Append("Running RunDatasetTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
ds = db.RunQueryDataset(query, "dsTabCe");
if (ds.Tables["dstest"] != null)
{
if (ds.Tables["dstest"].Rows.Count > 0)
{
numRows = ds.Tables["dstest"].Rows.Count;
}
}
_msg.Length = 0;
_msg.Append("Number of rows in ");
_msg.Append(ds.Tables[0].TableName);
_msg.Append(": ");
_msg.Append(numRows.ToString("#,##0"));
Program._messageLog.WriteLine(_msg.ToString());
db.returnResult += new PFSQLServerCE40.ResultDelegate(OutputResults);
db.ProcessDataSet(ds);
db.returnResultAsString += new PFSQLServerCE40.ResultAsStringDelegate(OutputExtractFormattedData);
db.ExtractDelimitedDataFromDataSet(ds, "\t", "\r\n", true);
ds.DataSetName = "dsDataSetCe";
db.SaveDataSetToXmlFile(ds, @"c:\Temp\TestCeDs.xml");
db.SaveDataSetWithSchemaToXmlFile(ds, @"c:\Temp\TestCeDsPlus.xml");
db.SaveDataSetToXmlSchemaFile(ds, @"c:\Temp\TestCeDs.xsd");
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataSetToXmlDocument(ds);
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("...RunDatasetTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void RunReaderToDataTableTest(MainForm frm, PFSQLServerCE40 db)
{
string query = string.Empty;
_msg.Length = 0;
_msg.Append("Running RunReaderToDataTableTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
SqlCeDataReader rdr = (SqlCeDataReader)db.RunQueryDataReader(query);
DataTable tab = db.ConvertDataReaderToDataTable(rdr);
_msg.Length = 0;
_msg.Append("Number of rows in table: ");
_msg.Append(tab.Rows.Count);
Program._messageLog.WriteLine(_msg.ToString());
int inx = 0;
int maxInx = tab.Rows.Count - 1;
int inxCol = 0;
int maxInxCol = tab.Columns.Count - 1;
for (inx = 0; inx <= maxInx; inx++)
{
_msg.Length = 0;
DataRow row = tab.Rows[inx];
for (inxCol = 0; inxCol <= maxInxCol; inxCol++)
{
DataColumn col = tab.Columns[inxCol];
_msg.Append(col.ColumnName);
_msg.Append(": ");
_msg.Append(row[inxCol].ToString());
if (inxCol < maxInxCol)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("...RunReaderToDataTableTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void ImportDataTableTest(MainForm frm)
{
PFSQLServerCE40 db = new PFSQLServerCE40();
string fileName = @"c:\temp\AppSettings_40.sdf";
string dataSource = fileName;
string connectionString = string.Empty;
string createScript = string.Empty;
try
{
_msg.Length = 0;
_msg.Append("ImportDataTableTest started ...");
Program._messageLog.WriteLine(_msg.ToString());
if (File.Exists(fileName))
{
File.Delete(fileName);
}
db.DatabasePath = dataSource;
connectionString = db.ConnectionString;
//create the database
bool dbCreated = db.CreateDatabase(connectionString);
if (dbCreated == false)
{
_msg.Length = 0;
_msg.Append("Attempt to create database ");
_msg.Append(fileName);
_msg.Append(" failed.");
throw new DataException(_msg.ToString());
}
db.OpenConnection();
//create the table
DataTable dt = frm.keyValsDataSet.Tables["KeyValTable"];
db.CreateTable(dt);
db.ImportDataFromDataTable(dt);
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("... ImportDataTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void RunReaderTest(MainForm frm, PFSQLServerCE40 db)
{
string query = string.Empty;
_msg.Length = 0;
_msg.Append("Running RunReaderTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
SqlCeDataReader rdr = (SqlCeDataReader)db.RunQueryDataReader(query);
db.returnResult += new PFSQLServerCE40.ResultDelegate(OutputResults);
//sqlce.returnResultAsString += new PFSQLServerCE40.ResultAsStringDelegate(OutputResultsAsString);
db.ProcessDataReader(rdr);
rdr.Close();
rdr = (SqlCeDataReader)db.RunQueryDataReader(query);
db.SaveDataReaderToXmlFile(rdr, @"c:\temp\TestCeRdr.xml");
rdr.Close();
rdr = (SqlCeDataReader)db.RunQueryDataReader(query);
db.SaveDataReaderWithSchemaToXmlFile(rdr, @"c:\temp\TestCeRdrPlus.xml");
rdr.Close();
rdr = (SqlCeDataReader)db.RunQueryDataReader(query);
db.SaveDataReaderToXmlSchemaFile(rdr, @"c:\temp\TestCeRdr.xsd");
rdr.Close();
rdr = (SqlCeDataReader)db.RunQueryDataReader(query);
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(db.ConvertDataReaderToDataTable(rdr));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
rdr.Close();
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("...RunReaderTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void RunDataTableTest(MainForm frm, PFSQLServerCE40 db)
{
string query = string.Empty;
DataTable tab = null;
_msg.Length = 0;
_msg.Append("Running RunDataTableTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
tab = db.RunQueryDataTable(query, "tabtest");
_msg.Length = 0;
_msg.Append("Number of rows in table: ");
_msg.Append(tab.Rows.Count.ToString("#,##0"));
Program._messageLog.WriteLine(_msg.ToString());
db.returnResult += new PFSQLServerCE40.ResultDelegate(OutputResults);
db.ProcessDataTable(tab);
db.returnResultAsString += new PFSQLServerCE40.ResultAsStringDelegate(OutputExtractFormattedData);
db.ExtractDelimitedDataFromTable(tab, ",", "\r\n", true);
tab.TableName = "TabTestCe";
db.SaveDataTableToXmlFile(tab, @"c:\temp\TestCeTab.xml");
db.SaveDataTableWithSchemaToXmlFile(tab, @"c:\temp\TestCeTabPlus.xml");
db.SaveDataTableToXmlSchemaFile(tab, @"c:\temp\TestCeTab.xsd");
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(tab);
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("...RunDataTableTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static int RunNonQueryTest(MainForm frm, PFSQLServerCE40 db)
{
int numRowsAffected = -1;
string query = string.Empty;
_msg.Length = 0;
_msg.Append("Running RunNonQueryTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
numRowsAffected = db.RunNonQuery(query);
db.CloseConnection();
_msg.Length = 0;
_msg.Append("Query: ");
_msg.Append(frm.txtQuery.Text);
_msg.Append("\r\n");
_msg.Append("Num rows affected: ");
_msg.Append(numRowsAffected.ToString("#,##0"));
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("...RunNonQueryTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
return(numRowsAffected);
}
public static void RunResultsetTest(MainForm frm, PFSQLServerCE40 db)
{
SqlCeResultSet res = null;
string query = string.Empty;
_msg.Length = 0;
_msg.Append("Running RunResultsetTest ...");
Program._messageLog.WriteLine(_msg.ToString());
try
{
query = frm.txtQuery.Text;
db.OpenConnection();
res = db.RunQueryResultset(query);
if (res.HasRows)
{
db.returnResult += new PFSQLServerCE40.ResultDelegate(OutputResults);
//sqlce.returnResultAsString += new PFSQLServerCE40.ResultAsStringDelegate(OutputResultsAsString);
db.ProcessResultSet(res);
res.Close();
res = db.RunQueryResultset(query);
db.returnResultAsString += new PFSQLServerCE40.ResultAsStringDelegate(OutputExtractFormattedData);
db.ExtractDelimitedDataFromResultSet(res, "\t", "\r\n", true);
res.Close();
res = db.RunQueryResultset(query);
db.SaveResultSetToXmlFile(res, @"c:\temp\TestCeRes.xml");
res.Close();
res = db.RunQueryResultset(query);
db.SaveResultSetWithSchemaToXmlFile(res, @"c:\temp\TestCeResPlus.xml");
res.Close();
res = db.RunQueryResultset(query);
db.SaveResultSetToXmlSchemaFile(res, @"c:\temp\TestCeRes.xsd");
res.Close();
res = db.RunQueryResultset(query);
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(PFSQLServerCE40.ConvertResultSetToDataTable(res, "ResultTable"));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
res.Close();
}
db.CloseConnection();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("...RunResultsetTest Finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
//tests
public static void ConnectionTest(MainForm frm)
{
PFSQLServerCE40 db = new PFSQLServerCE40();
try
{
_msg.Length = 0;
_msg.Append("ConnectionTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
db.DatabasePath = frm.txtDataSource.Text;
db.DatabasePassword = frm.txtPassword.Text;
db.EncryptionOn = frm.chkEncryptionOn.Checked;
db.EncryptionMode = (SQLCE40EncryptionMode)Enum.Parse(typeof(SQLCE40EncryptionMode), frm.cboEncryptionMode.Text);
db.OpenConnection();
_msg.Length = 0;
_msg.Append("Connection string is ");
_msg.Append(db.ConnectionString);
_msg.Append("\r\n");
_msg.Append("Connection state is ");
_msg.Append(db.Connection.State.ToString());
_msg.Append("\r\n");
_msg.Append("Data Source is ");
_msg.Append(db.DatabasePath);
_msg.Append("\r\n");
_msg.Append("Password is ");
_msg.Append(db.DatabasePassword);
_msg.Append("\r\n");
_msg.Append("EncryptionOn is ");
_msg.Append(db.EncryptionOn.ToString());
_msg.Append("\r\n");
_msg.Append("EncryptionMode is ");
_msg.Append(db.EncryptionMode.ToString());
_msg.Append("\r\n");
Program._messageLog.WriteLine(_msg.ToString());
foreach (stKeyValuePair <string, string> kv in db.ConnectionStringKeyVals)
{
_msg.Length = 0;
_msg.Append(kv.Key + "=" + kv.Value);
Program._messageLog.WriteLine(_msg.ToString());
}
Program._messageLog.WriteLine(Environment.NewLine);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... ConnectionTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
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());
}
}