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