LINQ to DATASET

LINQ to DATASET means, performing a LINQ Query operation on the dataset. Generally, the dataset is the most widely used component in ADO.NET because it is built with disconnected architecture, but it has limited querying capabilities. The LINQ to Dataset provides the facility to write richer queries on a dataset based on our requirement.

Syntax of LINQ to DATASET

Here is the syntax of writing the LINQ queries on ADO.NET dataset object.

var result = from dt in ds.Tables[0].AsEnumerable()
where (dt.Field<string>("EmpName").EndsWith("a"))
select new
{
Name = dt.Field<string>("EmpName"),
Location = dt.Field<string>("Location")
};

From the above syntax, we have written the LINQ Queries on ADO.NET Dataset "ds" object to get the data.

LINQ to Dataset Example

How to use LINQ to Dataset, we will see with the example. For that, we will create a new web application and make the connection with SQL Server and write the queries on Dataset object using LINQ, to display the data in gridview.

Firstly, we will create one new table "EmployeeDetails" in the database, for that we will execute the following query in our database and insert some dummy data to show it in the application.

Create database db_employee
use db_employee
CREATE TABLE [dbo].[EmployeeDetails](

[EmpId]  INT IDENTITY (1, 1) NOT NULL,

[EmpName]  VARCHAR (50) NULL,

[Location] VARCHAR (50) NULL,

[Gender] VARCHAR (20) NULL

PRIMARY KEY CLUSTERED ([EmpId] ASC)

);
insert into EmployeeDetails(EmpName,Location,Gender) values('Vaishali','Noida','Female')
insert into EmployeeDetails(EmpName,Location,Gender) values('Shalu','Gurgaon','Female')
insert into EmployeeDetails(EmpName,Location,Gender) values('Arpita','Gurgaon','Female')
Select *From EmployeeDetails

Now we will create a new web application for that we have to click on Go to file->Select New->Select Project like as shown below.

When we select the new project, a new popup will open in that we have to choose an empty asp.net web application and give name as "LINQ to DataSet" and click "OK" to create a new web application.

Now we will add the new web page to the application for that we have to Right-click on the application --> Select Add-> New Item->Select Web Form->give name "Default.aspx" and click "OK" button, it will create a new page in the application.

Now open Default.aspx page and write the code:

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
</head>
<body>
    <form id="form2" runat="server">
        
            <div class="GridviewDiv">

<asp:GridView ID="gvDetails" CssClass="Gridview" runat="server" AutoGenerateColumns="False">

<HeaderStyle CssClass="headerstyle" />

<Columns>

<asp:BoundField HeaderText="EmpName" DataField="Name" />

<asp:BoundField HeaderText="EmpLocation" DataField="Location" />

<asp:BoundField HeaderText="EmpGender" DataField="Gender" />

</Columns>

</asp:GridView>
        </div>
    </form>
</body>
</html>

In the above example, we used Dataset object and write the LINQ Query on the dataset object to get the detail from the dataset where EmpName ends with "a". Now we will run the application.

OUTPUT