Here we will learn LINQ to Dataset in asp.net using c#, vb.net with example and how to query dataset data using LINQ in asp.net using c#, vb.net with examples.
LINQ to Dataset
LINQ to Dataset means performing LINQ query operations on Dataset. Generally, the dataset is the most widely used component in ADO.NET because it's built with disconnected architecture, but it has limited querying capabilities. The LINQ to Dataset provides a facility to write richer queries on a dataset based on our requirements.
Syntax of LINQ to Dataset
Following is the syntax of writing LINQ queries on the ADO.NET dataset object in c#, vb.net.
C# Code
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")
};
VB.NET Code
Dim result = From dt In ds.Tables(0).AsEnumerable() Where (dt.Field(Of String)("EmpName").EndsWith("a")) Select New With {.Name = dt.Field(Of String)("EmpName"), .Location = dt.Field(Of String)("Location")}
If you observe the above syntax, we wrote LINQ queries on the ADO.NET dataset “ds” object to get the required data.
LINQ to Dataset Example
Here we will see how to use LINQ to Dataset with an example. For that, we will create one new web application, make a connection with SQL Server and write queries on the Dataset object using LINQ to display data in gridview.
First, we will create one new “EmployeeDetails” table in the database. For that, execute the following query in your database and insert some dummy data to show it in the application.
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 values ('Suresh Dasari','Chennai','Male')
insert into EmployeeDetails values ('Rohini Alavala','Chennai','Female')
insert into EmployeeDetails values ('Praveen Alavala','Guntur','Male')
insert into EmployeeDetails values ('Sateesh Chandra','Vizag','Male')
insert into EmployeeDetails values ('Sushmitha','Vizag','Female')
Now, we will create a new web application for that Go to File à Select New à Select Project like as shown below.
Once we select a new project new popup will open in that select Asp.Net Empty Web Application and give name as “LINQtoDataset” and click OK to create a new web application.
Now we will add a web page to the application for that Right-click on your application à Select Add à New Item à Select Web Form à give name as “Default.aspx” and click on OK button it will create a new page in the application.
Now open the Default.aspx page and write the code as shown below.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1">
<title>Bind Gridview with LINQ to ADO.NET Operations</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: #df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView ID="gvDetails" CssClass="Gridview" runat="server" AutoGenerateColumns="False">
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField HeaderText="Name" DataField="Name" />
<asp:BoundField HeaderText="Location" DataField="Location" />
<asp:BoundField HeaderText="Gender" DataField="Gender" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Now open the code behind file and write the code as shown below.
C# Code
using System;
using System.Web.UI;
using System.Data.SqlClient;
using System.Data;
namespace WebApplication2
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGridview();
}
}
protected void BindGridview()
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection("Data Source=SURESH\\SQLEXPRESS;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select * from employeedetails", con);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
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"),
Gender = dt.Field<string>("Gender"),
};
gvDetails.DataSource = result;
gvDetails.DataBind();
}
}
}
}
}
VB.NET Code
Imports System.Web.UI
Imports System.Data.SqlClient
Imports System.Data
Partial Class VBCode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindGridview()
End If
End Sub
Protected Sub BindGridview()
Dim ds As New DataSet()
Using con As New SqlConnection("Data Source=SURESH\SQLEXPRESS;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("select * from employeedetails", con)
cmd.CommandType = CommandType.Text
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
Dim result = From dt In ds.Tables(0).AsEnumerable() Where (dt.Field(Of String)("EmpName").EndsWith("a")) Select New With {.Name = dt.Field(Of String)("EmpName"), .Location = dt.Field(Of String)("Location"), .Gender = dt.Field(Of String)("Gender")}
gvDetails.DataSource = result
gvDetails.DataBind()
End If
End Using
End Sub
End Class
If you observe the above example, we used Dataset object and written LINQ query on dataset object to get details from the dataset where EmpName ends with “a”. Now we will run the application and see the output.
Result of LINQ to Dataset Example
Following is the result of the LINQ to Dataset example.
This is how we can write LINQ queries on dataset objects to get the required data based on our requirements.
