LINQ to Dataset

  Total Views : 144
  Comments : 0
 
  Keywords : Linq to dataset examples in asp.net using c# / vb.net, Linq queries on ado.net dataset in asp.net with example, How to use linq with dataset in asp.net using c# / vb.net with example

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 dataset is the most widely used component in ADO.NET because its built with disconnected architecture but its having limited querying capabilities. The LINQ to Dataset provides a facility to write richer queries on dataset based on our requirements.

Syntax of LINQ to Dataset

Following is the syntax of writing LINQ queries on 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 above syntax we written LINQ queries on ADO.NET dataset “ds” object to get required data.

LINQ to Dataset Example

Here we will see how to use LINQ to Dataset with example for that we will create one new web application and make connection with SQL Server and write queries on Dataset object using LINQ to display data in gridview.

 

First we will create one new table “EmployeeDetails” in database for that execute following query in your database and insert some dummy data to show it in application.

 

CREATETABLE [dbo].[EmployeeDetails](

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

[EmpName]  VARCHAR (50NULL,

[Location] VARCHAR (50NULL,

[Gender] VARCHAR (20NULL

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

 

Create new linq to sql project in visual studio

 

Once we select new project new popup will open in that select Asp.Net Empty Web Application and give name as “LINQtoDataset” and click OK to create new web application.

 

Now we will add 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 OK button it will create new page in application.

 

Now open Default.aspx page and write the code like 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 code behind file and write the code like 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 above example, we used Dataset object and written LINQ query on dataset object to get details from dataset where EmpName ends with “a”. Now we will run the application and see the output.

Output LINQ to Dataset Example

Following is the result of LINQ to Dataset example.

 

LINQ to Dataset in Asp.Net using C#, VB.NET with Examples

 

This is how we can write LINQ queries on dataset objects to get required data based on our requirements.

 

PREVIOUS

LINQ to ADO.NET
 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User