C# DataTable Examples

C# DataTable Examples

You need to store data that was read from a database such as SQL Server or generated in memory from user input. DataTable is ideal for this purpose, as you can take objects from memory and display the results in controls such as DataGridView in Windows Forms. Through the descriptions here, we examine the DataTable type in the C# language.

DataTable is found in the System.Data namespace.
It stores data in memory from databases or user input.
It helps with using DataGridView and SQL Server databases.

Simple DataTable example

First, the DataTable type is probably the most convenient and powerful way to store data in memory. You may have fetched this data from a database, or you may have generated it dynamically. In this example, we get a DataTable with four columns of type int, string and DateTime. This DataTable could then be persisted or displayed.

*** Program that uses DataTable [C#] *** 

using System;
using System.Data;

class Program
{
    static void Main()
    {
        //
        // Get the DataTable.
        //
        DataTable table = GetTable();
        //
        // Use DataTable here with SQL, etc.
        //
    }

    /// <summary>
    /// This example method generates a DataTable.
    /// </summary>
    static DataTable GetTable()
    {
        //
        // Here we create a DataTable with four columns.
        //
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        //
        // Here we add five DataRows.
        //
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        return table;
    }
}

Note on GetTable method. This method simply instantiates a new DataTable reference, adds four column collections to it, and then adds five drug and patient records. The next step to using this code could be to assign the DataSource to a Windows Forms control.

Detailed DataTable example

The first example was simplistic, but the next example here will show you how to insert data from object collections such as List into a DataTable, and then render that table onto the screen with Windows Forms.

Why use DataTable here? It makes your DataGridView simpler and easier. You could manually add data to the DataGridView using the Add method, but it's better to put the logic in a separate class. DataGridView has performance problems with manually adding rows.

How to use a DataGridView. Make a new Windows Forms project and add a DataGridView to it. It will be named dataGridView1 for you automatically. Its purpose will be rendering the DataTable you will make. You need some actual data for the example. You will have something important, so just use that.

Getting started with the DataTable. We want to make a new class and make a method that returns DataTable. This method will return the full DataTable. In my testing the application handled one million numbers with only moderate slowdowns.

*** Program that uses DataTable with DataGridView [C#] *** 

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;
        }
    }
}

Detailed description

It is an entire Form. The above code can be dropped into a Windows Forms application with a DataGridView in the designer. The two arrays are initialized in the class and in the constructor. They contain column information.

How to create a new DataTable. In the above code, we create a new DataTable. This is populated with all the data and put into the DataGrid. Note that there are more efficient ways of modifying existing DataTables.

How to loop through columns. We have a collection that contains many arrays. Each of those arrays needs to be a new column. So the main loop in the above code loops through the data we want to put in each column, one column at a time. This site now contains more information about looping through the rows and cells in the DataTable type in the C# language.

DataTable Foreach Loop

How to add columns by name. We add the column names to the DataTable. This can be the data series type, such as "name", "number" or "id". These are the column headers, which must be added with Columns.Add().

How to use an object array. The DataTable requires that we use arrays of objects to assign to the cells. Here we loop through each point in the column, which each point being a new row.

How to add empty rows. We keep adding rows until we have enough to contain all the data points in our array. If we don't add empty rows, the runtime will throw an exception. Right after this step, we assign the cells.

DataRow Examples

How to assign each cell in your column. We set each cell in this column to its data value. Be careful with the indexer syntax on the DataTable and make sure there are enough rows. We directly use our object list that we converted from another value type.

Using block

It is possible to use the using block in the C# language to enclose your DataTable instance. This may yield some memory and resource improvements in programs. This syntax is not essential but may improve certain cases; please see the selected article.

Using DataTable Block

Example use

Here you can assign the DataSource of your DataGridView directly to the result value. We use .NET's built-in display logic instead of reinventing the wheel. For Windows Forms, the DataSource property usually provides better display performance than trying to add cells or rows to the DataGridView individually.

*** Example code that sets DataSource [C#] *** 

//
// Draw new cells on DataGridView.
//
dataGridView1.DataSource = null;
dataGridView1.DataSource = GetResultsTable();

Rows and Columns

The two most important parts of DataTable are its Rows collection and its Columns collection. Use the instance Add method to add to either of these collections. There is more information, including many examples, of using DataRow collections on this site. The article on the DataColumn type provides more details on the internal representations of the DataTable in the .NET Framework.

DataColumn Class

DataSet

You can combine multiple DataTable instances into one collection by using the DataSet type. This provides some very useful functionality. The DataSet is described in a separate article.

DataSet Examples and Stuff

DataView

One very useful way to manipulate your DataTable's representation is to sort it using DataView. The DataView has a sort property you can assign. More detail about the DataView and sorting DataTables is available here.

DataView Usage

Summary

We saw how you can add data to a DataTable in the C# programming language. This code is effective when storing more than one million data points and rendering them every three seconds. DataTable is far smoother for Windows Forms than manually adding rows, and it separates presentation from logic. This article was written for .NET 3.5 but applies to earlier and later versions as well.

Data

© 2007-2011 Sam Allen. All rights reserved.