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