C# Filter DataTable by Date

Post Views: 1,448

C# Filter DataTable 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# datatable using date.

Syntax :

DataTable dt = datatableId.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 DataTable. 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);
 DataTable dt = new DataTable();
 adapter.Fill(dt);
 
 dataGrid.DataSource = dt;
 dataGrid.DataBind();
}
 catch (Exception ex)
 {
 }
 }
}

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

Now, we will do filtering of datatable. 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);
 DataTable dt = new DataTable();
 adapter.Fill(dt);
 DataTable dt1 = dt.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 = dt1;
 dataGrid.DataBind();

This will filter the Datatable and produce below result :

C# Filter DataTable by Date

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

C# Filter DataTable by Date