Working with LINQ

on

Here is the .aspx

Here I am going to explain you how linq works instead of sql query. It’s very simple just create you LINQ to SQL class ie (DataClasses.dbml) and connect with your server, choose your database retrieve all the tables and use below code as given:

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

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>ASP.Net LINQ using C#title>
head>
<body>
    <form id="form1" runat="server">
    <div>
    <div>Working winth LINQ div>
    <div>
    <asp:GridView ID="gvEmployeeMLinq" runat="server" Width="50%" HorizontalAlign="Center" AutoGenerateColumns="false"
            DataKeyNames="empId" AllowPaging="True"
            onpageindexchanging="gvEmployeeMLinq_PageIndexChanging">
   <AlternatingRowStyle BackColor="ActiveBorder" Font-Italic="true" />
   <EmptyDataRowStyle BackColor="Azure" Font-Bold="true" />
   <FooterStyle BackColor="AliceBlue" Font-Strikeout="true" />
   <PagerStyle BackColor="Chocolate" ForeColor="ControlLight" />
   <Columns>
    <asp:BoundField DataField="empName" HeaderText="Employee Name" />
    <asp:BoundField DataField="Salary" HeaderText="Salary" />
   Columns>
    asp:GridView>
   
    div>
    div>
    <div style="text-align:center; margin-top:10px;"><asp:Label ID="lblMessage" runat="server">asp:Label>div>
    <div style="text-align:center; margin-top:40px;">
    <asp:Button ID="btnAdd" runat="server" Text="Add" onclick="btnAdd_Click" /> 
    <asp:Button ID="btnUpdate" runat="server" Text="Update" onclick="btnUpdate_Click"/> 
    <asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click"/>
    div>
    form>
body>
html>


.aspx.cs page

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;


public partial class _Default : System.Web.UI.Page
{
    DataClassesDataContext objLinq = new DataClassesDataContext();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //Bind GridView ();
            GridView();
        }

    }

    protected void GridView()
    {

Note: please remove comments one by one to see the changes
/*using top  query */

        // var dt = objLinq.EmployeeMasters.Where(tt => tt.Active.Equals("N")).Take(5);

        /*using Data source*/
        // var dt = objLinq.EmployeeMasters.Take(25);           

        /*using query*/
        /* var dt = from emp in objLinq.EmployeeMasters           
                  orderby emp.empName ascending
                    select new
                    {
                        empId = emp.empId,
                        empName = emp.empName,
                        salary = emp.salary
                    };
         */

        /*using DISTINCT query*/
        /*  var dt = (from emp in objLinq.EmployeeMasters
                    orderby emp.empName ascending
                    select new
                    {
                        //empId = emp.empId,
                        empName = emp.empName,
                        salary = emp.salary
                    }).Distinct();
          */

        /*using JOIN query*/
        var dt = from emp in objLinq.EmployeeMasters
                 join empT in objLinq.EmployeePhones
                 on emp.empId equals empT.empId

                 select new
                 {
                     empId = emp.empId,
                     empName = emp.empName,
                     salary = emp.salary
                 };


        gvEmployeeMLinq.DataSource = dt;
        gvEmployeeMLinq.DataBind();
    }
    protected void gvEmployeeMLinq_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvEmployeeMLinq.PageIndex = e.NewPageIndex;
        GridView();
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        string strEmpName = "Dinesh K";
        double salary = 15000;
        int telephone = 5;

        EmployeeMaster emp = new EmployeeMaster
              {
                  empName = strEmpName,
                  salary = salary,
                  telphone = Convert.ToString(telephone)
              };
        objLinq.EmployeeMasters.InsertOnSubmit(emp);
        objLinq.SubmitChanges();
        lblMessage.Text = "Record added successfully!";
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        int empId = 101;        //update 101 employee Id record

        EmployeeMaster emp = objLinq.EmployeeMasters.Single(UId => UId.empId == empId);
        emp.empName = "ved R";
        emp.salary = 32000;
        objLinq.SubmitChanges();

        lblMessage.Text = "Record updated successfully!";
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        int empId = 259;        //delete 259 employee Id record

        EmployeeMaster emp = objLinq.EmployeeMasters.First(UID => UID.empId == empId);
        objLinq.EmployeeMasters.DeleteOnSubmit(emp);
        objLinq.SubmitChanges();

        lblMessage.Text = "Record deleted successfully!";
    }
}

Comments

Post a Comment