C# DataTable

Array Collections File Keyword String .NET Cast Class Data Dictionary Enum Exception If Interface Lambda LINQ List Loop Method Number Process Property Regex Sort Split StringBuilder Struct Substring Switch Time Windows

DataTable

Data. Our world is enormously complex. It contains an enormity of data. For complex projects, file systems are inadequate.
Databases,
and types from System.Data,
are more powerful.


Data

DataTable. This is a collection of rows and columns of data. It is not always simple to use DataTable. For beginners it is somewhat difficult.


About part

DataTable stores data. The data can come from anywhere—a database, from a method, from memory. Here, GetTable generates a table with four columns of different types.

Then:The table could be persisted to disk, displayed, or stored in memory—just like any other object.

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

Typeof:We must specify a type (as with typeof) to create a Column. All fields from rows in this column must have this type.

Based on:

.NET 4.5

C# program that uses DataTable

using System;
using System.Data;

class Program
{
    static void Main()
    {
	// Get the DataTable.
	DataTable table = GetTable();
	// ... Use the 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;
    }
}
Method

Methods. There are many ways to use a DataTable. Complete solutions are often project-specific. We cover the basics. Exceptions also may occur.

Compare RowsForeach
Iterate

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.

Generic method:Field is a generic method. So we must specify its parametric type (here, int) to indicate its behavior.

Field

Rows:Often we must access the rows of a DataTable. The Rows property, which can be looped over, is ideal here.

C# program that uses Rows, Field

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

Output

25
50
10
21
100
Using keyword

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

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

Tip 2:In this example, the Dispose method is invoked. After the using statement, the Dispose method on DataTable is called.

Using

Display:When Dispose is called, native resources are released. This may help resource usage problems.

C# program that uses using statement

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

Output

cat
DataTable

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.

Tip:DataTable works well with DataGridView. It makes DataGridView simpler and easier.

Caution:DataGridView has performance problems with manually adding rows. Using DataTable, List and DataSource helps.

ListDataGridView
C# program that uses DataGridView

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;
	}
    }
}
Window: Windows Forms

Notes on example. The code creates a DataTable and populates it with data. It is an entire Form and can be dropped into a Windows Forms application with a DataGridView in the designer.

Arrays:The two arrays are initialized in the class and in the constructor. They contain column information.

Note:Sorry for the confusing code—the important parts are how we interact with the DataTable, as with Columns.Add.


Loop

Loop through columns. We have a collection that contains many arrays. Each of those arrays needs to be a new column. We use DataTable's methods to expand its size.

Add:We add the column names to our DataTable with Columns.Add. These are the column headers.

Tip:DataTable requires objects to assign to the cells. Objects can hold any type of data.

Object
Framework: NET

DataSource. With this property in Windows Forms, we can assign a DataTable to render the data. DataSource improves display performance. Everything is added at once.

Sometimes:It is helpful to assign DataSource to null to clear it—this internally resets the data binding.

Example code that sets DataSource: C#

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

Convert. The List type is often useful. DataTable is better when we interact with controls such as DataGridView or with SQL databases. We can convert between the types.

Convert List, DataTable

Note:Many DataTable-List conversions may be needed. Often conversions require custom logic to be successful.


DataRow

Rows. Two important parts of DataTable are its Rows and Columns collections. With Rows, we access fields from a DataTable. We can loop over Rows with foreach.

DataRow
2D array

Columns. Think of columns as templates for rows. The fields in rows must adhere to the types specified in the corresponding DataColumns. Only a few columns are usually needed.

DataColumn
Set

DataSet. We can combine multiple DataTable instances into one collection with the DataSet type. So a DataSet is a set of DataTables (and other things from System.Data).

DataSet
About part

DataView. With this type, we can sort a DataTable's representation. We use the Sort property. The underlying data is not mutated.

DataView
Select method call

Select. We can use the Select method on DataTable. This filters elements based on some criteria. We receive an array of DataRow instances that match our filter.

Select
Convert

RowChanged. 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.

RowChanged

SQL types. With SQL, we formulate queries to database servers. SqlClient is used with many related types.
We manipulate
and store data of various sizes,
of various types.

SqlClientSqlCommandSqlCommandBuilderSqlConnectionSqlDataAdapterSqlDataReaderSqlParameter

Types. Sometimes SqlClient does not work on a specific database. Types such as OdbcConnection are helpful here. With OdbcConnection we can use MySQL database servers.

OdbcConnectionSQLCE

DataTable is a powerful, convenient class. To it we added data. We added columns. We added rows.
DataTable is faster,
in Windows Forms,
than manually adding rows. It reduces complexity.

C#