C# Filter DataSet by Date

Post Views: 325

C# Filter DataSet by Date

Several times, situation appears wherein we have already fetched data from database but we need it to filter by date. This post explains how we can filter C# dataset using date.

Syntax :

DataTable dt = datasetId.Tables[0].Select().Where(p => (Convert.ToDateTime(p[“YourDateColumnName”])
>= Convert.ToDateTime(FilterFromDate)) && (Convert.ToDateTime(p[“YourDateColumnName”])
<= Convert.ToDateTime(FilterToDate))).CopyToDataTable();

So, let’s try it on actual scenario.

I have a table in MS SQL Database with below data :

I will be using my already existing .net website project. You can create new or use already existing one. We will create a gridview and data bind it from the database using Dataset. Create a new page named Example.aspx.

Edit the design page and code page as below :

Example.aspx:

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

<!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">
 <title>Untitled Page</title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <asp:GridView runat="server" ID="dataGrid" BackColor="White" 
 BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" 
 ForeColor="Black" GridLines="Vertical" >
 <RowStyle BackColor="#F7F7DE" />
 <FooterStyle BackColor="#CCCC99" />
 <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
 <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
 <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
 <AlternatingRowStyle BackColor="White" />
 </asp:GridView>
 </div>
 </form>
</body>
</html>

To fill the grid view of the page we will use the below code :

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

public partial class Example : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
 {
 FillDataGrid();
 }

public void FillDataGrid()
 {
 try
 {
 SqlConnection con = new SqlConnection("Data Source=192.168.43.146;Initial Catalog=CustomersDB;User ID=sa;Password=rbs;");
 SqlCommand cmd = new SqlCommand("Select * from tblCustomerInfo", con);
 con.Open();
 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 adapter.Fill(ds, "tblCustomerInfo");
 dataGrid.DataSource = ds.Tables[0];
 dataGrid.DataBind();

}
 catch (Exception ex)
 {
 }
 }
}

This will fill the grid view at run time using the Dataset and Sql Adapter. Below is the screenshot of result without filtering.

Now, we will do filtering of dataset. Edit the code for filling datagrid as below :

SqlConnection con = new SqlConnection("Data Source=192.168.43.146;Initial Catalog=CustomersDB;User ID=sa;Password=rbs;");
 SqlCommand cmd = new SqlCommand("Select * from tblCustomerInfo", con);
 con.Open();
 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 adapter.Fill(ds, "tblCustomerInfo");
 DataTable dt = ds.Tables[0].Select().Where(p => (Convert.ToDateTime(p["OnDate"])
 >= Convert.ToDateTime("7/25/2017 0:00:00 AM")) && (Convert.ToDateTime(p["OnDate"])
 <= Convert.ToDateTime("7/30/2017 0:00:00 AM"))).CopyToDataTable();
 
 dataGrid.DataSource = dt;
 dataGrid.DataBind();

This will filter the Dataset and produce below result :

C# Filter DataSet by Date

So the date is filtered according the given date. Result Achieved.

C# Filter DataSet by Date