Updated on

A DataTable in C# is a table that lives in memory. We define its columns, each with a name and a type, add rows that hold the values, then filter, copy or print those rows with no database involved.

To download the source code for this article, you can visit our GitHub repository.

DataTable is a data structure that is a member of the System.Data namespace. It consists of columns that define the data type and rows that hold individual records, making it ideal for structured data sets. Good use cases include reading information from a relational database or files organized in columns and rows. Microsoft’s ADO.NET documentation describes it as something that “represents one table of in-memory relational data”.

How Do We Create a DataTable in C#?

Creating a DataTable takes two steps. First, we declare and instantiate the table with new DataTable(). Then we define its structure by calling Add() on the Columns property, once for every column we need.

Each Add() call creates a DataColumn. The first argument becomes its ColumnName, and the second, a Type such as typeof(string) or typeof(DateTime), becomes its DataType. If we leave the type out, the column stores strings.

Every column gets a zero-based Ordinal in the order we add it, so later we can read a value by name or by position.

Once the columns exist, the table is ready for data. We ask it for an empty row with NewRow(), fill that row by column name or by index, and hand it back with Rows.Add(). For short rows, Rows.Add() also accepts the values directly, in column order.

Let’s jump in with an example.

First, let’s declare and instantiate dt, a DataTable:

using System.Data;

var dt = new DataTable();

Next, let’s define the structure of our table:

dt.Columns.Add("Ticker", typeof(string));
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("Price", typeof(double));

Calling the Add() method creates a new DataColumn object and assigns a reference to it in the Columns property of dt, our DataTable.  Passing Ticker, Date and Price as string parameters defines the ColumnName property of the new columns. We use the typeof operator to explicitly define the DataType property of our DataColumn objects.

Populate a DataTable in C#

Now that we’ve created and defined our DataTable, let’s add some data. There are three steps to adding a record:

  • Declaring and instantiating a DataRow object by calling the NewRow() method on the DataTable
  • Populating the DataRow by setting each column to the data we want to populate
  • Calling the Add() method on the Rows property to add the new DataRow to our DataTable

Let’s apply these steps by adding a record for Microsoft’s stock price.

First, let’s create a DataRow object:

DataRow row = dt.NewRow();

Next, let’s update row by referencing each column to insert the data we want to add:

row["Ticker"] = "MSFT";
row["Date"] = new DateTime(2023, 3, 3);
row["Price"] = 255.29;

Here, we use the ColumnName property of each DataColumn object to populate the data. However, we could populate the columns by index:

row[0] = "MSFT";
row[1] = new DateTime(2023, 3, 3);
row[2] = 255.29;

The Ordinal property of the DataColumn object assigns a zero-based int index to each column in the order it’s added. Referencing 0, 1 and 2 has identical behavior to referencing Ticker, Date and Price. We’re just referencing different properties of the same object.

Finally, we add our populated row to the table:

dt.Rows.Add(row);

Until we call Rows.Add(), the row is detached. NewRow() gives it our columns, but the table doesn’t hold it yet.

Common DataTable Properties

In addition to the Columns and Rows properties, DataTable has a couple of common properties that allow us to apply relational data modeling concepts in memory in C#.

Primary Key

A primary key is defining one or more columns as a unique identifier for records in a table. We can use the PrimaryKey property to define this in C# with a DataColumn[] object.

Building off our example, let’s create a new column and assign it as the primary key of our table:

var pkCol = new DataColumn("Key", typeof(int));
dt.Columns.Add(pkCol);

dt.Rows[0]["Key"] = 0;

dt.PrimaryKey = [pkCol];

First, we’re defining pkCol, a variable referencing Key, a new DataColumn we’re adding to our table. Next, we give our MSFT row a key of 0. Finally, we set the PrimaryKey property. It takes a DataColumn[], and the collection expression [pkCol] builds that array with our one key column in it.

Why give the row a key first? Our MSFT row was already in the table when we added the column, so its Key cell is empty. A primary key can’t hold empty values, so setting PrimaryKey before we fill the cell throws a DataException with the message “Column ‘Key’ has null values in it.”

Constraints

Constraints allow us to set rules to limit what can be inserted into a table. This is beneficial as it guarantees reliability in tables and relationships between multiple tables. In C#, the Constraints property references a ConstraintCollection object that stores defined constraints.

Let’s look at what constraints exist on our table:

foreach (Constraint constraint in dt.Constraints)
{
    Console.WriteLine(constraint.ConstraintName);
    Console.WriteLine(constraint.GetType());
}

Here, we loop through each item in our table’s ConstraintCollection and print the ConstraintName and type.

Let’s run the code and view the output:

Constraint1
System.Data.UniqueConstraint

