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
public static void DelimitedLineTextFileTests(int numRowsToOutput) { PFTextFile textFile = new PFTextFile(@"c:\temp\DelimitedText.txt", PFFileOpenOperation.OpenFileForWrite); PFDelimitedDataLine line = new PFDelimitedDataLine(5); RandomString dat = new RandomString(); RandomNumber siz = new RandomNumber(); int num = 0; string str = string.Empty; try { line.LineTerminator = "\r\n"; line.ColumnSeparator = ","; line.SetColumnDefinition(0, "FirstColumn", 10); line.SetColumnDefinition(1, "SecondColumn", 15); line.SetColumnDefinition(2, "ColumnThree", 5); line.SetColumnDefinition(3, "ColumnFour", 35); line.SetColumnDefinition(4, "FifthColumn", 25); textFile.WriteData(line.OutputColumnNames()); for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++) { for (int inx = 0; inx < 5; inx++) { num = siz.GenerateRandomInt(1, line.ColumnDefinitions.ColumnDefinition[inx].ColumnLength); str = dat.GetStringAL(num); line.ColumnData.ColumnDataValue[inx].Data = str; } textFile.WriteData(line.OutputColumnData()); } textFile.CloseFile(); line.SaveToXmlFile(@"c:\temp\DelimitedText.xml"); //read textfile textFile.OpenFile(@"c:\temp\DelimitedText.txt", PFFileOpenOperation.OpenFileToRead); string input = textFile.ReadLine(); line.VerifyColumnNames(input); Program._messageLog.WriteLine(line.ColumnDefinitions.ToString()); input = textFile.ReadLine(); line.ParseData(input); Program._messageLog.WriteLine(line.ColumnData.ToString()); textFile.CloseFile(); //double check loading of xml schema works PFDelimitedDataLine line2 = PFDelimitedDataLine.LoadFromXmlFile(@"c:\temp\DelimitedText.xml"); Program._messageLog.WriteLine(line2.ToXmlString()); PFTextFile textFile2 = new PFTextFile(@"c:\temp\DelimitedText2.txt", PFFileOpenOperation.OpenFileForWrite); char[] tst = { 'a', 'b', 'c', 'd', 'e' }; //line2.LineTerminator = "\r\n"; line2.ColumnSeparator = ","; line2.SetColumnDefinition(0, "Column One", 10); line2.SetColumnDefinition(1, "2ndColumn", 15); line2.SetColumnDefinition(2, "Column Three", 5); line2.SetColumnDefinition(3, "Column Four", 35); line2.SetColumnDefinition(4, "5thColumn", 25); textFile2.WriteData(line2.OutputColumnNames()); for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++) { for (int inx = 0; inx < 5; inx++) { num = siz.GenerateRandomInt(1, line2.ColumnDefinitions.ColumnDefinition[inx].ColumnLength); str = new string(tst[inx], num); line2.ColumnData.ColumnDataValue[inx].Data = str; } textFile2.WriteData(line2.OutputColumnData()); } textFile2.CloseFile(); } 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 (textFile != null) { textFile = null; } ; } }//end DelimitedLineTextFileTests