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
- DataSet: a collection of tables with relations between them, DataSet.Relations. A relation is what makes parentRow.GetChildRows(relation) work, and it is the closest thing a DataSet has to a join.
- DataView: a sorted, filtered window on one table. Sort = "total DESC" and RowFilter = "paid = false" are strings, and the view is what a grid can be bound to instead of the table.
- DataTable.DefaultView: the table's own view, which is what the grid uses when it is bound to the table.
- AcceptChanges() and RejectChanges(): the change log again. A row has an original and a current version, and rejecting restores the original.
- DataTable.Load(reader): fills a table from any IDataReader, which is how a database query arrives in a grid.
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
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
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
- Add a line with a doubled quote inside a quoted field and run the program. What do the fields print?
- Add a line with three fields and explain why Count differs from the header.
- 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.
-
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.
-
What does DataTable.PrimaryKey enable?
Answer
A typed lookup with Rows.Find
Without it the table works and the lookup scans.
-
What is a DataView?
Answer
A sorted and filtered window on one table
Sort and RowFilter are the two properties, both strings.
-
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.
-
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.
-
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.
-
Which call serialises a list of records to JSON?
Answer
JsonSerializer.Serialize
Deserialize is the other direction.
-
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.
-
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.
-
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