DataTable, DataSet, CSV and JSON

A DataTable is an in-memory table: named columns with types, rows, a primary key, and a view that sorts and filters without touching the rows. It predates the generic collections by a few years and it is still what a grid, a report and a DataView understand, which is why a WinForms application meets it. This lesson covers what it is, and then the two text formats a program exchanges with the rest of the world: CSV and JSON.

DataTable

using System; using System.Data; using System.Drawing; using System.Windows.Forms; internal static class Program { [STAThread] private static void Main() { ApplicationConfiguration.Initialize(); Application.Run(new Form1()); } } internal sealed class Form1 : Form { public Form1() { Text = "A table"; ClientSize = new Size(460, 240); var table = new DataTable("orders"); table.Columns.Add("id", typeof(string)); table.Columns.Add("total", typeof(decimal)); table.Columns.Add("paid", typeof(bool)); table.PrimaryKey = new[] { table.Columns["id"] }; table.Rows.Add("A-1041", 1250.00m, true); table.Rows.Add("A-1042", 89.50m, false); var grid = new DataGridView { Dock = DockStyle.Fill, DataSource = table, AllowUserToAddRows = false, AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill, }; Controls.Add(grid); } }

dotnet build app.csproj

Build succeeded.
    0 Warning(s)
    0 Error(s)

The listing is built and not run. A DataTable is defined by its columns, and a column has a type: the grid then edits total as a number and paid as a check box without a configuration line. PrimaryKey is what makes Rows.Find("A-1041") work; without it the table still works and the lookup scans.

A table is not a database. It holds everything in memory, it has no query language, and it does not enforce a foreign key unless a DataSet relation says so. What it has is a change log per row (DataRowState, the original and the current values) and the events a bound control needs: RowChanged, ColumnChanged and TableNewRow.

DataSet and DataView

A form that binds a DataGridView to a DataTable gets sorting and a filter for free through the default view; a form that binds to a BindingList does not. That is the trade: the table is heavier and knows about views, the typed list is lighter and needs the two lessons before this one.

CSV

CSV looks trivial and is not: a field may contain the separator, and the rule is that such a field is quoted and a quote inside it is doubled. A parser that splits on commas works until the first address "Lovelace, Ada", and then silently produces one column too many.

The other rules a reader should know: a line ends with \r\n or \n, a quoted field may contain a line break, and a file may start with a byte order mark that ends up in the first column name. A writer quotes a field when it contains a comma, a quote or a newline, and doubles the quotes inside.

JSON

JSON is the format this course uses for everything that is not a table: settings (lesson 043), a saved BindingList, the payload of a capstone. System.Text.Json serialises a list of records in one call:

var json = JsonSerializer.Serialize(rows); var back = JsonSerializer.Deserialize<List<List<string>>>(json);

The C# course covers the options, the naming policy, JsonDocument and the exceptions in its two JSON lessons; the only WinForms-specific part is that a bound list of records is already the shape JSON wants, so a form saves it without a mapping layer.

A CSV parser that runs

The parser is a state machine over characters, and it runs here. It reads three lines of CSV with one quoted field, prints the fields and serialises the result as JSON:

using System; using System.Collections.Generic; using System.Text; using System.Text.Json; var csv = "name,total\n\"Lovelace, Ada\",1250.00\nHopper,340.50"; var rows = Parse(csv); foreach (var row in rows) { Console.WriteLine($"{row.Count} fields: {string.Join(" | ", row)}"); } Console.WriteLine(JsonSerializer.Serialize(rows)); Console.WriteLine($"rows: {rows.Count}, fields in the first: {rows[0].Count}"); static List<List<string>> Parse(string text) { var rows = new List<List<string>>(); var row = new List<string>(); var field = new StringBuilder(); var quoted = false; for (var i = 0; i < text.Length; i++) { var c = text[i]; if (quoted) { if (c == '"' && i + 1 < text.Length && text[i + 1] == '"') { field.Append('"'); i++; } else if (c == '"') { quoted = false; } else { field.Append(c); } } else if (c == '"') { quoted = true; } else if (c == ',') { row.Add(field.ToString()); field.Clear(); } else if (c == '\n') { row.Add(field.ToString()); field.Clear(); rows.Add(row); row = new List<string>(); } else if (c != '\r') { field.Append(c); } } if (field.Length > 0 || row.Count > 0) { row.Add(field.ToString()); rows.Add(row); } return rows; }

dotnet run

2 fields: name | total
2 fields: Lovelace, Ada | 1250.00
2 fields: Hopper | 340.50
[["name","total"],["Lovelace, Ada","1250.00"],["Hopper","340.50"]]
rows: 3, fields in the first: 2

The output is the real run. The quoted field keeps its comma and loses its quotes, which is the one rule the naive split gets wrong; the JSON line is the same data in the other format, and it quotes what it must. The parser treats a doubled quote as one literal quote, skips the carriage return of a Windows line ending, and flushes the last field when the text does not end with a newline — the three details that separate a parser from a split.

Exercises

  1. Add a line with a doubled quote inside a quoted field and run the program. What do the fields print?
  2. Add a line with three fields and explain why Count differs from the header.
  3. Explain from the documentation why DataTable.DefaultView is what a grid binds to.

Questions

A new set of questions in a new order every time. Choose one answer for each, then check.

  1. What defines a DataTable?

    Answer

    Its columns, each with a name and a type

    The column types are what make a grid edit a number as a number.

  2. What does DataTable.PrimaryKey enable?

    Answer

    A typed lookup with Rows.Find

    Without it the table works and the lookup scans.

  3. What is a DataView?

    Answer

    A sorted and filtered window on one table

    Sort and RowFilter are the two properties, both strings.

  4. What does a DataSet add to a DataTable?

    Answer

    Several tables and relations between them

    GetChildRows follows a relation, which is the closest thing to a join.

  5. How is a field containing a comma written in CSV?

    Answer

    Quoted, with the quotes doubled inside

    A parser that splits on commas gets that line wrong.

  6. What does the parser do with two quotes inside a quoted field?

    Answer

    Reads one literal quote

    It is the CSV rule for a quote inside a quoted field.

  7. Which call serialises a list of records to JSON?

    Answer

    JsonSerializer.Serialize

    Deserialize is the other direction.

  8. What did the run print for the second row?

    Answer

    2 fields: Lovelace, Ada | 1250.00

    The quoted field keeps the comma and loses the quotes.

  9. Why is a DataTable heavier than a BindingList of records?

    Answer

    It keeps a change log per row and knows about views

    The trade is views and RowState against weight.

  10. Which detail of a CSV file can end up in the first column name?

    Answer

    A byte order mark at the start of the file

    It is the reason the first column of a file from Excel is sometimes called id.

Every listing on this page was built with the .NET SDK 10.0.303 for net10.0-windows against the targeting pack Microsoft.WindowsDesktop.App.Ref 10.0.15 from the offline feed, in a Docker container on Alpine Linux 3.24.3 (x86_64). The programs were built, not run on Linux: WinForms needs Windows. The logic that draws nothing was also run headless on the same machine, and its output is the run output printed below each of those programs.

Article author: Arthur Isaev