I'm using Visual Studio 2005 and have a DataTable with two columns and some rows that I want to output to the console. I hoped there would be something like:
DataTable results = MyMethod.GetResults();
Console.WriteLine (results.ToString());
What's the best way (i.e. least amount of coding from me) to convert a simple DataTable to a string?
public static string DataTable2String(DataTable dataTable)
{
StringBuilder sb = new StringBuilder();
if (dataTable != null)
{
string seperator = " | ";
#region get min length for columns
Hashtable hash = new Hashtable();
foreach (DataColumn col in dataTable.Columns)
hash[col.ColumnName] = col.ColumnName.Length;
foreach (DataRow row in dataTable.Rows)
for (int i = 0; i < row.ItemArray.Length; i++)
if (row[i] != null)
if (((string)row[i]).Length > (int)hash[dataTable.Columns[i].ColumnName])
hash[dataTable.Columns[i].ColumnName] = ((string)row[i]).Length;
int rowLength = (hash.Values.Count + 1) * seperator.Length;
foreach (object o in hash.Values)
rowLength += (int)o;
#endregion get min length for columns
sb.Append(new string('=', (rowLength - " DataTable ".Length) / 2));
sb.Append(" DataTable ");
sb.AppendLine(new string('=', (rowLength - " DataTable ".Length) / 2));
if (!string.IsNullOrEmpty(dataTable.TableName))
sb.AppendLine(String.Format("{0,-" + rowLength + "}", String.Format("{0," + ((rowLength + dataTable.TableName.Length) / 2).ToString() + "}", dataTable.TableName)));
#region write values
foreach (DataColumn col in dataTable.Columns)
sb.Append(seperator + String.Format("{0,-" + hash[col.ColumnName] + "}", col.ColumnName));
sb.AppendLine(seperator);
sb.AppendLine(new string('-', rowLength));
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < row.ItemArray.Length; i++)
{
sb.Append(seperator + String.Format("{0," + hash[dataTable.Columns[i].ColumnName] + "}", row[i]));
if (i == row.ItemArray.Length - 1)
sb.AppendLine(seperator);
}
}
#endregion write values
sb.AppendLine(new string('=', rowLength));
}
else
sb.AppendLine("================ DataTable is NULL ================");
return sb.ToString();
}
output:
======================= DataTable =======================
MyTable
| COL1 | COL2 | COL3 1000000ng name |
----------------------------------------------------------
| 1 | 2 | 3 |
| abc | Dienstag, 12. März 2013 | xyz |
| Have | a nice | day! |
==========================================================
Prerequisite
using System.Linq;
then ...
string res = string.Join(Environment.NewLine,
results.Rows.OfType<DataRow>().Select(x => string.Join(" ; ", x.ItemArray)));
two for loops, one for rows, another for columns, output dataRow(i).Value. Watch out for nulls and DbNulls.
I would install PowerShell. It understands .NET objects and has an Format-Table and Export-Csv that would do exactly what you are looking for. If you do any sort of console work it is a great complement/replacement to C# console apps.
When I started using it, I rewrote my console apps as libraries and import the libraries into Powershell. The built-in commandlets make console work so nice.
If you have a single column in datatable than it's simple to change datatable to string.
DataTable results = MyMethod.GetResults();
if(results != null && results.Rows.Count > 0) // Check datatable is null or not
{
List<string> lstring = new List<string>();
foreach(DataRow dataRow in dt.Rows)
{
lstring.Add(Convert.ToString(dataRow["ColumnName"]));
}
string mainresult = string.Join(",", lstring.ToArray()); // You can Use comma(,) or anything which you want. who connect the two string. You may leave space also.
}
Console.WriteLine (mainresult);
I created my variant of class for your needs. I believe it is a bit more configurable than already provided variants.
You can use it with all default settings just create an instance of a class and call StringifyDataTable method, or you can set additional options if needed.
public class DataTableStringifier
{
public bool IsOuterBordersPresent { get; set; } //Whether outer borders of table needed
public bool IsHeaderHorizontalSeparatorPresent { get; set; } // Whether horizontal line separator between table title and data is needed. Useful to set 'false' if you expect only 1 or 2 rows of data - no need for additional lines then
public char ValueSeparator { get; set; } //Vertical line character
public char HorizontalLinePadChar { get; set; } // Horizontal line character
public char HorizontalLineSeparator { get; set; } // Horizontal border (between header and data) column separator (crossing of horizontal and vertical borders)
public int ValueMargin { get; set; } // Horizontal margin from table borders (inner and outer) to cell values
public int MaxColumnWidth { get; set; } // To avoid too wide columns with thousands of characters. Longer values will be cropped in the center
public string LongValuesEllipses { get; set; } // Cropped values wil be inserted this string in the middle to mark the point of cropping
public DataTableStringifier()
{
MaxColumnWidth = int.MaxValue;
IsHeaderHorizontalSeparatorPresent = true;
ValueSeparator = '|';
ValueMargin = 1;
HorizontalLinePadChar = '-';
HorizontalLineSeparator = '+';
LongValuesEllipses = "...";
IsOuterBordersPresent = false;
}
public string StringifyDataTable(DataTable table)
{
int colCount = table.Columns.Count;
int rowCount = table.Rows.Count;
string[] colHeaders = new string[colCount];
string[,] cells = new string[rowCount, colCount];
int[] colWidth = new int[colCount];
for (int i = 0; i < colCount; i++)
{
var column = table.Columns[i];
var colName = ValueToLimitedLengthString(column.ColumnName);
colHeaders[i] = colName;
if (colWidth[i] < colName.Length)
{
colWidth[i] = colName.Length;
}
}
for (int i = 0; i < rowCount; i++)
{
DataRow row = table.Rows[i];
for (int j = 0; j < colCount; j++)
{
var valStr = ValueToLimitedLengthString(row[j]);
cells[i, j] = valStr;
if (colWidth[j] < valStr.Length)
{
colWidth[j] = valStr.Length;
}
}
}
string valueSeparatorWithMargin = string.Concat(new string(' ', ValueMargin), ValueSeparator, new string(' ', ValueMargin));
string leftBorder = IsOuterBordersPresent ? string.Concat(ValueSeparator, new string(' ', ValueMargin)) : "";
string rightBorder = IsOuterBordersPresent ? string.Concat(new string(' ', ValueMargin), ValueSeparator) : "";
string horizLine = new string(HorizontalLinePadChar, colWidth.Sum() + (colCount - 1)*(ValueMargin*2 + 1) + (IsOuterBordersPresent ? (ValueMargin + 1)*2 : 0));
StringBuilder tableBuilder = new StringBuilder();
if (IsOuterBordersPresent)
{
tableBuilder.AppendLine(horizLine);
}
tableBuilder.Append(leftBorder);
for (int i = 0; i < colCount; i++)
{
tableBuilder.Append(colHeaders[i].PadRight(colWidth[i]));
if (i < colCount - 1)
{
tableBuilder.Append(valueSeparatorWithMargin);
}
}
tableBuilder.AppendLine(rightBorder);
if (IsHeaderHorizontalSeparatorPresent)
{
if (IsOuterBordersPresent)
{
tableBuilder.Append(ValueSeparator);
tableBuilder.Append(HorizontalLinePadChar, ValueMargin);
}
for (int i = 0; i < colCount; i++)
{
tableBuilder.Append(new string(HorizontalLinePadChar, colWidth[i]));
if (i < colCount - 1)
{
tableBuilder.Append(HorizontalLinePadChar, ValueMargin);
tableBuilder.Append(HorizontalLineSeparator);
tableBuilder.Append(HorizontalLinePadChar, ValueMargin);
}
}
if (IsOuterBordersPresent)
{
tableBuilder.Append(HorizontalLinePadChar, ValueMargin);
tableBuilder.Append(ValueSeparator);
}
tableBuilder.AppendLine();
}
for (int i = 0; i < rowCount; i++)
{
tableBuilder.Append(leftBorder);
for(int j=0; j<colCount; j++)
{
tableBuilder.Append(cells[i, j].PadRight(colWidth[j]));
if(j<colCount-1)
{
tableBuilder.Append(valueSeparatorWithMargin);
}
}
tableBuilder.AppendLine(rightBorder);
}
if (IsOuterBordersPresent)
{
tableBuilder.AppendLine(horizLine);
}
return tableBuilder.ToString(0, tableBuilder.Length - 1); //Trim last enter char
}
private string ValueToLimitedLengthString(object value)
{
string strValue = value.ToString();
if (strValue.Length > MaxColumnWidth)
{
int beginningLength = (MaxColumnWidth) / 2;
int endingLength = (MaxColumnWidth + 1) / 2 - LongValuesEllipses.Length;
return string.Concat(strValue.Substring(0, beginningLength), LongValuesEllipses, strValue.Substring(strValue.Length - endingLength, endingLength));
}
else
{
return strValue;
}
}
}
using(var writer = new StringWriter()) {
results.WriteXml(writer);
Console.WriteLine(writer.ToString());
}
Of course the usefulness of this depends on how important the formatting is. If it's just a debug dump, I find XML outputs like this very readable. However, if the formatting is important to you, then you have no choice but to write your own method to do it.
i know i'm years late xD but Here's how i did it
public static string convertDataTableToString(DataTable dataTable)
{
string data = string.Empty;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
for (int j = 0; j < dataTable.Columns.Count; j++)
{
data += dataTable.Columns[j].ColumnName + "~" + row[j];
if (j == dataTable.Columns.Count - 1)
{
if (i != (dataTable.Rows.Count - 1))
data += "$";
}
else
data += "|";
}
}
return data;
}
If someone ever optimizes this please let me know
i tried this :
public static string convertDataTableToString(DataTable dataTable)
{
string data = string.Empty;
int rowsCount = dataTable.Rows.Count;
for (int i = 0; i < rowsCount; i++)
{
DataRow row = dataTable.Rows[i];
int columnsCount = dataTable.Columns.Count;
for (int j = 0; j < columnsCount; j++)
{
data += dataTable.Columns[j].ColumnName + "~" + row[j];
if (j == columnsCount - 1)
{
if (i != (rowsCount - 1))
data += "$";
}
else
data += "|";
}
}
return data;
}
but this answer says it's worse
Late but this is what I use
public static string ConvertDataTableToString(DataTable dataTable)
{
var output = new StringBuilder();
var columnsWidths = new int[dataTable.Columns.Count];
// Get column widths
foreach (DataRow row in dataTable.Rows)
{
for(int i = 0; i < dataTable.Columns.Count; i++)
{
var length = row[i].ToString().Length;
if (columnsWidths[i] < length)
columnsWidths[i] = length;
}
}
// Get Column Titles
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var length = dataTable.Columns[i].ColumnName.Length;
if (columnsWidths[i] < length)
columnsWidths[i] = length;
}
// Write Column titles
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var text = dataTable.Columns[i].ColumnName;
output.Append("|" + PadCenter(text, columnsWidths[i] + 2));
}
output.Append("|\n" + new string('=', output.Length) + "\n");
// Write Rows
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var text = row[i].ToString();
output.Append("|" + PadCenter(text,columnsWidths[i] + 2));
}
output.Append("|\n");
}
return output.ToString();
}
private static string PadCenter(string text, int maxLength)
{
int diff = maxLength - text.Length;
return new string(' ', diff/2) + text + new string(' ', (int) (diff / 2.0 + 0.5));
}
very vague ....
id bung it into a dataset simply so that i can output it easily as xml ....
failing that why not iterate through its row and column collections and output them?
/// <summary>
/// Dumps the passed DataSet obj for debugging as list of html tables
/// </summary>
/// <param name="msg"> the msg attached </param>
/// <param name="ds"> the DataSet object passed for Dumping </param>
/// <returns> the nice looking dump of the DataSet obj in html format</returns>
public static string DumpHtmlDs(string msg, ref System.Data.DataSet ds)
{
StringBuilder objStringBuilder = new StringBuilder();
objStringBuilder.AppendLine("<html><body>");
if (ds == null)
{
objStringBuilder.AppendLine("Null dataset passed ");
objStringBuilder.AppendLine("</html></body>");
WriteIf(objStringBuilder.ToString());
return objStringBuilder.ToString();
}
objStringBuilder.AppendLine("<p>" + msg + " START </p>");
if (ds != null)
{
if (ds.Tables == null)
{
objStringBuilder.AppendLine("ds.Tables == null ");
return objStringBuilder.ToString();
}
foreach (System.Data.DataTable dt in ds.Tables)
{
if (dt == null)
{
objStringBuilder.AppendLine("ds.Tables == null ");
continue;
}
objStringBuilder.AppendLine("<table>");
//objStringBuilder.AppendLine("================= My TableName is " +
//dt.TableName + " ========================= START");
int colNumberInRow = 0;
objStringBuilder.Append("<tr><th>row number</th>");
foreach (System.Data.DataColumn dc in dt.Columns)
{
if (dc == null)
{
objStringBuilder.AppendLine("DataColumn is null ");
continue;
}
objStringBuilder.Append(" <th> |" + colNumberInRow.ToString() + " | ");
objStringBuilder.Append( dc.ColumnName.ToString() + " </th> ");
colNumberInRow++;
} //eof foreach (DataColumn dc in dt.Columns)
objStringBuilder.Append("</tr>");
int rowNum = 0;
foreach (System.Data.DataRow dr in dt.Rows)
{
objStringBuilder.Append("<tr><td> row - | " + rowNum.ToString() + " | </td>");
int colNumber = 0;
foreach (System.Data.DataColumn dc in dt.Columns)
{
objStringBuilder.Append(" <td> |" + colNumber + "|" );
objStringBuilder.Append(dr[dc].ToString() + " </td>");
colNumber++;
} //eof foreach (DataColumn dc in dt.Columns)
rowNum++;
objStringBuilder.AppendLine(" </tr>");
} //eof foreach (DataRow dr in dt.Rows)
objStringBuilder.AppendLine("</table>");
objStringBuilder.AppendLine("<p>" + msg + " END </p>");
} //eof foreach (DataTable dt in ds.Tables)
} //eof if ds !=null
else
{
objStringBuilder.AppendLine("NULL DataSet object passed for debugging !!!");
}
return objStringBuilder.ToString();
}
Or, change the app to WinForms, use grid and bind DataTable to grid. If it is a demo/sample app.
Source: Stackoverflow.com