/// <summary>
/// Retrieves formatting information for a line in a fixed length text file.
/// </summary>
/// <param name="tab">DataTable containing the data.</param>
/// <param name="useLineTerminator">String containing one or more characters that will denote the end of a line of data.</param>
/// <param name="columnNamesOnFirstLine">If true, a line containing the column names in delimited format will be the first line returned.</param>
/// <param name="allowDataTruncation">If true, data longer than defined column length will be truncated; otherwise an exception will be thrown.</param>
/// <param name="tableNumber">Arbitrary number used to identify the table.</param>
/// <param name="lineTerminatorChars">Default is CR/LF for characters to mark end of line. You can override the default by setting this parameter.</param>
/// <returns>PFFixedLengthDataLine object.</returns>
public PFFixedLengthDataLine GetFixedLengthLineDefinitionFromTable(DataTable tab,
bool useLineTerminator,
bool columnNamesOnFirstLine,
bool allowDataTruncation,
int tableNumber,
string lineTerminatorChars)
{
int colInx = 0;
int maxColInx = -1;
int colLen = -1;
DataColumnCollection cols = tab.Columns;
PFFixedLengthDataLine fixedLengthLine = new PFFixedLengthDataLine(cols.Count);
maxColInx = tab.Columns.Count - 1;
fixedLengthLine.UseLineTerminator = useLineTerminator;
fixedLengthLine.LineTerminator = lineTerminatorChars;
fixedLengthLine.ColumnNamesOnFirstLine = columnNamesOnFirstLine;
fixedLengthLine.AllowDataTruncation = allowDataTruncation;
fixedLengthLine.MaxColumnLengthOverride = this.MaxColumnLengthOverride;
for (colInx = 0; colInx <= maxColInx; colInx++)
{
if (PFFixedLengthDataLine.DataTypeIsNumeric(cols[colInx].DataType))
{
colLen = PFFixedLengthDataLine.GetNumericTypeMaxExtractLength(cols[colInx].DataType);
}
else if (PFFixedLengthDataLine.DataTypeIsDateTime(cols[colInx].DataType))
{
colLen = PFFixedLengthDataLine.GetDateTimeTypeMaxExtractLength(cols[colInx].DataType);
}
else
{
colLen = cols[colInx].MaxLength;
}
fixedLengthLine.SetColumnDefinition(colInx, cols[colInx].ColumnName, colLen);
}
if (columnNamesOnFirstLine == true)
{
if (returnResultAsString != null)
{
returnResultAsString(fixedLengthLine.OutputColumnNames(), tableNumber);
}
}
return(fixedLengthLine);
}
/// <summary>
/// Extracts data from DataTable into fixed length text format.
/// </summary>
/// <param name="tab">DataTable containing the data.</param>
/// <param name="useLineTerminator">String containing one or more characters that will denote the end of a line of data.</param>
/// <param name="columnNamesOnFirstLine">If true, a line containing the column names in delimited format will be the first line returned.</param>
/// <param name="allowDataTruncation">If true, data longer than defined column length will be truncated; otherwise an exception will be thrown.</param>
/// <param name="tableNumber">Arbitrary number used to identify the table.</param>
/// <param name="lineTerminatorChars">Default is CR/LF for characters to mark end of line. You can override the default by setting this parameter.</param>
private void ExtractFixedLengthDataFromTable(DataTable tab,
bool useLineTerminator,
bool columnNamesOnFirstLine,
bool allowDataTruncation,
int tableNumber,
string lineTerminatorChars)
{
int rowInx = 0;
int maxRowInx = -1;
int colInx = 0;
int maxColInx = -1;
int colLen = -1;
DataColumnCollection cols = tab.Columns;
PFFixedLengthDataLine fixedLengthLine = new PFFixedLengthDataLine(cols.Count);
maxRowInx = tab.Rows.Count - 1;
maxColInx = tab.Columns.Count - 1;
fixedLengthLine.UseLineTerminator = useLineTerminator;
fixedLengthLine.LineTerminator = lineTerminatorChars;
fixedLengthLine.ColumnNamesOnFirstLine = columnNamesOnFirstLine;
fixedLengthLine.AllowDataTruncation = allowDataTruncation;
fixedLengthLine.MaxColumnLengthOverride = this.MaxColumnLengthOverride;
fixedLengthLine.DefaultStringColumnLength = this.DefaultStringColumnLength;
for (colInx = 0; colInx <= maxColInx; colInx++)
{
if (PFFixedLengthDataLine.DataTypeIsNumeric(cols[colInx].DataType))
{
colLen = PFFixedLengthDataLine.GetNumericTypeMaxExtractLength(cols[colInx].DataType);
}
else if (PFFixedLengthDataLine.DataTypeIsDateTime(cols[colInx].DataType))
{
colLen = PFFixedLengthDataLine.GetDateTimeTypeMaxExtractLength(cols[colInx].DataType);
}
else
{
colLen = cols[colInx].MaxLength;
}
fixedLengthLine.SetColumnDefinition(colInx, cols[colInx].ColumnName, colLen);
}
if (columnNamesOnFirstLine == true)
{
if (returnResultAsString != null)
{
returnResultAsString(fixedLengthLine.OutputColumnNames(), tableNumber);
}
}
for (rowInx = 0; rowInx <= maxRowInx; rowInx++)
{
DataRow row = tab.Rows[rowInx];
if (returnResultAsString != null)
{
for (colInx = 0; colInx <= maxColInx; colInx++)
{
fixedLengthLine.SetColumnData(colInx, row[colInx].ToString());
}
returnResultAsString(fixedLengthLine.OutputColumnData(), tableNumber);
}
}
}//end ExtractFixedLengthDataFromTable method
}//end ExtractFixedLengthDataFromTable method
/// <summary>
/// Extracts data from DataTable into fixed length text format.
/// </summary>
/// <param name="fixedLengthLineDef">Object containing column definitions to use for the output fixed length text data.</param>
/// <param name="tab">DataTable containing the data.</param>
/// <param name="useLineTerminator">String containing one or more characters that will denote the end of a line of data.</param>
/// <param name="columnNamesOnFirstLine">If true, a line containing the column names in delimited format will be the first line returned.</param>
/// <param name="allowDataTruncation">If true, data longer than defined column length will be truncated; otherwise an exception will be thrown.</param>
/// <param name="tableNumber">Arbitrary number used to identify the table.</param>
/// <param name="lineTerminatorChars">Default is CR/LF for characters to mark end of line. You can override the default by setting this parameter.</param>
/// <remarks>This version of ExtractFixedLengthDataFromTable adjusts column definitions to allow for changes to the data lengths in the data table.</remarks>
public void ExtractFixedLengthDataFromTable(PFFixedLengthDataLine fixedLengthLineDef,
DataTable tab,
bool useLineTerminator,
bool columnNamesOnFirstLine,
bool allowDataTruncation,
int tableNumber,
string lineTerminatorChars)
{
int rowInx = 0;
int maxRowInx = -1;
int dtColInx = 0;
int defColInx = 0;
int maxColInx = -1;
int colLen = -1;
DataColumnCollection cols = tab.Columns;
PFFixedLengthDataLine dtFixedLengthLine = new PFFixedLengthDataLine(cols.Count);
bool colDefOverride = false;
maxRowInx = tab.Rows.Count - 1;
maxColInx = tab.Columns.Count - 1;
dtFixedLengthLine.UseLineTerminator = fixedLengthLineDef.UseLineTerminator;
dtFixedLengthLine.LineTerminator = fixedLengthLineDef.LineTerminator;
dtFixedLengthLine.ColumnNamesOnFirstLine = fixedLengthLineDef.ColumnNamesOnFirstLine;
dtFixedLengthLine.AllowDataTruncation = fixedLengthLineDef.AllowDataTruncation;
dtFixedLengthLine.MaxColumnLengthOverride = fixedLengthLineDef.MaxColumnLengthOverride;
dtFixedLengthLine.DefaultStringColumnLength = fixedLengthLineDef.DefaultStringColumnLength;
for (dtColInx = 0; dtColInx <= maxColInx; dtColInx++)
{
for (defColInx = 0; defColInx < fixedLengthLineDef.ColumnDefinitions.ColumnDefinition.Length; defColInx++)
{
colDefOverride = false;
if (tab.Columns[dtColInx].ColumnName == fixedLengthLineDef.ColumnDefinitions.ColumnDefinition[defColInx].ColumnName)
{
dtFixedLengthLine.SetColumnDefinition(dtColInx,
fixedLengthLineDef.ColumnDefinitions.ColumnDefinition[defColInx].ColumnName,
fixedLengthLineDef.ColumnDefinitions.ColumnDefinition[defColInx].ColumnLength,
fixedLengthLineDef.ColumnDefinitions.ColumnDefinition[defColInx].ColumnDataAlignment);
colDefOverride = true;
break;
}
}
if (colDefOverride == false)
{
//dynamically build the column definition
if (PFFixedLengthDataLine.DataTypeIsNumeric(tab.Columns[dtColInx].DataType))
{
colLen = PFFixedLengthDataLine.GetNumericTypeMaxExtractLength(tab.Columns[dtColInx].DataType);
}
else if (PFFixedLengthDataLine.DataTypeIsDateTime(tab.Columns[dtColInx].DataType))
{
colLen = PFFixedLengthDataLine.GetDateTimeTypeMaxExtractLength(tab.Columns[dtColInx].DataType);
}
else
{
colLen = tab.Columns[dtColInx].MaxLength;
}
dtFixedLengthLine.SetColumnDefinition(dtColInx, tab.Columns[dtColInx].ColumnName, colLen, PFDataAlign.LeftJustify);
}
}
if (columnNamesOnFirstLine == true)
{
if (returnResultAsString != null)
{
returnResultAsString(dtFixedLengthLine.OutputColumnNames(), tableNumber);
}
}
for (rowInx = 0; rowInx <= maxRowInx; rowInx++)
{
DataRow row = tab.Rows[rowInx];
if (returnResultAsString != null)
{
for (dtColInx = 0; dtColInx <= maxColInx; dtColInx++)
{
dtFixedLengthLine.SetColumnData(dtColInx, row[dtColInx].ToString());
}
returnResultAsString(dtFixedLengthLine.OutputColumnData(), tableNumber);
}
}
}//end method
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
