Navigator: Home - Display - Using DataView and DataTable to filter and sort C#

Using DataView and DataTable to filter and sort C#

In this tutorial, we will create three GridViews and use DataView and DataTable to manipulate one DataSource to filter and sort the data into the GridViews. C# version.

Download the Full Working Version of this Project written with Visual Studio.NET C# 2005 Here!

Looking for the VB.NET 2005 Version? Click Here!

Looking for more Database Tutorials? Click Here!

For this tutorial, we will be using a sample database to display the data in three different GridViews. The First will be the original data, then we will use DataTables and DataViews to filter and sort the data into two more GridViews. These will be displayed on button clicks. First we need the Connection String:

<appSettings>
<add key="ConnectionString" value="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" />
</appSettings>

We then implement our GridView controls, as well as the buttons and a TextBox for the user to choose what to filter by:

<form id="form1" runat="server">
<div>
<strong>DataView and DataTable<br />
</strong >
<br />
<strong ><em>Original Data<br /></em >
<asp:GridView ID ="gvResults" runat ="server" AutoGenerateColumns ="False" BackColor ="White" BorderColor ="#CCCCCC" BorderStyle ="None" BorderWidth ="1px" CellPadding ="3">
<FooterStyle BackColor ="White" ForeColor ="#000066" />
<Columns>
<asp:BoundField DataField ="stor_id" HeaderText ="Store ID" />
<asp:BoundField DataField ="stor_address" HeaderText ="Address" />
<asp:BoundField DataField ="city" HeaderText ="City" />
<asp:BoundField DataField ="state" HeaderText ="State" />
</Columns>

<RowStyle ForeColor ="#000066" />
<SelectedRowStyle BackColor ="#669999" Font-Bold ="True" ForeColor ="White" />
<PagerStyle BackColor ="White" ForeColor ="#000066" HorizontalAlign ="Left" />
<HeaderStyle BackColor ="#006699" Font-Bold ="True" ForeColor ="White" />
</asp:GridView>

<br />Filtering Data Using DataView<br />
Enter State to Filter:
<asp:TextBox ID="txtFilter" runat="server" Columns="3" MaxLength="3"></asp:TextBox><br />

<asp:Button ID ="btnFiltering" runat ="server" OnClick ="btnFiltering_Click" Text ="Filtering" Width ="103px" /><br />
<asp:GridView ID ="gvFilter" runat ="server" AutoGenerateColumns ="False" BackColor ="White" BorderColor ="#999999" BorderStyle ="Solid" BorderWidth ="1px" CellPadding ="3" ForeColor ="Black" GridLines ="Vertical">
<FooterStyle BackColor ="#CCCCCC" />
<Columns>
<asp:BoundField DataField ="stor_id" HeaderText ="Store ID" />
<asp:BoundField DataField ="stor_address" HeaderText ="Address" />
<asp:BoundField DataField ="city" HeaderText ="City" />
<asp:BoundField DataField ="state" HeaderText ="State" />
</Columns>

<SelectedRowStyle BackColor ="#000099" Font-Bold ="True" ForeColor ="White" />
<PagerStyle BackColor ="#999999" ForeColor ="Black" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Black" Font-Bold ="True" ForeColor ="White" />
<AlternatingRowStyle BackColor ="#CCCCCC" />
</asp:GridView>

<br />Sorting Data Using DataView<br />
Sort by City:<br />
<asp:Button ID ="btnSorting" runat ="server" OnClick ="btnSorting_Click" Text ="Sorting" /><br />
<asp:GridView ID ="gvSort" runat ="server" AutoGenerateColumns ="False" BackColor ="White" BorderColor ="#336666" BorderStyle ="Double" BorderWidth ="3px" CellPadding ="4" GridLines ="Horizontal">
<FooterStyle BackColor ="White" ForeColor ="#333333" />

<Columns>
<asp:BoundField DataField ="stor_id" HeaderText ="Store ID" />
<asp:BoundField DataField ="stor_address" HeaderText ="Address" />
<asp:BoundField DataField ="city" HeaderText ="City" />
<asp:BoundField DataField ="state" HeaderText ="State" />
</Columns>

<RowStyle BackColor ="White" ForeColor ="#333333" />
<SelectedRowStyle BackColor ="#339966" Font-Bold ="True" ForeColor ="White" />
<PagerStyle BackColor ="#336666" ForeColor ="White" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="#336666" Font-Bold ="True" ForeColor ="White" />
</asp:GridView>
</strong>
</div>
</form>

The code-behind will look something like this:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string sqlQuery = "SELECT * from Table1" ;
SqlConnection conn = new SqlConnection (ConfigurationManager.AppSettings["ConnectionString"]);
SqlDataAdapter sda = new SqlDataAdapter(sqlQuery, conn);
DataTable dtData = new DataTable();
sda.Fill(dtData);

gvResults.DataSource = dtData.DefaultView;
gvResults.DataBind();
}
}

protected void btnFiltering_Click( object sender, EventArgs e)
{
string sqlQuery = "SELECT * from Table1";
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
SqlDataAdapter sda = new SqlDataAdapter (sqlQuery, conn);
DataTable dtData = new DataTable ();
sda.Fill(dtData);

DataView dvData = new DataView(dtData);
dvData.RowFilter = "state = '" + txtFilter.Text.ToUpper() + "'";

gvFilter.DataSource = dvData;
gvFilter.DataBind();
}

protected void btnSorting_Click( object sender, EventArgs e)
{
string sqlQuery = "SELECT * from Table1";
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
SqlDataAdapter sda = new SqlDataAdapter(sqlQuery, conn);
DataTable dtData = new DataTable();
sda.Fill(dtData);

DataView dvData = new DataView(dtData);
dvData.Sort = "city";

gvSort.DataSource = dvData;
gvSort.DataBind();
}
}

Download the Full Working Version of this Project written with Visual Studio.NET C# 2005 Here!

Looking for the VB.NET 2005 Version? Click Here!

Looking for more Database Tutorials? Click Here!
411asp.net123aspxDotNetFreaksServer Intellect