DataView Sorting Filtering and DataBinding in ADO.NET 2.0 - Converting DataView to Table - ADO.NET Tutorials

http://davidhayden.com/blog/dave/archive/2006/02/11/2798.aspx

you will probably want to use the DataView Object in ADO.NET 2.0 for sorting and filtering a DataTable, because the DataView is bindable whereas the array of DataRows[] returned by the methods of the DataTable is not.

public DataView(DataTable table, string RowFilter, string Sort, DataViewRowState RowState)

Here is an example of using the full constructor to create a view from the Customers Table in the Northwind Database such that it only contains customers from a specific region ( SP ) and country ( Brazil ), sorted by ContactName, and including only current rows.

string connectionString = "..Nortwind Connection String..";
DataTable customers = new DataTable("Customers");

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand selectAllCustomers = connection.CreateCommand();
selectAllCustomers.CommandText = "SELECT * FROM [Customers]";
connection.Open();
customers.Load(selectAllCustomers.ExecuteReader(CommandBehavior.CloseConnection));
}

DataView dv = new DataView(customers,"Region = 'SP' and Country = 'Brazil'", "ContactName", DataViewRowState.CurrentRows);

dataGridView1.DataSource = dv;


Now you don't have to use the full DataView Constructor, you can also specify individual properties based on your needs. For example, we can delete two records from the same Customers DataTable and then only view those deleted records in the DataGridView:

string connectionString = "..Nortwind Connection String..";
DataTable customers = new DataTable("Customers");

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand selectAllCustomers = connection.CreateCommand();
selectAllCustomers.CommandText = "SELECT * FROM [Customers]";
connection.Open();
customers.Load(selectAllCustomers.ExecuteReader(CommandBehavior.CloseConnection));
}

DataView dv = new DataView(categories);

dv.Table.Rows[0].Delete();
dv.Table.Rows[1].Delete();
dv.RowStateFilter = DataViewRowState.Deleted;
dataGridView1.DataSource = dv;