/// <summary>
/// Extracts data from DataTable into delimited text format.
/// </summary>
/// <param name="tab">DataTable containing the data.</param>
/// <param name="columnSeparator">String containing one or more characters that will separate columns in the line of data that is extracted.</param>
/// <param name="lineTerminator">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="tableNumber">Arbitrary number used to identify the table.</param>
/// <param name="stringValuesSurroundedWithQuotationMarks">If true, string values will be surrounded by double quotes.</param>
public void ExtractDelimitedDataFromTable(DataTable tab,
string columnSeparator,
string lineTerminator,
bool columnNamesOnFirstLine,
int tableNumber,
bool stringValuesSurroundedWithQuotationMarks)
{
int rowInx = 0;
int maxRowInx = -1;
int colInx = 0;
int maxColInx = -1;
DataColumnCollection cols = tab.Columns;
PFDelimitedDataLine delimitedLine = new PFDelimitedDataLine(cols.Count);
bool[] surroundWithQuotationMarks = new bool[tab.Columns.Count];
maxRowInx = tab.Rows.Count - 1;
maxColInx = tab.Columns.Count - 1;
delimitedLine.ColumnSeparator = columnSeparator;
delimitedLine.LineTerminator = lineTerminator;
delimitedLine.StringValuesSurroundedWithQuotationMarks = stringValuesSurroundedWithQuotationMarks;
for (colInx = 0; colInx <= maxColInx; colInx++)
{
delimitedLine.SetColumnDefinition(colInx, cols[colInx].ColumnName);
if (cols[colInx].DataType == typeof(System.String) && stringValuesSurroundedWithQuotationMarks)
{
surroundWithQuotationMarks[colInx] = true;
}
else
{
surroundWithQuotationMarks[colInx] = false;
}
}
if (columnNamesOnFirstLine == true)
{
if (returnResultAsString != null)
{
returnResultAsString(delimitedLine.OutputColumnNames(), tableNumber);
}
}
for (rowInx = 0; rowInx <= maxRowInx; rowInx++)
{
DataRow row = tab.Rows[rowInx];
if (returnResultAsString != null)
{
for (colInx = 0; colInx <= maxColInx; colInx++)
{
if (stringValuesSurroundedWithQuotationMarks)
{
delimitedLine.StringValuesSurroundedWithQuotationMarks = surroundWithQuotationMarks[colInx];
}
delimitedLine.SetColumnData(colInx, row[colInx].ToString());
}
returnResultAsString(delimitedLine.OutputColumnData(), tableNumber);
}
}
}//end ExtractDelimitedtDataFromTable method
private void ExportToTextFile(string colDelimiter, string eol)
{
PFDataDelimitersPrompt delimitersPrompt = new PFDataDelimitersPrompt();
string fileName = string.Empty;
string columnDelimiter = string.Empty;
string lineTerminator = string.Empty;
PFTextFile outputFile = null;
PFDelimitedDataLine dataLine = null;
try
{
if (columnDelimiter == string.Empty && eol == string.Empty)
{
if (delimitersPrompt.ShowDialog() != DialogResult.OK)
{
delimitersPrompt.Close();
return;
}
else
{
columnDelimiter = delimitersPrompt.ColumnDelimiter;
lineTerminator = delimitersPrompt.LineTerminator;
delimitersPrompt.Close();
}
}
else
{
columnDelimiter = colDelimiter;
lineTerminator = eol;
}
if (ShowSaveFileDialog() != DialogResult.OK)
{
return;
}
fileName = _saveFileDialog.FileName;
outputFile = new PFTextFile(fileName, PFFileOpenOperation.OpenFileForWrite);
dataLine = new PFDelimitedDataLine(2);
dataLine.ColumnSeparator = columnDelimiter;
dataLine.LineTerminator = lineTerminator;
for (int i = 0; i < this.dataGridAppSettings.Rows.Count; i++)
{
DataGridViewRow row = this.dataGridAppSettings.Rows[i];
for (int j = 0; j < row.Cells.Count; j++)
{
//excelApp.Cells[i + 1, j + 1] = row.Cells[j].Value.ToString();
dataLine.SetColumnData(j, row.Cells[j].Value.ToString());
}
outputFile.WriteData(dataLine.OutputColumnData());
}
outputFile.CloseFile();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToErrorLog);
}
finally
{
;
}
}
}//end method
public static void DelimitedExtractFileTest(MainForm frm)
{
PFSQLServer sqlserv = new PFSQLServer();
string connectionString = string.Empty;
string sqlQuery = "select GeographyKey, City, StateProvinceName as StateName, EnglishCountryRegionName as CountryName, PostalCode from dbo.DimGeography;";
PFTextFile extractFile = new PFTextFile(@"c:\temp\ExtractFileTest.txt", PFFileOpenOperation.OpenFileForWrite);
Stopwatch watch = new Stopwatch();
try
{
watch.Start();
sqlserv.ServerName = "PROFASTSV2";
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();
PFDelimitedDataLine line = new PFDelimitedDataLine(schemaTable.Rows.Count);
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
line.SetColumnDefinition(i, schemaTable.Rows[i]["ColumnName"].ToString());
}
extractFile.WriteData(line.OutputColumnNames());
//foreach (DataRow row in schemaTable.Rows)
//{
// Program._messageLog.WriteLine(row["ColumnName"].ToString());
// //For each property of the field...
// //foreach (DataColumn prop in schemaTable.Columns)
// //{
// // //Display the field name and value.
// // Program._messageLog.WriteLine(prop.ColumnName + " = " + fld[prop].ToString());
// //}
//}
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());
//if (rdr.HasRows)
//{
// int colInx = 0;
// int maxColInx = -1;
// while (rdr.Read())
// {
// _msg.Length = 0;
// maxColInx = rdr.FieldCount - 1;
// for (colInx = 0; colInx <= maxColInx; colInx++)
// {
// _msg.Append(rdr.GetName(colInx));
// _msg.Append(": ");
// _msg.Append(rdr[colInx].ToString());
// if (colInx < maxColInx)
// _msg.Append(", ");
// }
// 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());
}
}
