Most efficient way of converting a DataTable to CSV
Use a System.Text.StringBuilder for huge strings - that's pretty fast. I implemented this one:
public static string DataTableToCSV(this DataTable datatable, char seperator)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < datatable.Columns.Count; i++)
{
sb.Append(datatable.Columns[i]);
if (i < datatable.Columns.Count - 1)
sb.Append(seperator);
}
sb.AppendLine();
foreach (DataRow dr in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
sb.Append(dr[i].ToString());
if (i < datatable.Columns.Count - 1)
sb.Append(seperator);
}
sb.AppendLine();
}
return sb.ToString();
}
Here's a method I have in my Utility class. Works well for what I'm doing.
public static void GenerateCSV(DataTable dt)
{
StringBuilder sb = new StringBuilder();
try
{
int count = 1;
int totalColumns = dt.Columns.Count;
foreach (DataColumn dr in dt.Columns)
{
sb.Append(dr.ColumnName);
if (count != totalColumns)
{
sb.Append(",");
}
count++;
}
sb.AppendLine();
string value = String.Empty;
foreach (DataRow dr in dt.Rows)
{
for (int x = 0; x < totalColumns; x++)
{
value = dr[x].ToString();
if (value.Contains(",") || value.Contains("\""))
{
value = '"' + value.Replace("\"", "\"\"") + '"';
}
sb.Append(value);
if (x != (totalColumns - 1))
{
sb.Append(",");
}
}
sb.AppendLine();
}
}
catch (Exception ex)
{
// Do something
}
}