Constraint1 is our only Constraint object in our Constraints property. It is a UniqueConstraint that was automatically created by defining the PrimaryKey property for our table. It prevents adding duplicates to our Key column, ensuring integrity for the unique identifier of our table.

If we add another row with a Key of 0, Rows.Add() throws a ConstraintException telling us that Key is constrained to be unique and the value 0 is already present.

Common Methods of DataTable Class in C#

DataTable has several common methods that make wrangling structured data sets easy.

Clone

The Clone() method takes a copy of the schema and columns but does not copy any of the actual data.

Let’s clone dt as an example:

DataTable dtClone = dt.Clone();

Next, let’s print out the columns. First, we’ll create a helper method to do this. Like every helper method from here on, it goes below our statements in Program.cs, inside partial class Program { }:

public static void PrintColumns(DataTable dt)
{
    foreach (DataColumn col in dt.Columns)
        Console.WriteLine(col.ColumnName);
}

Then, we’ll call it on our cloned table:

PrintColumns(dtClone);

The output shows the identical column structure:

Ticker
Date
Price
Key

Copy

The Copy() method copies a table’s underlying structure and the actual content of the table.

Let’s try it on dt. First, we’ll create a helper method to print out the content of a DataTable object:

public static void PrintData(DataTable dt)
{
    foreach (DataRow row in dt.Rows)
    {
        foreach(DataColumn col in dt.Columns)
        {
            Console.WriteLine($"{col}: {row[col]}");
        }
    }
}

Next, we’ll copy our table, and print out the contents:

DataTable dtCopy = dt.Copy();
PrintData(dtCopy);

The output shows identical data to what we’ve added in dt:

Ticker: MSFT
Date: 3/3/2023 12:00:00 AM
Price: 255.29
Key: 0

Select

The Select() method allows us to easily access data rows. We can pass a string argument with an expression to filter records. Let’s say we add an additional record to dt:

row = dt.NewRow();

row["Ticker"] = "AMZN";
row["Date"] = new DateTime(2023, 3, 6);
row["Price"] = 93.75;
row["Key"] = 1;

dt.Rows.Add(row);

We could also add the same record in one line. Rows.Add() has an overload that takes the values in column order and builds the row for us:

dt.Rows.Add("AMZN", new DateTime(2023, 3, 6), 93.75, 1);

It’s handy for short rows. But it depends on the column order, so once a table has more than a few columns, the named version above is easier to read and harder to get wrong.

Now, let’s access our new record. We can do this by writing an expression that filters the table by the Ticker:

string expression = "Ticker='AMZN'";
DataRow[] result = dt.Select(expression);

Next, let’s write a helper method to print out the results:

public static void PrintSelectedRows(DataRow[] rows, DataTable dt)
{
    foreach (DataRow dr in rows)
    {
        foreach (DataColumn col in dt.Columns)
        {
            Console.WriteLine($"{col}: {dr[col]}");
        }
    }
}

Finally, we can call this method to view the output of our Select() method call:

PrintSelectedRows(result, dt);

Which prints out the expected result of the DataRow objects that have a Ticker value of AMZN:

Ticker: AMZN
Date: 3/6/2023 12:00:00 AM
Price: 93.75
Key: 1

How Do We Print a DataTable in C#?

DataTable has no method that prints its contents as a table. Even ToString() returns only the table’s name, so printing one means walking its rows and columns ourselves.

It takes two nested loops: the outer one goes over dt.Rows, the inner one over dt.Columns, and row[col] gives us each value as an object. That’s what our PrintData() helper does, one Column: value line per cell.

For more than a couple of rows, one line per row reads better. Every DataRow exposes its values through the ItemArray property, in column order, so string.Join() turns a whole row into a single line. Printing the column names first gives us a header.

Dates and numbers print in the current culture, so 3/3/2023 12:00:00 AM on an en-US machine looks different on a German one. An empty cell holds DBNull.Value, which prints as nothing at all, not as the word null.

Let’s write a helper that prints a header line and then one line per row:

public static void PrintTable(DataTable dt)
{
    var header = dt.Columns.Cast<DataColumn>().Select(col => col.ColumnName);
    Console.WriteLine(string.Join(" | ", header));

    foreach (DataRow row in dt.Rows)
        Console.WriteLine(string.Join(" | ", row.ItemArray));
}

DataColumnCollection predates generics, so Cast<DataColumn>() gives us something LINQ can work with. When we call PrintTable(dt) on our table with both records, it prints:

Ticker | Date | Price | Key
MSFT | 3/3/2023 12:00:00 AM | 255.29 | 0
AMZN | 3/6/2023 12:00:00 AM | 93.75 | 1

