C#:DataTable

.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 Row Sort Split Static StringBuilder Substring Switch Tuple

DataView selectively filters and sorts DataTable rows. The DataView class in System.Data is ideal for sorting data for UIs or for database INSERT. We use the data in a DataTable by sorting it with DataView.


Intro. First, DataTable stores the physical data—the DataView is only a view of that data. You cannot easily sort a DataTable without a DataView reference. DataView has a Sort string, which we specify the column to sort with.

DataTable: Where you populate your data, from the user or database—an in-memory representation.DataView: Accessed with the DefaultView property on DataTable. DataViews allow you to filter and sort data, not store it.DefaultView: Access this property on your DataTable instance. This is an instance of DataView.Count: This is an instance property on all DataView instances. You can use this in a for-loop on the DataView.Sort: This is a string property on every DataView. Assign this to a string containing the name of a column.


Example. We first use DataView to sort one of four columns on a DataTable. In your program, the DataTable may be generated from user input or a database, but here we programmatically create it. The GetTable method returns a table with four columns.

C# program that uses DataView

using System;
using System.Data;

class Program
{
    static void Main()
    {
	//
	// Specify the column to sort on.
	//
	DataTable table = GetTable();
	table.DefaultView.Sort = "Weight";

	//
	// Display all records in the view.
	//
	DataView view = table.DefaultView;
	Console.WriteLine("=== Sorted by weight ===");
	for (int i = 0; i < view.Count; i++)
	{
	    Console.WriteLine("{0}, {1}, {2}, {3}",
		view[i][0],
		view[i][1],
		view[i][2],
		view[i][3]);
	}

	//
	// Now sort on the Name.
	//
	view.Sort = "Name";
	//
	// Display all records in the view.
	//
	Console.WriteLine("=== Sorted by name ===");
	for (int i = 0; i < view.Count; i++)
	{
	    Console.WriteLine("{0}, {1}, {2}, {3}",
		view[i][0],
		view[i][1],
		view[i][2],
		view[i][3]);
	}
    }

    /// <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("Weight", typeof(int));
	table.Columns.Add("Name", typeof(string));
	table.Columns.Add("Breed", typeof(string));
	table.Columns.Add("Date", typeof(DateTime));

	//
	// Here we add unsorted data to the DataTable and return.
	//
	table.Rows.Add(57, "Koko", "Shar Pei", DateTime.Now);
	table.Rows.Add(130, "Fido", "Bullmastiff", DateTime.Now);
	table.Rows.Add(92, "Alex", "Anatolian Shepherd Dog", DateTime.Now);
	table.Rows.Add(25, "Charles", "Cavalier King Charles Spaniel", DateTime.Now);
	table.Rows.Add(7, "Candy", "Yorkshire Terrier", DateTime.Now);
	return table;
    }
}

Output

=== Sorted by weight ===
7, Candy, Yorkshire Terrier, 4/5/2014 3:38:22 PM
25, Charles, Cavalier King Charles Spaniel, 4/5/2014 3:38:22 PM
57, Koko, Shar Pei, 4/5/2014 3:38:22 PM
92, Alex, Anatolian Shepherd Dog, 4/5/2014 3:38:22 PM
130, Fido, Bullmastiff, 4/5/2014 3:38:22 PM

=== Sorted by name ===
92, Alex, Anatolian Shepherd Dog, 4/5/2014 3:38:22 PM
7, Candy, Yorkshire Terrier, 4/5/2014 3:38:22 PM
25, Charles, Cavalier King Charles Spaniel, 4/5/2014 3:38:22 PM
130, Fido, Bullmastiff, 4/5/2014 3:38:22 PM
57, Koko, Shar Pei, 4/5/2014 3:38:22 PM

The program first has the Main entry point, which we use to sort the DataTable. The final method, GetTable, is a custom method that returns a new DataTable. Before we can use DataView, we must have a complete table.

Note: The GetTable method returns a DataTable with four columns, each specifying an attribute of a dog—Weight, Name, Breed and Date.

DataView. In the Main method, the DefaultView is accessed. DefaultView is a property of type DataView. This means it has a Sort property. We assign the Sort property to a string that matches one of the column names ("Weight").

Strings

Finishing the code. The first for-loop displays all fields in the DataTable, sorted by the Weight integer. Then, the DataView is resorted on Name, and then that view is displayed.

For

Output: In the first section, the Weight integer is ordered in ascending (low to high) order.

And: In the second section, the Name string is ordered in ascending ASCII (a - z) sort order.


Filter. You can also filter your DataView, which allows you to exclude rows. This means you can view your DataTable with certain rows matching a condition omitted. The two properties we use here are RowFilter, and RowStateFilter.

Property

Summary. We saw a complete example of using DataView to sort rows in a DataTable. This is powerful. It allows you to sort your data before using it in your program, and before INSERT-ing it into your SQL database.