To convert an ADO.NET DataTable to CSV, a strongly-typed List<T>, or a JSON string in .NET 9, iterate the table's columns and rows with a StringBuilder for CSV, project each DataRow with AsEnumerable() and Field<T>() for a list, and build a List<Dictionary<string, object?>> that you hand to System.Text.Json. This article gives you three reusable extension methods — ToCsv, ToList<T>, and ToJson — that work on any DataTable and handle the tricky edge cases (embedded commas, quotes, newlines, and DBNull) correctly.
DataTable is still the natural result type when you call SqlDataAdapter.Fill, read a stored procedure with multiple result shapes, or accept tabular data whose schema you don't know at compile time. The problem is that a DataTable doesn't serialize or export on its own. Below we solve that once, in a small static class you can drop into any .NET 9 project.
Each target format serves a different need. CSV is what a business user opens in Excel or hands to a legacy import job. A List<T> is what the rest of your C# code actually wants to work with — LINQ queries, validation, mapping to a view model. JSON is what you return from an API or write to a message queue. Because all three start from the same DataTable, it pays to write the conversions once as extension methods rather than sprinkling ad-hoc loops through your codebase. The three methods in this article total under sixty lines and have no dependencies beyond the framework.
Set up a sample DataTable
Every example uses the same table so you can see the input and the output side by side.
static DataTable BuildSample()
{
var table = new DataTable("Employees");
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Department", typeof(string));
table.Columns.Add("Salary", typeof(decimal));
table.Rows.Add(1, "Ava Stone", "Engineering", 92000m);
table.Rows.Add(2, "Liam \"LJ\" Ford", "Sales, EMEA", 68000m);
table.Rows.Add(3, "Noah Reed", DBNull.Value, 74000m);
return table;
}
Row 2 deliberately contains a quote and a comma, and row 3 has a DBNull department, so every converter has to prove it handles real data. In a real application this table would arrive from SqlDataAdapter.Fill or a DataReader; the conversions that follow do not care where it came from, which is the whole point of putting them on DataTable itself.
Convert a DataTable to CSV
CSV looks trivial until a field contains a comma, a double quote, or a line break. RFC 4180 says any such field must be wrapped in double quotes, and any double quote inside it must be doubled (" becomes ""). Skip that and one bad value shifts every column after it.
public static string ToCsv(this DataTable table, string separator = ",")
{
var sb = new StringBuilder();
sb.AppendLine(string.Join(separator,
table.Columns.Cast<DataColumn>()
.Select(c => Escape(c.ColumnName, separator))));
foreach (DataRow row in table.Rows)
{
sb.AppendLine(string.Join(separator,
row.ItemArray.Select(f => Escape(f, separator))));
}
return sb.ToString();
static string Escape(object? field, string separator)
{
var value = field is null or DBNull ? string.Empty : field.ToString() ?? "";
var mustQuote = value.Contains(separator) || value.Contains('"')
|| value.Contains('\n') || value.Contains('\r');
if (mustQuote)
value = "\"" + value.Replace("\"", "\"\"") + "\"";
return value;
}
}
The local Escape function treats DBNull as an empty cell and quotes only when needed. Running it against the sample produces:
Id,Name,Department,Salary
1,Ava Stone,Engineering,92000
2,"Liam ""LJ"" Ford","Sales, EMEA",68000
3,Noah Reed,,74000
Notice how Liam "LJ" Ford and Sales, EMEA are wrapped and escaped, while the missing department in row 3 becomes an empty field between two commas.
The separator parameter lets you emit tab-separated or semicolon-separated files (common in European locales where the comma is a decimal mark) without changing the logic. Two refinements worth knowing: AppendLine uses the platform newline, so pass "\r\n" explicitly if you need Windows-style line endings on Linux; and if any field could itself contain your chosen separator, the escaping already covers it because the quoting check tests against that exact separator string.
Convert a DataTable to a strongly-typed List
Once data leaves the untyped DataTable world, you usually want real objects. DataTableExtensions.AsEnumerable() (from System.Data.DataSetExtensions) gives you an IEnumerable<DataRow>, and DataRowExtensions.Field<T>() reads a column with proper DBNull handling — it maps DBNull to null for reference types and to Nullable<T> cleanly.
public static List<T> ToList<T>(this DataTable table, Func<DataRow, T> map)
=> table.AsEnumerable().Select(map).ToList();
You supply the mapping so the compiler checks every column name and type. Define a record and project into it:
record Employee(int Id, string Name, string? Department, decimal Salary);
var employees = table.ToList(r => new Employee(
r.Field<int>("Id"),
r.Field<string>("Name")!,
r.Field<string?>("Department"), // DBNull becomes null
r.Field<decimal>("Salary")));
Field<string?>("Department") returns null for row 3 instead of throwing, which is exactly why you prefer it over (string)row["Department"]. A direct cast on a DBNull value throws an InvalidCastException, and row["Department"].ToString() silently gives you an empty string that hides the fact the value was missing. Field<T>() also performs the numeric and date conversions you expect, so Field<int>("Id") works even when the column was loaded as a wider type.
If your column names line up with property names, you can generalize the mapping with reflection, but an explicit map delegate is faster, allocation-free, and — more importantly — fails at compile time when a column is renamed. For a handful of known result shapes, prefer the explicit projection.
Convert a DataTable to a JSON string
The cleanest modern approach is to reshape the table into a List<Dictionary<string, object?>> — one dictionary per row keyed by column name — and let System.Text.Json serialize it. This keeps you on the built-in, high-performance serializer that ships with .NET 9, with no extra package.
public static string ToJson(this DataTable table, bool indented = true)
{
var rows = new List<Dictionary<string, object?>>(table.Rows.Count);
foreach (DataRow row in table.Rows)
{
var dict = new Dictionary<string, object?>(table.Columns.Count);
foreach (DataColumn col in table.Columns)
{
var value = row[col];
dict[col.ColumnName] = value is DBNull ? null : value;
}
rows.Add(dict);
}
var options = new JsonSerializerOptions
{
WriteIndented = indented,
Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping
};
return JsonSerializer.Serialize(rows, options);
}
WriteIndented = true gives readable output for logs and debugging; set it to false for compact payloads over the wire. The Encoder line is worth understanding: by default System.Text.Json HTML-escapes characters like ", <, and & into \uXXXX sequences, which is safe but noisy. UnsafeRelaxedJsonEscaping keeps the JSON valid while leaving those characters readable — appropriate when the output is not being written straight into an HTML page. The sample serializes to:
[
{
"Id": 1,
"Name": "Ava Stone",
"Department": "Engineering",
"Salary": 92000
},
{
"Id": 2,
"Name": "Liam \"LJ\" Ford",
"Department": "Sales, EMEA",
"Salary": 68000
},
{
"Id": 3,
"Name": "Noah Reed",
"Department": null,
"Salary": 74000
}
]
DBNull becomes a proper JSON null, and System.Text.Json escapes the embedded quote for you.
What about Newtonsoft.Json?
For years the one-liner was JsonConvert.SerializeObject(dataTable) from Newtonsoft.Json, which understands DataTable natively and still works:
string json = Newtonsoft.Json.JsonConvert.SerializeObject(table);
It remains a valid shortcut if the package is already in your project. For new .NET 9 code, prefer System.Text.Json: it is built in, faster, allocates less, and needs no third-party dependency. System.Text.Json cannot serialize a raw DataTable directly — pass one and you get an empty result — which is exactly why the dictionary projection above exists. Those few lines buy you the modern serializer without giving up the convenience, and they give you full control over null handling and property casing that the Newtonsoft shortcut hides. If you are already on System.Text.Json everywhere else, keeping serialization consistent across your app is reason enough to drop the extra dependency.
Key takeaways
- A
DataTableneeds a projection step before it becomes CSV, aList<T>, or JSON — none of these are automatic. - For CSV, always quote fields containing the separator, a double quote, or a newline, and double any inner quotes per RFC 4180.
- Use
AsEnumerable()withField<T>()to map rows to strong types while handlingDBNullsafely. - For JSON, build a
List<Dictionary<string, object?>>and serialize withSystem.Text.Jsonon .NET 9. JsonConvert.SerializeObject(dataTable)is the legacy shortcut;System.Text.Jsonis preferred for new code.- Package the conversions as reusable extension methods so any
DataTablegets them for free.
Frequently asked questions
Why can't System.Text.Json serialize a DataTable directly?
System.Text.Json has no built-in converter for DataTable or DataRow, so it produces empty or unusable output. Reshaping the table into a List<Dictionary<string, object?>> (or List<T>) gives the serializer plain objects it understands.
How do I handle DBNull when converting a DataTable?
Check value is DBNull and substitute null (for JSON) or an empty string (for CSV). When mapping to objects, DataRow.Field<T>() converts DBNull to null for nullable and reference types automatically.
Is Newtonsoft.Json still okay for DataTable serialization?
Yes. JsonConvert.SerializeObject(dataTable) still works and is convenient if Newtonsoft is already referenced. For new .NET 9 projects, System.Text.Json is preferred because it is built in, faster, and dependency-free.
How do I export the CSV to a file?
Call File.WriteAllText("employees.csv", table.ToCsv()). For very large tables, write row by row to a StreamWriter instead of building the whole string in memory first.
Comments (0)
No comments yet — be the first to share your thoughts.