LINQ to ADO.NET

by Online Tutorials Library July 14, 2022

LINQ to ADO.Net

LINQ to ADO.Net means using the LINQ queries on the objects in the ADO.Net. The LINQ to ADO.Net gives us a chance to write the LINQ Queries on Enumerable object in ADO.Net and the LINQ to ADO.Net is having the three types of LINQ technologies available. These are LINQ to Dataset, LINQ to SQL and LINQ to Entities.

Syntax of LINQ to ADO.Net

Here is the syntax of LINQ to ADO.Net objects.

  var result = from dt in ds.Tables[0].AsEnumerable()  where (dt.Field<string>(“Gender”) == “Male”)  select new  {  Name = dt.Field<string>(“empname”),  Location = dt.Field<string>(“location”),  Gender = dt.Field<string>(“gender”),  };  

In the above syntax, we have written the LINQ queries on the ADO.NET dataset object “ds” to get the required data.

LINQ to ADO.NET Example

Here we will see how to use LINQ to ADO.NET with the example for that we will create one new web application and make the connection with the SQL SERVER and write the queries on ADO.NET object (dataset) using LINQ to display the data in grid view.

Here first, 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.

Now we will create a new web application for that Go to File->Select New->Select Project, as shown below.

After selecting the new project, a popup will open in that we have to choose asp.net empty Web Application and give the name as LINQ to ADO.NET and click ok to create a new web application.

Now we will add the web page to the application for that we have to the right click on the application-> Select Add->Select Web Form->Give name as “Default.aspx” and click the ok button, it will create a new page in the application.

Now open the “Default.aspx” page and write the code as shown below.

  <%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>    <!DOCTYPE html>    <html xmlns=”http://www.w3.org/1999/xhtml”>  <head runat=”server”>      <title></title>  </head>  <body>      <form id=”form1″ runat=”server”>          <div>              <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 the file and write the code as shown below:

  using System;  using System.Collections.Generic;  using System.Data;  using System.Data.SqlClient;  using System.Linq;  using System.Web;  using System.Web.UI;  using System.Web.UI.WebControls;    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=tutoraspire\SQLEXPRESS;Integrated Security=true;Initial Catalog=ADONET”))            {                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>(“Gender”) == “Male”)                                 select new                                 {                                     Name = dt.Field<string>(“EmpName”),                                     Location = dt.Field<string>(“Location”),                                     Gender = dt.Field<string>(“Gender”),                                 };                    gvDetails.DataSource = result;                    gvDetails.DataBind();                }          }      }  }  

In the above example, we used the ADO.NET dataset object and write the LINQ query on the dataset object to get the details from the dataset where Gender is equal to “Female”. Now we will run the application and see the output.

OUTPUT

Next TopicLINQ to DataSet

LINQ to ADO.NET

LINQ to ADO.Net

Syntax of LINQ to ADO.Net

LINQ to ADO.NET Example

LINQ Sorting Operators

Kill command

You may also like