C# DataTable

DataTable

Data is read from a database. It is generated in memory from input. DataTable is ideal for storing data from any source. With it we take objects from memory and display the results in controls such as DataGridView.

Example

The DataTable type is a powerful way to store data in memory. You may have fetched this data from a database, or dynamically generated it. We get a DataTable with four columns of type int, string and DateTime.

IntStringsDateTimeData

Then:This DataTable could be persisted or displayed, stored in memory as any other object, or with helper methods manipulated.

GetTable:This method instantiates a new DataTable reference. It adds four column collections, then five rows.

Based on:

.NET 4.5

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.
	//
    }

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

Example 2

This example shows how to insert data from object collections such as List into a DataTable. We then render that table onto the screen with Windows Forms. DataTable helps with the visual display of data.

DataTable example screenshotQuestion and answer

Why use DataTable here? It makes your DataGridView simpler and easier. You could manually add data to the DataGridView using the Add method. It is better to put the logic in a separate class.

Caution:DataGridView has performance problems with manually adding rows. Using DataTable instead alleviates these.

How to use a DataGridView. Make a new Windows Forms project and add a DataGridView to it. The control will be named dataGridView1 automatically. Its purpose will be rendering the DataTable you will make.

Steps

And:You need some actual data for the example. You will have something important—just use that.

Getting started. We want to make a new class and a method that returns DataTable. This method will return the full DataTable. In 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;
	}
    }
}

Form

Squares

The above code is an entire Form and 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.

In the above code, we create a new DataTable. This is populated with data and put into the DataGrid. There are more efficient ways of modifying existing DataTables. By changing an existing one, you could avoid excess allocations.

Loop through columns. We have a collection that contains many arrays. Each of those arrays needs to be a new column. So the main for-loop in the above code goes through the data we want to put in each column.

Note:This site has more information about looping through the rows and cells in the DataTable type.

DataTable Foreach LoopNote

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.

Tip:DataTable requires object arrays to assign to the cells. Object arrays hold any derived type.

Warning: exclamation mark

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.

We set each cell in this column to its data value. Please be careful with the indexer syntax on the DataTable—make sure there are enough rows. We directly use our object list that we converted.

DataSource

Framework: NET

Here we assign the DataSource of a DataGridView directly to the result value. We use the built-in display logic. For Windows Forms, DataSource provides better display performance than trying to individually add elements.

Tip:Sometimes it is helpful to assign DataSource to null to clear it. I am unsure why this step is required.

Example code that sets DataSource: C#

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

Convert List

Convert or change

The List type is often useful. The DataTable type is better for when you are interacting with controls such as the DataGridView or with SQL databases. It is sometimes a good idea to convert your List into a DataTable.

Note:A custom method may need to be developed for each DataTable and List conversion needed.

Convert List, DataTable

Rows, columns

Logo

The two most important parts of DataTable are its Rows and Columns collections. Use the instance Add method to add to either of these collections. We look also into the types' internal representations.

Tip:There is more information, including many examples, of using DataRow and DataColumn collections on this site.

DataRowDataColumn

DataSet:You can combine multiple DataTable instances into one collection by using the DataSet type. This provides useful functionality.

DataSet Examples

DataView:One useful way to manipulate your DataTable's representation is to sort it using DataView. The DataView has a Sort property.

DataView Examples

Select

Select method call

You can use the Select method on the DataTable. This filters elements based on some criteria. You receive an array of DataRow instances that match your filter. The filter is specified in string format.

DataTable Select

RowChanged

Lightning bolt

A DataTable can monitor its changes. We use the RowChanged event, and similar events like ColumnChanged to detect changes. The AcceptChanges method too is helpful for this task. Often, these methods are used together.

DataTable RowChanged

Using

Using keyword

DataTable can be placed in a using statement. But is this worthwhile? Often the using block construct helps improve resource management in programs. We show the using statement wrapping a DataTable.

Tip:You can add Columns and Rows to the DataTable instance inside, but not outside, the using block.

Program that uses using statement: C#

using System;
using System.Data;

class Program
{
    static void Main()
    {
	using (DataTable table = new DataTable())
	{
	    table.Columns.Add("Name", typeof(string));
	    table.Columns.Add("Date", typeof(DateTime));

	    table.Rows.Add("cat", DateTime.Now);
	    table.Rows.Add("dog", DateTime.Today);
	}
    }
}

In this example, the Dispose method is invoked. After the using statement, the Dispose method on DataTable is called. This method is implemented on the base class for DataTable: MarshalValueByComponent.

Using

Worthwhile? Is enclosing the DataTable inside a using statement worthwhile? When Dispose is called, native resources from MarshalValueByComponent (a base class of DataTable) are released. This may be helpful.

Therefore:It is possible that the using statement could alleviate some resource usage problems.

Also:The using-statement is good coding hygiene for DataTables. It is recommended if your program has any possible memory issues.

Summary

We used DataTable in several C# programs.
To it we added data,
in the form of columns
and rows. This code is effective when storing more than one million data points and rendering them every three seconds. It is efficient.

Tip:DataTable is faster in Windows Forms than manually adding rows. It reduces complexity.


C#: Data