DataView Sorting Filtering and DataBinding in ADO.NET 2.0 - Converting DataView to Table - ADO.NET Tutorials
by David Hayden ( Florida .NET Developer )
Awhile back I talked about a lot of cool new features in the DataTable in ADO.NET 2.0 as well the capabilities of the DataTable for sorting, filtering, and searching:
All of that is really cool and all, but realistically 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.
DataTable as a DataSource - Thanks to DataTable.DefaultView Property
You can bind a DataTable as a DataSource thanks to the DataTable's DefaultView Property. When you write code like this:
dataGridView1.DataSource = myDataTable
this is what is happening under the covers:
dataGridView1.DataSource = myDataTable.DefaultView;
The DataGridView is binding to the DataTable.DefaultView property, which is all the columns and rows in your table with a DataRowState equal to CurrentRows.
Creating a DataView
However, often you will need to display only a subset of the rows in your DataTable sorted in some specific fashion. You do this by creating your own DataView and specifying the DataTable, RowFilter, Sort, and DataViewRowState by either using the full DataView Constructor or the individual properties.
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;
DataView and DataGridView in a Winform Application
The best way to learn about DataView is to create a simple winform application and run through the various options for displaying data. I did just that as shown below:
I essentially dropped a DataGridView on a form along with 4 buttons that show off various ways to work with a DataView. Note that each time I retrieved a new DataTable. This is not necessary. I just did it to show you I was working with fresh data each time and nothing else was going on behind the scenes. Here is the code:
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public DataTable GetTable()
{
string connectionString = "...Northwind...";
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));
}
return customers;
}
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = GetTable().DefaultView;
}
private void btnSortByCity_Click(object sender, EventArgs e)
{
DataView dv = new DataView(GetTable());
dv.Sort = "City ASC";
dataGridView1.DataSource = dv;
}
private void btnOnlyMexico_Click(object sender, EventArgs e)
{
DataView dv = new DataView(GetTable());
dv.RowFilter = "Country = 'Mexico'";
dataGridView1.DataSource = dv;
}
private void btnDeletedRowsOnly_Click(object sender,
EventArgs e)
{
DataView dv = new DataView(GetTable());
dv.Table.Rows[0].Delete();
dv.Table.Rows[1].Delete();
dv.RowStateFilter = DataViewRowState.Deleted;
dataGridView1.DataSource = dv;
}
private void btnEverythingGoes_Click(object sender,
EventArgs e)
{
DataView dv = new DataView(GetTable(),"Region = 'SP'
and Country = 'Brazil'", "ContactName",
DataViewRowState.CurrentRows);
dataGridView1.DataSource = dv;
}
}
Creating a DataTable from a DataView - DataView.ToTable()
In ADO.NET 2.0, the DataView Object has a new method called ToTable, which allows you to create a new table based on data in the DataView. Here is an example that we used above that creates a new DataTable listing Brazilian Contact Names only:
DataView dv = new DataView(GetTable(),"Region = 'SP'
and Country = 'Brazil'", "ContactName",
DataViewRowState.CurrentRows);
DataTable newTable = dv.ToTable("BrazilianContactNames",
true, new string[] { "ContactName" });
dataGridView1.DataSource = newTable.DefaultView;
If we take a look at the new table using the DataSet Debugger Visualizer you can see that it only includes the Brazilian contact names:
Conclusion
Rather than using the sorting and filtering capabilities in the DataTable, you will want to use the DataView to sort and filter the data and then set the DataView as a DataSource in your winform and web applications.
Author: David Hayden ( Florida .NET Developer )
Free ADO.NET Tutorials