/// <summary>
/// Main worker method
/// </summary>
static void DoWork()
{
Dictionary <string, ColHeader> Cols = null;
string ServerName = AppSettingsImpl.Server.Value;
string DatabaseName = AppSettingsImpl.Db.Value;
string TableName = AppSettingsImpl.Tbl.Value;
string FileName = AppSettingsImpl.File.Value;
bool Fixed = AppSettingsImpl.Type.Value.ToLower() == "fixed";
int MaxWidth = AppSettingsImpl.MaxWidth.Initialized ? AppSettingsImpl.MaxWidth.Value : 0;
DateTime Start = DateTime.Now;
if (AppSettingsImpl.Type.Value.ToLower() == "fixed")
{
Log.InformationMessage("Computing column widths");
bool FitToHeader = ShouldFitDataToHeader(); // if true, widen data. If false, truncat headers
Cols = ServerUtils.GetColInfo(ServerName, DatabaseName, TableName, ShouldFitDataToHeader(), true, MaxWidth); // column names will be lower case
if (!FitToHeader)
{
Log.InformationMessage("Narrowing headers");
ColumnUniquer.NarrowHeaders(Cols); // modifies Cols
}
}
else
{
Cols = ServerUtils.GetColInfo(ServerName, DatabaseName, TableName, false, false, MaxWidth);
}
string Delimiter = AppSettingsImpl.Delimiter.Value.Xlat(new string[] { "pipe", "comma", "tab" }, new string[] { "|", ",", "\t" });
Log.InformationMessage("Exporting data");
int Pad = AppSettingsImpl.Pad.Value;
bool Append = AppSettingsImpl.Append.Value;
bool ShouldWriteHeader = AppSettingsImpl.Header.Initialized && !Append;
int MaxRows = AppSettingsImpl.MaxRows.Value;
bool Quote = AppSettingsImpl.Quote.Value;
int RecordsWritten;
bool FoundDelimiterInData = ServerUtils.DoExport(ServerName, DatabaseName, TableName, FileName, Cols, Fixed,
Delimiter, Pad, Append, ShouldWriteHeader, MaxRows, Quote, out RecordsWritten);
if (!Fixed && FoundDelimiterInData && !Quote)
{
Log.InformationMessage("Note: The specified delimiter was found in the data, but the -quote option was not specified. " +
"The generated file may not be able to be successfully parsed.");
}
if (AppSettingsImpl.Type.Value == "fixed" && AppSettingsImpl.Widths.Value)
{
List <ColHeader> Hdrs = ColHeader.ToList(Cols);
int[] Lengths = new int[Hdrs.Count];
for (int i = 0; i < Hdrs.Count; ++i)
{
Lengths[i] = Hdrs[i].FieldWidth;
}
Log.InformationMessage("Exported field widths: {0}", string.Join(",", Lengths));
}
Log.InformationMessage("Export completed -- Records written: {0} Elapsed time (HH:MM:SS.Milli): {1}", RecordsWritten, DateTime.Now - Start);
}
/// <summary>
/// Computes column widths. For varchar max fields, issues a select statement to determine the widths. Can be time-consuming for
/// a large table. Otherwise uses metadata to compute the widths
/// </summary>
/// <param name="ServerName">The server name</param>
/// <param name="DatabaseName">The database name</param>
/// <param name="TableName">the table name</param>
/// <param name="ServerCols">A list of table columns with metadata</param>
/// <param name="MaxWidth">The max width for output columns. If zero, then don't impose a maximum.</param>
/// <returns>A dictionary keyed by column name containing corresponding column metadata</returns>
private static Dictionary <string, ColHeader> ComputeColWidths(string ServerName, string DatabaseName, string TableName,
List <Column> ServerCols, int MaxWidth)
{
Dictionary <Column, int> VariableCols = new Dictionary <Column, int> ();
Dictionary <Column, int> NonVariableCols = new Dictionary <Column, int>();
foreach (Column Col in ServerCols)
{
if (MaxWidth == 0 && Col.DataType.Name.ToLower().In("varchar", "nvarchar") && Col.DataType.MaximumLength == -1)
{
// if there's a max width then we don't do varchar max calculation from the DB
VariableCols.Add(Col, 0);
}
else if (MaxWidth == 0 && Col.DataType.Name.ToLower().In("varchar", "nvarchar", "char", "nchar") && AppSettingsImpl.Trim)
{
VariableCols.Add(Col, 0);
}
else
{
NonVariableCols.Add(Col, 0);
}
}
VariableCols = ComputeCharColWidths(ServerName, DatabaseName, TableName, VariableCols);
NonVariableCols = ComputeNonVarcharMaxColWidths(ServerName, DatabaseName, TableName, NonVariableCols, MaxWidth);
// zip everything back together
Dictionary <string, ColHeader> Cols = new Dictionary <string, ColHeader>();
int OrdinalPosition = 0;
foreach (Column Col in ServerCols) // now zip them back together
{
int ColWidth = VariableCols.ContainsKey(Col) ? VariableCols[Col] : NonVariableCols[Col];
ColHeader Hdr = new ColHeader(Col.Name.ToLower(), ColWidth, Col.DataType, OrdinalPosition++);
Cols.Add(Col.Name.ToLower(), Hdr);
}
return(Cols);
}
/// <summary>
/// Exports data from a database table to a file
/// </summary>
/// <param name="ServerName">The server name</param>
/// <param name="DatabaseName">The database name</param>
/// <param name="TableName">the table name</param>
/// <param name="FileName">The file to export to</param>
/// <param name="HeadersDict">the Columns to export</param>
/// <param name="Fixed">true if field-length export</param>
/// <param name="Delimiter">Delimiter, if not fixed length</param>
/// <param name="Pad">The number of characters of padding to add to each column if fixed. Zero if no padding</param>
/// <param name="Append">True to append to the output file. Otherwise re-create the output file</param>
/// <param name="ShouldWriteHeader">True to write a header</param>
/// <param name="MaxRows">Max rows (excl. header) to write</param>
/// <param name="Quote">True to quote fields if fields contain a delimiter character</param>
/// <returns>true if delimiters were found in the fields, and quoting was not specified</returns>
public static bool DoExport(string ServerName, string DatabaseName, string TableName, string FileName,
Dictionary <string, ColHeader> HeadersDict, bool Fixed, string Delimiter, int Pad, bool Append, bool ShouldWriteHeader, int MaxRows, bool Quote,
out int RecordsWritten)
{
List <ColHeader> ColHeaders = ColHeader.ToList(HeadersDict);
string Sql = BuildSelect(TableName, ColHeaders);
bool FoundDelimitersInField = false;
string Padding = Pad != 0 ? new string(' ', Pad) : string.Empty;
int RecordCount = 0;
using (SqlConnection Cnct = GetSqlConnection(ServerName, DatabaseName))
using (StreamWriter Writer = new StreamWriter(FileName, Append))
{
SqlCommand command = new SqlCommand(Sql, Cnct)
{
CommandTimeout = GetSQLTimeout()
};
command.Connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (RecordCount == 0 && ShouldWriteHeader)
{
Writer.WriteLine(BuildHeader(ColHeaders, Fixed, Delimiter, Pad));
}
foreach (ColHeader Hdr in ColHeaders)
{
if (!Fixed && Hdr.OrdinalPosition > 0)
{
Writer.Write(Delimiter);
}
string Fld = Hdr.ValueFrom(reader, Fixed);
if (!Fixed && (Fld.Contains(Delimiter) || Fld.Contains(ONE_DBL_QUOTE)))
{
// if the field contains a delimiter, quote it. If it contains any quotes, also
// quote it so the reader can strip the quotes and get the original contents back
if (Quote)
{
Fld = ONE_DBL_QUOTE + Fld.Replace(ONE_DBL_QUOTE, TWO_DBL_QUOTES) + ONE_DBL_QUOTE;
}
FoundDelimitersInField = true;
}
Writer.Write(Fld);
if (Pad != 0)
{
Writer.Write(Padding);
}
}
Writer.WriteLine();
if (++RecordCount >= MaxRows)
{
command.Cancel();
break;
}
if (RecordCount % 10000 == 0)
{
Log.InformationMessage("Records: {0}", RecordCount);
}
} // while (reader.Read())
} // using SqlDataReader
}// using SqlConnection & StreamWriter
RecordsWritten = RecordCount;
return(FoundDelimitersInField);
}