Search data from DataTable by Int or Date C# VB.Net

In each project one or more time we need to do extensive work on DataTable object. Asp.Net System.Data.DataTable provides us lot of general methods and extension methods. Select method is one of them-which help us to query data from a DataTable. You can consider DataTable as a RDBMS regular table. Everything is possible in DataTable now a days. So we are talking about Select method. Basically Select method is used to Search Data or find data from a DataTable. Select Method return an array of DataRows. So we can access the (0) index, or first row, from the array if we search through Primary Key. As well as we can iterate through the DataRows. In this example i will show you “How to Search or Find Data from DataTbale”. To do that first create a DataTable:

C# Code:

            DataTable oTable = new DataTable("Article");

            //Add DataTable column dynamically/run time/on the fly.
            oTable.Columns.Add(new DataColumn("ID", typeof(System.Int64)));
            oTable.Columns.Add(new DataColumn("Title", typeof(System.String)));
            oTable.Columns.Add(new DataColumn("Published", typeof(System.DateTime)));

            //Add DataTable rows dynamically/run time/on the fly.
            oTable.Rows.Add(1001, "DataTable Engineering", DateTime.Now.AddDays(5));
            oTable.Rows.Add(1002, "Event Calendar", DateTime.Now.AddDays(-5));
            oTable.Rows.Add(1003, "Master Detail Data", DateTime.Now);

Note: Don’t forget to add “using System.Data;” Namespace;

VB.Net Code:

            Dim oTable As DataTable = New DataTable("Article")

            'Add DataTable column dynamically/run time/on the fly.
            oTable.Columns.Add(New DataColumn("ID", System.Type.GetType("System.Int64")))
            oTable.Columns.Add(New DataColumn("Title", System.Type.GetType("System.String")))
            oTable.Columns.Add(New DataColumn("Published", System.Type.GetType("System.DateTime")))

            'Add DataTable rows dynamically/run time/on the fly.
            oTable.Rows.Add(1001, "DataTable Engineering", DateTime.Now.AddDays(5))
            oTable.Rows.Add(1002, "Event Calendar", DateTime.Now.AddDays(-5))
            oTable.Rows.Add(1003, "Master Detail Data", DateTime.Now)

Note: Don’t forget to add “Imports System.Data” Namespace;

Search DataTable Record/Data Using Primary Key/Integer DataType:
C# Code:

            //Search DataTable Using Primary Key
            //Search DataTable by Integer DataType
            DataRow[] oRow = oTable.Select("ID = 1003");
            Response.Write(oRow[0]["Title"]);

VB.Net Code:

            'Search DataTable Using Primary Key
            'Search DataTable by Integer DataType
            Dim oRow() As DataRow = oTable.Select("ID = 1003")
            Response.Write(oRow(0)("Title"))

Search DataTable Record/Data Using Text/String DataType:
While querying with String wrap the value with single quotation(“‘”).
C# Code:

            //Search DataTable by string DataType
            DataRow[] oRow = oTable.Select("Title = 'Event Calendar'");
            Response.Write(oRow[0]["Title"]);

VB.Net Code:

            'Search DataTable by string DataType
            Dim oRow() As DataRow = oTable.Select("Title = 'Event Calendar'")
            Response.Write(oRow(0)("Title"))

Search DataTable Record/Data Using Date/DateTime DataType:
To Find or search records from DataTable by Date or DateTime datatype, always wrap the date between “#”. If you already know MS Access DataBase SQL, then you are already familiar with such search criteria.
C# Code:

            //Search DataTable by Date Time DataType
            DataRow[] oRow = oTable.Select("Published > '#"+DateTime.Now.AddDays(1)+"#'");
            Response.Write(oRow[0]["Title"]);

VB.Net Code:

            'Search DataTable by Date Time DataType
            Dim oRow() As DataRow = oTable.Select("Published > '#" + DateTime.Now.AddDays(1) + "#'")
            Response.Write(oRow(0)("Title"))

Search using Date Between:
There is no Between operator for the Select Method like other SQL. But you can achieve this by using logical “AND”, “OR” operator. Find code example from below:
C# Code:

            //Search DataTable by Date Time Between
            DataRow[] oRow = oTable.Select("Published > '#" + DateTime.Now.AddDays(-1) + "#' AND Published <'#" + DateTime.Now.AddDays(1) + "#'");
            Response.Write(oRow[0]["Title"]);

VB.Net Code:

            'Search DataTable by Date Time Between
            Dim oRow() As DataRow = oTable.Select("Published > '#" + DateTime.Now.AddDays(-1) + "#' AND Published <'#" + DateTime.Now.AddDays(1) + "#'")
            Response.Write(oRow(0)("Title"))

Download Code Example C#        Download Code Example VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code