public static void DataTableTest()
{
PFSQLAnywhereUL db = new PFSQLAnywhereUL();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
db.DatabasePath = _frm.txtDatabaseFile.Text;
db.DatabaseName = _frm.txtDatabaseName.Text;
db.Username = _frm.txtUsername.Text;
db.Password = _frm.txtPassword.Text;
db.DatabaseKey = _frm.txtDatabaseKey.Text;
connectionString = db.ConnectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (_frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
db.OpenConnection();
db.SQLQuery = _frm.txtSQLQuery.Text;
db.CommandType = CommandType.Text;
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
DataTable tab1 = db.RunQueryDataTable();
db.returnResult += new PFSQLAnywhereUL.ResultDelegate(OutputResults);
db.ProcessDataTable(tab1);
sw.Stop();
_msg.Length = 0;
_msg.Append("Process Table time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\TableTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
DataTable tab = db.RunQueryDataTable();
db.returnResultAsString += new PFSQLAnywhereUL.ResultAsStringDelegate(OutputResultsToFile);
db.ExtractDelimitedDataFromTable(tab, ",", "\r\n", true);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Delimiated Table time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
tab = null;
sw.Start();
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\TableTestExtractFXL.txt", PFFileOpenOperation.OpenFileForWrite);
tab = db.RunQueryDataTable();
db.ExtractFixedLengthDataFromTable(tab, true, true, false);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Fixed Length Table time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
db.SaveDataTableToXmlSchemaFile(tab, @"c:\temp\Testtab.xsd");
db.SaveDataTableToXmlFile(tab, @"c:\temp\Testtab.xml");
db.SaveDataTableWithSchemaToXmlFile(tab, @"c:\temp\Testtabplus.xml");
DataTable tab2 = db.LoadXmlFileToDataTable(@"c:\temp\Testtabplus.xml");;
int numRows = tab2.Rows.Count;
//StringWriter writer = new StringWriter();
//tab.WriteXml(writer, XmlWriteMode.WriteSchema);
//XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml(writer.ToString());
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(tab);
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
}
catch (System.Exception ex)
{
_frm.OutputErrorMessageToLog(ex);
}
finally
{
db.CloseConnection();
db = null;
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
}
}//end DataTableTest