/// <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()); } }