Tutorial RSS
 
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.

Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.


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>

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!


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



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

Looking for more Database Tutorials? Click Here!

Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!


Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!

411asp.net123aspxDotNetFreaksServer Intellect