C# DataTable Examples

Store data in memory with a DataTable. Add rows, call Compute and Merge, and set PrimaryKey.

DataTable. A crystal has many sides. In a similar sense, computer data has many rows and columns. The DataTable class stores rows and columns of data.

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

Step 1: We call GetTable, which generates a table. The returned object could be persisted to disk, displayed, or stored in memory.

Step 2: We create a new DataTable reference. Then we add 4 columns—these include a type, specified with typeof.

typeof

Step 3: We add the actual data as rows. The 4 arguments to each Add() call match up with the 4 columns already added.

Step 4: We print a cell value from the first row (row 0) and the Dosage column (which is an int).

C# program that uses DataTable 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("Patient", typeof(string)); table.Columns.Add("Date", typeof(DateTime)); // Step 3: here we add 5 rows. 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; } } Output 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.

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

DataRow 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. DataTable can be placed in a using statement. This can help programs' performance. Often the using block construct helps improve resource management.

Tip: We 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, Dispose() on DataTable is called.

Using

Dispose: 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

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

Form: This code is a Form and can be placed in a Windows Forms application with a DataGridView. It creates a DataTable.

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

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

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

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.

And: We call Compute() with a SUM of the column name (like Variety1). The filter argument can be specified as string.Empty.

Result: The Total row has the columns summed correctly. We do not need to sum the values with a for-loop.

C# program that uses Compute, SUM expression 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); } } } } Output :: 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.

Here: We create 2 DataTables with names PictureStyle and PictureSize. Each table has a column of IDs, and these IDs match in each table.

PrimaryKey: We assign the PrimaryKey to a column (or array of columns). Here we use the ID column, so we can join on the IDs.

Merge: We invoke Merge() to join the 2 tables based on their PrimaryKeys. One or more columns can be used.

Result: The "PictureStyle" DataTable is updated to include the row fields from the "PictureSize" data table—each row now has 3 cells.

C# program that uses Merge to join DataTables 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)); } } } Output ::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

System.Data objects. Each DataTable has DataColumn and DataRow parts. And with DataSet and DataView we can collect 2 or more DataTables, or filter data.DataColumn DataRow DataSet DataView

Foreach. Often we want to loop over our DataTable rows. This can be done with a foreach-loop. The exact syntax can be confusing, and objects are sometimes needed.DataTable Foreach Loop

Compare rows. Data may sometimes contain duplicate rows. This condition can be detected with a simple looping algorithm. The syntax can be used for many DataTable tasks.DataTable Compare Rows

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.DataTable Select

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

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

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

A summary. DataTable is a powerful, convenient class. We added columns. We added rows. DataTable is faster, in Windows Forms, than manually adding rows. It reduces complexity.

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

C# DataTable Examples

Related Links:

.Net

Java

Related Links