public static void DataTableTest() { PFSQLServer sqlserv = new PFSQLServer(); string connectionString = string.Empty; Stopwatch sw = new Stopwatch(); PFDataExporter dex = new PFDataExporter(); PFDelimitedDataLine delimitedLine = null; PFFixedLengthDataLine fixedLenLine = 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; connectionString = sqlserv.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(); sqlserv.OpenConnection(); sqlserv.SQLQuery = _frm.txtSQLQuery.Text; if (_frm.chkIsStoredProcedure.Checked) { sqlserv.CommandType = CommandType.StoredProcedure; } else { sqlserv.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 = sqlserv.RunQueryDataTable(); sqlserv.returnResult += new PFSQLServer.ResultDelegate(OutputResults); sqlserv.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); //sw.Start(); DataTable tab = sqlserv.RunQueryDataTable(); //sqlserv.returnResultAsString += new PFSQLServer.ResultAsStringDelegate(OutputExtractFormattedData); sqlserv.returnResultAsString += new PFSQLServer.ResultAsStringDelegate(OutputResultsToFile); sqlserv.ExtractDelimitedDataFromTable(tab, ",", "\r\n", true); delimitedLine = dex.GetDelimitedLineDefinitionFromTable(tab, ",", "\r\n", true); delimitedLine.SaveToXmlFile(@"c:\temp\TableTestExtractLineFormat.xml"); 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 = sqlserv.RunQueryDataTable(); sqlserv.ExtractFixedLengthDataFromTable(tab, true, true, false); fixedLenLine = dex.GetFixedLengthLineDefinitionFromTable(tab, true, true, false); fixedLenLine.SaveToXmlFile(@"c:\temp\TableTestExtractFxlLineFormat.xml"); sw.Stop(); _msg.Length = 0; _msg.Append("Extract Fixed Length Table time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); sw.Start(); if (_textFile.FileIsOpen) { _textFile.CloseFile(); } _textFile.OpenFile(@"c:\temp\TableTestExtractFxlNoCrLf.txt", PFFileOpenOperation.OpenFileForWrite); tab = sqlserv.RunQueryDataTable(); sqlserv.ExtractFixedLengthDataFromTable(tab, false, true, false); fixedLenLine = dex.GetFixedLengthLineDefinitionFromTable(tab, false, true, false); fixedLenLine.SaveToXmlFile(@"c:\temp\TableTestExtractFxlNoCrLfLineFormat.xml"); sw.Stop(); _msg.Length = 0; _msg.Append("Extract Fixed Length (No CrLf) Table time: "); _msg.Append(sw.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); sqlserv.SaveDataTableToXmlSchemaFile(tab, @"c:\temp\Testtab.xsd"); sqlserv.SaveDataTableToXmlFile(tab, @"c:\temp\Testtab.xml"); sqlserv.SaveDataTableWithSchemaToXmlFile(tab, @"c:\temp\Testtabplus.xml"); DataTable tab2 = sqlserv.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 { sqlserv.CloseConnection(); sqlserv = null; if (_textFile.FileIsOpen) { _textFile.CloseFile(); } } }//end DataTableTest