public static void FixedLengthExtractFileTest(MainForm frm) { PFSQLServer sqlserv = new PFSQLServer(); string connectionString = string.Empty; string sqlQuery = "select GeographyKey, City, StateProvinceName as StateName, EnglishCountryRegionName as CountryName, PostalCode, SalesTerritoryKey as TerritoryKey from dbo.DimGeography;"; PFTextFile extractFile = new PFTextFile(@"c:\temp\FixedLengthFileTest.txt", PFFileOpenOperation.OpenFileForWrite); Stopwatch watch = new Stopwatch(); try { watch.Start(); sqlserv.ServerName = "PROFASTWS1"; sqlserv.DatabaseName = "AdventureWorksDW2008R2"; sqlserv.UseIntegratedSecurity = true; sqlserv.ApplicationName = "TextExtractTest"; sqlserv.WorkstationId = Environment.MachineName; connectionString = sqlserv.ConnectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); sqlserv.OpenConnection(); sqlserv.SQLQuery = sqlQuery; sqlserv.CommandType = CommandType.Text; SqlDataReader rdr = (SqlDataReader)sqlserv.RunQueryDataReader(); DataTable schemaTable = rdr.GetSchemaTable(); PFFixedLengthDataLine line = new PFFixedLengthDataLine(schemaTable.Rows.Count); line.UseLineTerminator = frm.chkFixedLengthCrLf.Checked; line.AllowDataTruncation = frm.chkAllowDataTruncation.Checked; //foreach (DataRow myField in schemaTable.Rows) //{ // //For each property of the field... // foreach (DataColumn myProperty in schemaTable.Columns) // { // //Display the field name and value. // Program._messageLog.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString() + " - " ); // } //} //Program._messageLog.WriteLine("--------------------------------"); //Program._messageLog.WriteLine(schemaTable.Rows[0]["DataType"].ToString()); //Program._messageLog.WriteLine(schemaTable.Rows[1].ItemArray[12].ToString()); //Program._messageLog.WriteLine(schemaTable.Rows[2].ItemArray[12].ToString()); //Program._messageLog.WriteLine(schemaTable.Rows[3].ItemArray[12].ToString()); //Program._messageLog.WriteLine(schemaTable.Rows[4].ItemArray[12].ToString()); //Program._messageLog.WriteLine(schemaTable.Rows[5].ItemArray[12].ToString()); ////DataRow row = schemaTable.Rows[0]; ////Program._messageLog.WriteLine(row["ColumnName"] + " is " + row["DataType"]); //Program._messageLog.WriteLine("--------------------------------"); //for (int rowInx = 0; rowInx < schemaTable.Rows.Count; rowInx++) //{ // DataRow row = schemaTable.Rows[rowInx]; // Program._messageLog.WriteLine(row["ColumnName"] + " is " + row["DataType"]); //} //Program._messageLog.WriteLine("--------------------------------"); //line.SetColumnDefinition(0, "GeographyKey", 12,PFDataAlign.RightJustify); //line.SetColumnDefinition(1, "City", 30, PFDataAlign.LeftJustify); //line.SetColumnDefinition(2, "StateName", 50, PFDataAlign.LeftJustify); //line.SetColumnDefinition(3, "CountryName", 50, PFDataAlign.LeftJustify); //line.SetColumnDefinition(4, "PostalCode", 15, PFDataAlign.LeftJustify); //line.SetColumnDefinition(5, "TerritoryKey", 12, PFDataAlign.RightJustify); //foreach (DataRow myField in schemaTable.Rows) //{ // //For each property of the field... // foreach (DataColumn myProperty in schemaTable.Columns) // { // //Display the field name and value. // Program._messageLog.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString()); // } //} for (int rowInx = 0; rowInx < schemaTable.Rows.Count; rowInx++) { DataRow row = schemaTable.Rows[rowInx]; string colName = row["ColumnName"].ToString(); System.Type colType = (System.Type)row["DataType"]; bool typeIsNumeric = PFFixedLengthDataLine.DataTypeIsNumeric(colType); int colLen = PFFixedLengthDataLine.GetNumericTypeMaxExtractLength(colType); if (colLen < 1) { colLen = (int)row["ColumnSize"]; } if (colName.Length > colLen) { colLen = colName.Length; } PFDataAlign dataAlignment = typeIsNumeric ? PFDataAlign.RightJustify : PFDataAlign.LeftJustify; line.SetColumnDefinition(rowInx, colName, colLen, dataAlignment); Program._messageLog.WriteLine(colName + ", " + colType.FullName + ", " + colLen.ToString() + ", " + dataAlignment.ToString()); } extractFile.WriteData(line.OutputColumnNames()); int numRows = 0; if (rdr.HasRows) { int colInx = 0; int maxColInx = -1; while (rdr.Read()) { numRows++; maxColInx = rdr.FieldCount - 1; for (colInx = 0; colInx <= maxColInx; colInx++) { line.SetColumnData(colInx, rdr[colInx].ToString()); } extractFile.WriteData(line.OutputColumnData()); } } _msg.Length = 0; _msg.Append("Number of data rows written: "); _msg.Append(numRows.ToString("#,##0")); _msg.Append("\r\n"); _msg.Append("Number of header rows written: 1"); 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 { sqlserv.CloseConnection(); sqlserv = null; if (extractFile != null) { extractFile.CloseFile(); extractFile = null; } watch.Stop(); _msg.Length = 0; _msg.Append("Elapsed time: "); _msg.Append(watch.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); } }//end test
}//end DelimitedLineTextFileTests public static void FixedLengthLineTextFileTests(int numRowsToOutput, bool useCrLfLineTerminator, bool allowDataTruncation) { PFTextFile textFile = new PFTextFile(@"c:\temp\FixedLengthText.txt", PFFileOpenOperation.OpenFileForWrite); PFFixedLengthDataLine line = new PFFixedLengthDataLine(5); int num = 0; string str = string.Empty; char[] tst = { 'a', 'b', 'c', 'd', 'e' }; try { line.UseLineTerminator = useCrLfLineTerminator; line.AllowDataTruncation = allowDataTruncation; line.SetColumnDefinition(0, "Column01", 10); line.SetColumnDefinition(1, "SecondColumnHeadingIsThis", 15); line.SetColumnDefinition(2, "Col3HDR", 5); line.SetColumnDefinition(3, "Col4", 35); line.SetColumnDefinition(4, "FifthColumn", 25, PFDataAlign.RightJustify); textFile.WriteData(line.OutputColumnNames()); for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++) { for (int inx = 0; inx < 5; inx++) { num = line.ColumnDefinitions.ColumnDefinition[inx].ColumnLength; if (inx == 4) { num = num - 7; } //str = new string(tst[inx],num); str = PFTextProcessor.RepeatChar(tst[inx], num); line.ColumnData.ColumnDataValue[inx].Data = str; str = null; } textFile.WriteData(line.OutputColumnData()); } textFile.CloseFile(); line.SaveToXmlFile(@"c:\temp\FixedLengthText.xml"); //read textfile textFile.OpenFile(@"c:\temp\FixedLengthText.txt", PFFileOpenOperation.OpenFileToRead); int lineLen = line.LineLength; string input = textFile.ReadData(lineLen); line.VerifyColumnNames(input); Program._messageLog.WriteLine(line.ColumnDefinitions.ToString()); input = textFile.ReadData(lineLen); int lineNo = 0; while (input != null) { lineNo++; line.ParseData(input); Program._messageLog.WriteLine("LINE " + lineNo.ToString() + ": " + line.ColumnData.ToString()); input = textFile.ReadData(lineLen); } textFile.CloseFile(); PFTextFile textFile2 = new PFTextFile(@"c:\temp\FixedLengthText2.txt", PFFileOpenOperation.OpenFileForWrite); PFFixedLengthDataLine line2 = PFFixedLengthDataLine.LoadFromXmlFile(@"c:\temp\FixedLengthText.xml"); Program._messageLog.WriteLine(line2.ToXmlString()); line2.UseLineTerminator = useCrLfLineTerminator; line2.SetColumnDefinition(0, "Column01", 10); line2.SetColumnDefinition(1, "SecondColumnHeadingIsThis", 15); line2.SetColumnDefinition(2, "Col3HDR", 5); line2.SetColumnDefinition(3, "Col4", 35); line2.SetColumnDefinition(4, "FifthColumn", 25, PFDataAlign.RightJustify); textFile2.WriteData(line.OutputColumnNames()); for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++) { for (int inx = 0; inx < 5; inx++) { num = line2.ColumnDefinitions.ColumnDefinition[inx].ColumnLength; if (inx == 4) { num = num - 7; } str = PFTextProcessor.RepeatChar(tst[inx], num); line2.ColumnData.ColumnDataValue[inx].Data = str; str = null; } textFile2.WriteData(line.OutputColumnData()); } Program._messageLog.WriteLine(line2.ToString()); 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) { if (textFile.FileIsOpen) { textFile.CloseFile(); } textFile = null; } } }//end method