You are Here: Home >Aspnet>Database Tutorial >Filtering and Sorting Data Using DataView/DataTable

Filtering and Sorting Data Using DataView/DataTable

text zoom
Previous: Improving Performance using Stored Procedure Data Binding and Data Synchronization :Next

We often query the database and fetch the results of the query in a DataTable. We can make use of the inbuilt functions of DataTable to filter the data and show only the meaningful data to the user. DataView is also helps to fetch the data; however we cannot make use of the aggregate functions (SUM, MAX etc.) on DataView.DataView provides a customized view of the DataTable for sorting, filtering, searching, editing, and navigation. The following example will demonstrate the use of Data View in ASP.Net.


 


Example:- Data View


 


DataViewDT.aspx


 


<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="DataViewDT.aspx.cs" Inherits ="DataViewDT" %>


 


<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


 


<html xmlns ="http://www.w3.org/1999/xhtml" >


<head runat ="server">


</head>


<body>


<form id ="form1" runat ="server">


<div >


<strong > DataView and DataTable < br />


</strong >


<br />


<strong >< em > Original Data < br />


</em >


<asp:GridView ID ="grdvStore" 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 &nbsp;


<br />


<asp:Button ID ="btnFiltering" runat ="server" OnClick ="btnFiltering_Click" Text ="Filtering" Width ="103px" />< br />


<asp:GridView ID ="grdvFilter" 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 />


<asp:Button ID ="btnSorting" runat ="server" OnClick ="btnSorting_Click" Text ="Sorting" />< br />


<asp:GridView ID ="grdvSort" 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 >


</body >


</html >


 


 


 


DataViewDT.aspx.cs


 


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 DataViewDT : System.Web.UI. Page


{


  protected void Page_Load( object sender, EventArgs e)


    { //Bind Original GridView


    if (!IsPostBack)


        {


        string strConn = "Data Source=localhost;Initial Catalog= pubs; user Id=sa;Password = test" ;


        string sqlQuery = "select * from stores" ;


                SqlConnection conn = new SqlConnection (strConn);


                SqlDataAdapter ada = new SqlDataAdapter (sqlQuery, conn);


                DataTable dtStore = new DataTable ();


                ada.Fill(dtStore);


        //Bind GridView


                grdvStore.DataSource = dtStore.DefaultView;


                grdvStore.DataBind();


            }


    }


  protected void btnFiltering_Click( object sender, EventArgs e)


    { // Filtering of Data using DataView


    string strConn = "Data Source=localhost;Initial Catalog= pubs; user Id=sa;Password =test" ;


    string sqlQuery = "select * from stores" ;


        SqlConnection conn = new SqlConnection (strConn);


        SqlDataAdapter ada = new SqlDataAdapter (sqlQuery, conn);


        DataTable dtStore = new DataTable ();


        ada.Fill(dtStore);


    //Create DataView Object


        DataView dvStores = new DataView (dtStore);


        dvStores.RowFilter = "state = 'CA'" ;


    //Bind GridView : Only 'CA' state


        grdvFilter.DataSource = dvStores;


        grdvFilter.DataBind();


    }


  protected void btnSorting_Click( object sender, EventArgs e)


    { //Sorting of Data Using DataView


    string strConn = "Data Source=localhost;Initial Catalog= pubs; user Id=sa;Password = test" ;


    string sqlQuery = "select * from stores" ;


        SqlConnection conn = new SqlConnection (strConn);


        SqlDataAdapter ada = new SqlDataAdapter (sqlQuery, conn);


        DataTable dtStore = new DataTable ();


        ada.Fill(dtStore);


    //Create DataView Object


        DataView dvStores = new DataView (dtStore);


        dvStores.Sort = "city" ;


    //Bind GridView : Sorting By City


        grdvSort.DataSource = dvStores;


        grdvSort.DataBind();


    }


}


 


 


 


Output


 


                    
Previous: Improving Performance using Stored Procedure Data Binding and Data Synchronization:Next
Give feedback and win a prize.

Aspnet Source Code

...more

Aspnet Related Tutorials

...more

New Aspnet Resources

...more

Copyright © 2010 VisualBuilder. All rights reserved