For anything wider than a handful of columns, the console stops being a good place to look. At that point we bind the table to a grid, or convert the table to JSON and read it in a viewer.

Practical Example With DataTable Class in C#

Let’s take everything we’ve learned and apply it to an example. A common scenario is loading data into a program from a .csv file. A .csv file is structured so columns are separated by a comma, and rows are separated by a new line.

First, let’s create a method to load a file into a DataTable. It uses TextFieldParser from the Microsoft.VisualBasic.FileIO namespace, so we add using Microsoft.VisualBasic.FileIO; at the top of Program.cs:

public static DataTable CsvToDataTable(string filePath)
{
    var dtRes = new DataTable();
    using (var parser = new TextFieldParser(filePath))
    {
        parser.TextFieldType = FieldType.Delimited;
        parser.TrimWhiteSpace = true;
        parser.SetDelimiters(",");

        var columns = parser.ReadFields() ?? [];

        foreach (var col in columns)
            dtRes.Columns.Add(col);

        while (!parser.EndOfData)
        {
            var row = parser.ReadFields() ?? [];
            dtRes.LoadDataRow(row, true);
        }
    }
    return dtRes;
}

Here, we use parser, a TextFieldParser object that has built-in methods for reading structured text files to read a .csv file. We then use the LoadDataRow() method to read content from a .csv into dtRes, the DataTable that is returned with the file contents.

ReadFields() returns null once there is nothing left to read, so ?? [] keeps the compiler’s nullable checks quiet.

We called Columns.Add(col) with only a name, so every column in dtRes is a string column and every value arrives as text. Our carrot filter below works because it compares text with text. A numeric filter is fragile: the expression converts each text value to the type of the number it compares with, so on our file Select("Price > 1") throws an EvaluateException as soon as it reaches 0.75, which is not a whole number. When we need numeric filters, we pass a type to Columns.Add() for those columns.

We cover converting JSON into a DataTable in a separate article.

Next, let’s write a method to load vegetable-sales.csv into our program. We take the file from the sample repository, add it to the project and set it to copy to the output directory, because the method looks for it next to the compiled program:

public static void ProcessSales()
{
    var filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "vegetable-sales.csv");
    DataTable veggieSales = CsvToDataTable(filePath);
}

Now that we’ve loaded the file into a table, let’s apply a filter to analyze sales for carrots:

DataRow[] carrotSales = veggieSales.Select("Vegetable='Carrot'");

Finally, we can view the output using our PrintSelectedRows() method from our previous example:

Program.PrintSelectedRows(carrotSales, veggieSales);

Which prints out the records for carrot sales to the console:

Vegetable: Carrot
Quantity: 1
Price: 1
Sales: 1
Vegetable: Carrot
Quantity: 1
Price: 3
Sales: 3

When Should We Use a DataTable Instead of a Typed Class?

We reach for a DataTable when we only learn the shape of the data at run time, and for a typed class when we know it while writing the code.

A CSV file with whatever columns a user uploads, the result of an ad hoc SQL query, or a report whose columns come from configuration all fit a DataTable well. So does code that talks to APIs built around it: SqlDataAdapter.Fill(), SqlBulkCopy.WriteToServer() and table-valued parameters in SQL Server all accept one.

When the columns are fixed, a class or a record serves us better. The compiler checks property names and types. A DataTable finds the typo in row["Pirce"] only when that line runs, and every value comes back as an object we have to cast.

If the goal is reading and writing a database through our own types, EF Core or Dapper does that mapping.

When a DataTable does come our way, we don’t have to stay with string expressions. AsEnumerable() turns its rows into an IEnumerable<DataRow>, and Field<T>() reads a value with its real type:

public static IEnumerable<string?> GetTickersBelow(DataTable dt, double price)
{
    return dt.AsEnumerable()
        .Where(row => row.Field<double>("Price") < price)
        .Select(row => row.Field<string>("Ticker"));
}

Here, Field<double>() hands us a double, so the comparison happens on numbers without a cast, and the rest is an ordinary LINQ query. On our table, GetTickersBelow(dt, 100) returns AMZN.

For data with fixed columns, though, we’d still pick a class or a record for the rows, and EF Core or Dapper for the database mapping.

Conclusion

DataTable class in C# is a good choice for storing and manipulating structured data sets whose columns we only know at run time. It has built-in methods to easily define columns and populate rows with data. Properties like PrimaryKey and Constraints enable the application of data modeling best practices. Methods like Copy(), Clone() and Select() allow easy data transformation in memory.

We’ve also seen how to print a table, and why a typed class is the better fit once we know the columns while writing the code.

Tested with .NET 10 (SDK 10.0.302) and xUnit 2.9.3.