DataTable

This class stores rows and columns of data. In C# we can address parts of the DataTable with DataRow and DataColumn. And a DataSet can contain multiple tables.

The DataTable class is part of the System.Data namespace. We add, select and iterate over stored data. The foreach-loop can be used on the Rows in a DataTable.

First example

A key advantage to DataTable is it allows database-like manipulations of data (even simple joins). But before these advanced features can be used, we must create a table.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Step 1: get the DataTable.
        DataTable table = GetTable();

        // Step 4: print the first cell.
        Console.WriteLine("FIRST ROW, DOSAGE: {0}", table.Rows[0]["Dosage"]);
    }

    static DataTable GetTable()
    {
        // Step 2: here we create a DataTable.
        // ... We add 4 columns, each with a Type.
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Diagnosis", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        // Step 3: here we add rows.
        table.Rows.Add(25, "Drug A", "Disease A", DateTime.Now);
        table.Rows.Add(50, "Drug Z", "Problem Z", DateTime.Now);
        table.Rows.Add(10, "Drug Q", "Disorder Q", DateTime.Now);
        table.Rows.Add(21, "Medicine A", "Diagnosis A", DateTime.Now);
        return table;
    }
}
FIRST ROW, DOSAGE: 25

Rows, field

We build in complexity—here we loop over all Rows of our DataTable. We then access the first field, with the Field extension method, as an int.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // This uses the GetTable method (please paste it in).
        DataTable data = GetTable();

        // ... Loop over all rows.
        foreach (DataRow row in data.Rows)
        {
            // ... Write value of first field as integer.
            Console.WriteLine(row.Field<int>(0));
        }
    }
}
25
50
10
21

DataGridView example

We insert data from object collections (like List) into DataTable. We then render that table to the screen with Windows Forms. DataTable helps display data.

using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        /// <summary>
        /// Contains column names.
        /// </summary>
        List<string> _names = new List<string>();

        /// <summary>
        /// Contains column data arrays.
        /// </summary>
        List<double[]> _dataArray = new List<double[]>();

        public Form1()
        {
            InitializeComponent();

            // Example column.
            _names.Add("Cat");
            // Three numbers of cat data.
            _dataArray.Add(new double[]
            {
                1.0,
                2.2,
                3.4
            });

            // Another example column.
            _names.Add("Dog");
            // Add three numbers of dog data.
            _dataArray.Add(new double[]
            {
                3.3,
                5.0,
                7.0
            });
            // Render the DataGridView.
            dataGridView1.DataSource = GetResultsTable();
        }

        /// <summary>
        /// This method builds a DataTable of the data.
        /// </summary>
        public DataTable GetResultsTable()
        {
            // Create the output table.
            DataTable d = new DataTable();

            // Loop through all process names.
            for (int i = 0; i < this._dataArray.Count; i++)
            {
                // The current process name.
                string name = this._names[i];

                // Add the program name to our columns.
                d.Columns.Add(name);

                // Add all of the memory numbers to an object list.
                List<object> objectNumbers = new List<object>();

                // Put every column's numbers in this List.
                foreach (double number in this._dataArray[i])
                {
                    objectNumbers.Add((object)number);
                }

                // Keep adding rows until we have enough.
                while (d.Rows.Count < objectNumbers.Count)
                {
                    d.Rows.Add();
                }

                // Add each item to the cells in the column.
                for (int a = 0; a < objectNumbers.Count; a++)
                {
                    d.Rows[a][i] = objectNumbers[a];
                }
            }
            return d;
        }
    }
}

Using

DataTable can be placed in a using statement. This can help programs performance. Often the using block construct helps improve resource management.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Safely create and dispose of a DataTable.
        using (DataTable table = new DataTable())
        {
            // Two columns.
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Date", typeof(DateTime));

            // ... Add two rows.
            table.Rows.Add("cat", DateTime.Now);
            table.Rows.Add("dog", DateTime.Today);

            // ... Display first field.
            Console.WriteLine(table.Rows[0].Field<string>(0));
        }
    }
}
cat

Compute

We can use the Compute() method with SUM to sum a column in a DataTable. Here we create a product data table, and add 2 rows to it.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        var table = new DataTable();
        table.Columns.Add("Product", typeof(string));
        table.Columns.Add("Variety1", typeof(decimal));
        table.Columns.Add("Variety2", typeof(decimal));
        table.Columns.Add("Variety3", typeof(decimal));
        table.Columns.Add("Variety4", typeof(decimal));

        table.Rows.Add("Product 1", 10, 12, 14, 45);
        table.Rows.Add("Product 2", 20, 15, 24, 0);

        // Use Compute and SUM to sum up columns.
        // ... Use string.Empty as the filter as it is not needed.
        var sum1 = (decimal)table.Compute("SUM(Variety1)", string.Empty);
        var sum2 = (decimal)table.Compute("SUM(Variety2)", string.Empty);
        var sum3 = (decimal)table.Compute("SUM(Variety3)", string.Empty);
        var sum4 = (decimal)table.Compute("SUM(Variety4)", string.Empty);
        table.Rows.Add("Total", sum1, sum2, sum3, sum4);

        // Loop over rows.
        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine(":: ROW ::");
            foreach (var item in row.ItemArray)
            {
                Console.WriteLine(item);
            }
        }
    }
}
:: ROW ::
Product 1
10
12
14
45
:: ROW ::
Product 2
20
15
24
0
:: ROW ::
Total
30
27
38
45

Merge, PrimaryKey

We can join 2 DataTables together—each resulting row in the DataTable will contain all data from each table for the key. We must set a PrimaryKey column to use Merge.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Create 1 table, setting ID as PrimaryKey.
        DataTable tableStyle = new DataTable("PictureStyle");
        var idColumn = new DataColumn("ID", typeof(int));
        tableStyle.Columns.Add(idColumn);
        tableStyle.PrimaryKey = new DataColumn[] { idColumn };
        tableStyle.Columns.Add(new DataColumn("Style", typeof(string)));
        tableStyle.Rows.Add(1, "vertical");
        tableStyle.Rows.Add(2, "square");
        tableStyle.Rows.Add(3, "panorama");
        Display(tableStyle);

        // Create a second table, also using ID for PrimaryKey.
        DataTable tableSize = new DataTable("PictureSize");
        var idColumnSize = new DataColumn("ID", typeof(int));
        tableSize.Columns.Add(idColumnSize);
        tableSize.PrimaryKey = new DataColumn[] { idColumnSize };
        tableSize.Columns.Add(new DataColumn("Size", typeof(int)));
        tableSize.Rows.Add(1, 50);
        tableSize.Rows.Add(2, 150);
        tableSize.Rows.Add(3, 250);
        Display(tableSize);

        // Merge the 2 tables together based on the PrimaryKey.
        tableStyle.Merge(tableSize);
        Display(tableStyle);
    }

    static void Display(DataTable table)
    {
        // Display merged table.
        Console.WriteLine("::TABLE::");
        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine("ROW: {0}", string.Join(",", row.ItemArray));
        }
    }
}
::TABLE::
ROW: 1,vertical
ROW: 2,square
ROW: 3,panorama
::TABLE::
ROW: 1,50
ROW: 2,150
ROW: 3,250
::TABLE::
ROW: 1,vertical,50
ROW: 2,square,150
ROW: 3,panorama,250

Summary

DataTable is a powerful, convenient class. We can add columns and rows. DataTable is faster, in Windows Forms, than manually adding rows. It reduces complexity.