/// <summary> /// Method to specify column definitions for the current instance. /// </summary> /// <param name="columnDefinitionsExt">Object containing the array of column definitions.</param> /// <param name="columnIndex">0-based index of the column being defined.</param> /// <param name="sourceColumnName">Name to assign to the column. This will become the column header for the output.</param> /// <param name="columnLength">Length in bytes of the column. </param> /// <param name="columnDataAlignment">See <see cref="PFDataAlign"/> enumeration.</param> /// <remarks>If sourceColumnName is longer than columnLength, the sourceColumnName value will be truncated to columnLength.</remarks> public static void SetColumnDefinitionExt(PFColumnDefinitionsExt columnDefinitionsExt, int columnIndex, string sourceColumnName, int columnLength, PFDataAlign columnDataAlignment) { int minColLen = 1; int maxColumnLengthOverride = 1024; int defaultStringColumnLength = 255; string colName = sourceColumnName; string newColName = string.Empty; int colLen = columnLength > maxColumnLengthOverride ? maxColumnLengthOverride : columnLength; if (colLen < minColLen) { colLen = defaultStringColumnLength; } if (colName.Length > colLen) { colName = colName.Substring(0, colLen); } columnDefinitionsExt.ColumnDefinition[columnIndex].SourceColumnName = sourceColumnName; columnDefinitionsExt.ColumnDefinition[columnIndex].OutputColumnName = colName; columnDefinitionsExt.ColumnDefinition[columnIndex].OutputColumnLength = colLen; columnDefinitionsExt.ColumnDefinition[columnIndex].OutputColumnDataAlignment = columnDataAlignment; }
/// <summary> /// Method to specify column definitions for the current instance. /// </summary> /// <param name="columnIndex">0-based index of the column being defined.</param> /// <param name="columnName">Name to assign to the column. This will become the column header for the output.</param> /// <param name="columnLength">Length in bytes of the column. </param> /// <param name="columnDataAlignment">See <see cref="PFDataAlign"/> enumeration.</param> /// <remarks>If columnName is longer than columnLength, the columnName value will be truncated to columnLength.</remarks> public void SetColumnDefinition(int columnIndex, string columnName, int columnLength, PFDataAlign columnDataAlignment) { string colName = columnName; string newColName = string.Empty; int colLen = columnLength > this.MaxColumnLengthOverride ? this.MaxColumnLengthOverride : columnLength; if (colLen < _minColLen) { colLen = this.DefaultStringColumnLength; } if (colName.Length > colLen) { //newColName = "F" + columnIndex.ToString() + new String(' ', colLen); //colName = newColName.Substring(0, colLen).Trim(); if (this.AllowDataTruncation == true) { colName = colName.Substring(0, colLen); } else { newColName = "F" + columnIndex.ToString() + new String(' ', colLen); colName = newColName.Substring(0, colLen).Trim(); ////data truncation not allowed //_msg.Length = 0; //_msg.Append("Truncation error for column heading "); //_msg.Append(columnName); //_msg.Append(". Heading has length of "); //_msg.Append(colName.Length.ToString()); //_msg.Append(", which is longer than allowed length of "); //_msg.Append(colLen); //_msg.Append("."); //throw new System.ArgumentException(_msg.ToString()); } } this.ColumnDefinitions.ColumnDefinition[columnIndex].ColumnName = colName; this.ColumnDefinitions.ColumnDefinition[columnIndex].ColumnLength = colLen; this.ColumnDefinitions.ColumnDefinition[columnIndex].ColumnDataAlignment = columnDataAlignment; }
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