Sunday 21 April 2013

How to insert update delete in Gridview Using LINQ with Lamda Expression in asp.net



What is Lamda Expression:-

A lambda expression is an anonymous function that you can use to create delegates or expression tree types. By using lambda expressions, you can write local functions that can be passed as arguments or returned as the value of function calls. Lambda expressions are particularly helpful for writing LINQ query expressions.

Description:-

               In this example we can create a CRUD operation with LINQ but not through simple query but with the use of Lamda Expression.


    by using lamda expression we can reduce the length of Query so your code is very shortly and you can easily do the operation rather than simple long query 



In Linq you have to First Create a one DBML File in which you have to Add a all Table that you have to use in a application so not need to Connecton. And also you have to add Namespace using System.Linq instead  of  using System.Data.SqlClient;
 
to show Example of insert,update,delete in gridview using WCF Service please click here WCF Service For Insert,update,Delete
             
to show Example of insert,update,delete in gridview using LINQ please click here insert,update,delete using Linq

to show Example of insert,update,delete in gridview using Naming Container please click here Naming Container for insert update Delete in Gridview

to show Example of insert,update,delete in gridview using Modal Popup please click here insert,update,delete in Modal Popup

 to show Example of insert,update,delete in gridview using Stored Procedure please click here insert,update,delete through stored Procedure

  to show Example of insert,update,delete in XML File and bind to Gridview please click here insert,upadte,delete in XML File

  to show Example of insert,update,delete in gridview using Three Tier Architecture please click here Three Tier Architecture For insert,update,Delete

so How to insert update Delete through Lamda Expression Query are as define below code...






linqwithlamdajoin.aspx:-


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

<!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 id="Head1" runat="server">
<link rel="stylesheet" type="text/css" href="styles.css">

    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Panel ID="p" runat="server" BorderColor="#FFFF99" BorderStyle="Solid"
       GroupingText="Registration"  ScrollBars="Auto"
        >
 
  <center>
 
    <label for="password">Name</label> <asp:TextBox ID="txtcnm" runat="server" CssClass="txtfield"> </asp:TextBox>



    <asp:RequiredFieldValidator ID="rfvFirstName"

                 runat="server"

                 ControlToValidate="txtcnm"

                ErrorMessage="company Name can't be left blank"

                Display="Dynamic"> </asp:RequiredFieldValidator>



 <label for="email">description</label>

    <asp:TextBox ID="txtpnm" runat="server" TextMode="MultiLine" CssClass="txtfield"></asp:TextBox>

    <asp:RequiredFieldValidator

             ID="RequiredFieldValidator1" runat="server"

             ControlToValidate="txtpnm"

             ErrorMessage="person name can't be left blank"

             Display="Dynamic"> </asp:RequiredFieldValidator>

 <label for="email">Quantity</label>

   <asp:TextBox ID="txtqu" runat="server" CssClass="txtfield"

                    > </asp:TextBox>




    <asp:RequiredFieldValidator ID="RequiredFieldValidator2"

             runat="server"

         

             ControlToValidate="txtqu"

      

             ErrorMessage="Id can't be left blank"

             SetFocusOnError="True" Display="Dynamic"></asp:RequiredFieldValidator>
      <asp:CompareValidator ID="CompareValidator2" runat="server"
          ErrorMessage="only digit is allowed" ControlToValidate="txtqu" Display="Dynamic"
          Font-Bold="True" Type="Double" Operator="DataTypeCheck"></asp:CompareValidator>

   
  

<label for="email">rate</label>

   <asp:TextBox ID="txtrate" runat="server" CssClass="txtfield"

                    > </asp:TextBox>

    <asp:RequiredFieldValidator ID="RequiredFieldValidator4"

             runat="server"

             ControlToValidate="txtrate"

             ErrorMessage="Id can't be left blank"

             SetFocusOnError="True" Display="Dynamic"></asp:RequiredFieldValidator>
      <asp:CompareValidator ID="CompareValidator3" runat="server"
          ErrorMessage="only digit is allowed" ControlToValidate="txtrate" Display="Dynamic"
          Font-Bold="True" Type="Double" Operator="DataTypeCheck"></asp:CompareValidator>

     <br />
      <asp:Button id="Button1"
           Text="Register"
           CommandName="register"
           CommandArgument="register"
           OnCommand="Button1_Click" CssClass="btn"
           runat="server"/>
        
<asp:Button id="Button3"
           Text="Update"
           CommandName="update"
           CommandArgument="update"
           OnCommand="Button1_Click" CssClass="btn" Visible="false"
           runat="server"/>
      <asp:Button ID="Button4" runat="server" Text="Delete All" CssClass="btn"
          CausesValidation="false" onclick="Button4_Click1" />
     
        <asp:GridView ID="GridView1" runat="server"
     
       Font-Size="Large"
        ForeColor="#333333" ShowFooter="True" CellPadding="4" GridLines="None"
          
              AutoGenerateColumns="False"
        
          onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" onrowdatabound="GridView1_RowDataBound"
              >
         
        <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
        <Columns>
       <asp:TemplateField HeaderText="Delete All">
       <HeaderTemplate>
<asp:CheckBox ID="chkSelectAll" runat="server"
              AutoPostBack="true"
              OnCheckedChanged="chkSelectAll_CheckedChanged"/>
</HeaderTemplate>

       <ItemTemplate>
           <asp:CheckBox ID="CheckBox1" runat="server" AutoPostBack="true" />
       </ItemTemplate>
       </asp:TemplateField>
          
        
             <asp:TemplateField HeaderText="name">
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("name") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                     <asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("name") %>'></asp:TextBox>
                 </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Description">
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Eval("descp") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                     <asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("descp") %>'></asp:TextBox>
                 </EditItemTemplate>
                </asp:TemplateField>
            <asp:TemplateField HeaderText="Quantity">
                <ItemTemplate>
                    <asp:Label ID="Label3" runat="server" Text='<%# Eval("quantity") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                     <asp:TextBox ID="TextBox4" runat="server" Text='<%# Eval("quantity") %>'></asp:TextBox>
                 </EditItemTemplate>
                </asp:TemplateField>
            <asp:TemplateField HeaderText="Rate">
                <ItemTemplate>
                    <asp:Label ID="Label4" runat="server" Text='<%# Eval("rate") %>'></asp:Label>
                </ItemTemplate>
                 <EditItemTemplate>
                     <asp:TextBox ID="TextBox5" runat="server" Text='<%# Eval("rate") %>'></asp:TextBox>
                 </EditItemTemplate>
               <FooterTemplate>
                <asp:Label ID="lbl" runat="server" Text="GrandTotal"></asp:Label>
                </FooterTemplate>
             
                </asp:TemplateField>
                 <asp:TemplateField HeaderText="Total" >
                <ItemTemplate>
                    <asp:Label ID="Label6" runat="server" Text='<%# Eval("total") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                <asp:Label ID="lblgrand" runat="server"></asp:Label>
                </FooterTemplate>
                </asp:TemplateField>
           <asp:TemplateField>
                <ItemTemplate>
            <asp:LinkButton ID="LinkButton2" runat="server" CommandName="Delete"  CausesValidation="False">Delete</asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
            <ItemTemplate>
             <asp:LinkButton ID="LinkButton1" runat="server"  CommandName="Edit" CausesValidation="False">Edit</asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
      
            </Columns>
        
    </asp:GridView>
        <asp:Button ID="Button2" runat="server" Text="submit" CausesValidation="false" Visible="false"   CssClass="btn" onclick="Button2_Click"  />
        </center>
     </asp:Panel>
  
    </div>

    </form>
</body>
</html>







linqwithlamdajoin.aspx.cs:-


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;

public partial class linqwithlamdajoin : System.Web.UI.Page
{
    DataTable dt;
    DataTable ds;
    int xxx, xx = 0;
    Double tot = 0;
    static int temp = 0;
    static int chk = 0;
    DataClassesDataContext dc = new DataClassesDataContext();
    protected void Page_Load(object sender, EventArgs e)
    {


        if (!Page.IsPostBack)
        {



            Session["dtd"] = null;
            chk = 0;


        }
        dt = Session["dtd"] as DataTable;
        if (Request.QueryString["id"] != null && chk == 0)
        {
            chk++;
            var list = dc.hk_products.Join(dc.hk_rproducts, com => com.pid, st => st.rpid, (com, st) =>
                         new
                         {
                             com.pid,
                             com.name,
                             com.descp,
                             st.rate,
                             st.quantity,
                             st.total,

                         }).Where(com => com.pid == int.Parse(Request.QueryString["id"].ToString()));

            /* ds = new DataTable();

             string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";

             string q = "select p.name,p.descp,pr.quantity,pr.rate,pr.total from hk_product p INNER JOIN hk_rproduct pr ON p.pid = pr.rpid where p.pid='" + Request.QueryString["id"].ToString() + "'";

             SqlConnection conn = new SqlConnection(con);

             SqlCommand cmd = new SqlCommand(q, conn);



             SqlDataAdapter sa = new SqlDataAdapter();
             conn.Open();
             cmd.ExecuteNonQuery();
             sa.SelectCommand = cmd;
             sa.Fill(ds);*/
            GridView1.DataSource = list;
            GridView1.DataBind();
            if (dt == null)
            {
                dt = new DataTable();
                // dt.Columns.Add("pid").ToString();
                dt.Columns.Add("name").ToString();
                dt.Columns.Add("descp").ToString();
                dt.Columns.Add("quantity").ToString();
                dt.Columns.Add("rate").ToString();
                //dt.Columns.Add("rpid").ToString();
                dt.Columns.Add("total").ToString();
            }
            foreach (var item in list)
            {
                DataRow dr = dt.NewRow();
                // dr["pid"] = item.pid;
                dr["name"] = item.name;
                dr["descp"] = item.descp;
                dr["rate"] = item.rate;
                dr["quantity"] = item.quantity;
                dr["total"] = item.total;
                dt.Rows.Add(dr);
            }

            // dt = ds;
            Session["dtd"] = dt;



        }




    }
    protected void Button1_Click(object sender, CommandEventArgs e)
    {
        if (e.CommandName == "register")
        {


            if (dt == null)
            {
                dt = new DataTable();

                dt.Columns.Add("name").ToString();
                dt.Columns.Add("descp").ToString();
                dt.Columns.Add("quantity").ToString();
                dt.Columns.Add("rate").ToString();
                //dt.Columns.Add("rpid").ToString();
                dt.Columns.Add("total").ToString();
            }


            DataRow dr = dt.NewRow();


            dr["name"] = txtcnm.Text;
            dr["descp"] = txtpnm.Text;
            dr["quantity"] = txtqu.Text;
            dr["rate"] = txtrate.Text;


            dr["total"] = Double.Parse(txtqu.Text) * Double.Parse(txtrate.Text);
            /* for (int i = 0; i < dt.Rows.Count; i++)
             {
                 if (txtcid.Text.Equals(dt.Rows[i][0].ToString()))
                 {
                     xxx = 1;
                     break;
                 }
             }*/
            string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";

            string q = "select name from hk_product";

            SqlConnection conn = new SqlConnection(con);

            SqlCommand cmd = new SqlCommand(q, conn);

            conn.Open();
            SqlDataReader sr = cmd.ExecuteReader();
            while (sr.Read())
            {
                if (Request.QueryString["id"] == null)

                    if (sr["name"].ToString().Equals(dr["name"].ToString()))
                    {

                        xxx = 1;
                        break;

                    }
                if (Request.QueryString["id"] != null)
                    if (!dt.Rows[0][0].ToString().Equals(txtcnm.Text))
                    {
                        xx = 1;
                        break;


                    }


            }
            if (xx == 1)
            {
                Response.Write("<script>alert('you can insert only  " + dt.Rows[0][0].ToString() + "  record because you are in editin mode');</script>");

                txtcnm.Focus();
            }
            else
            {
                if (xxx == 1)
                {
                    Response.Write("<script>alert('the product " + txtcnm.Text + "  is exists in databse plz insert another product name');</script>");
                    txtcnm.Focus();
                    // txtcid.BackColor = System.Drawing.Color.Red;


                }
                else
                {
                    dt.Rows.Add(dr);


                    GridView1.DataSource = dt;

                    GridView1.DataBind();

                    Session["dtd"] = dt;
                    clear();

                    Button2.Visible = true;
                    txtcnm.ReadOnly = true;
                    txtpnm.ReadOnly = true;
                    txtqu.Focus();


                }
            }

        }
        if (e.CommandName == "update")
        {

            /* foreach (GridViewRow r1 in GridView1.Rows)
             {
                 if (GridView1.DataKeys[r1.RowIndex].Value.ToString().Equals(Session["id"].ToString()))
                 {
                     Label l = (Label)GridView1.Rows[r1.RowIndex].FindControl("i1");*/
            //dt.Rows[r1.RowIndex][0] = txtcid.Text;
            dt.Rows[temp][0] = txtcnm.Text;
            dt.Rows[temp][1] = txtpnm.Text;
            dt.Rows[temp][2] = txtqu.Text;
            dt.Rows[temp][3] = txtrate.Text;
            // dt.Rows[r1.RowIndex][5] = txtcid.Text;
            dt.Rows[temp][4] = Double.Parse(txtqu.Text) * Double.Parse(txtrate.Text);
            /* break;

         }

     }*/
            GridView1.DataSource = dt;
            GridView1.DataBind();
            Button1.Visible = true;
            Button3.Visible = false;
            Button2.Visible = true;
            Session["dtd"] = dt;
            clear();
            txtqu.Text = "";
            txtrate.Text = "";
            txtcnm.ReadOnly = false;
            txtpnm.ReadOnly = false;
            ClientScript.RegisterClientScriptBlock(this.GetType(), "key", "<script> alert('updated successfully');</script>");

        }

    }
    public string id()
    {
        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";

        string q = "select MAX(pid) from hk_product";

        SqlConnection conn = new SqlConnection(con);

        SqlCommand cmd = new SqlCommand(q, conn);
        conn.Open();
        SqlDataReader sr = cmd.ExecuteReader();
        string s = null;
        while (sr.Read())
        {
            s = sr[0].ToString();

        }

        return s;
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        int j = 0;
        txtpnm.ReadOnly = false;
        txtcnm.ReadOnly = false;
        if (Request.QueryString["id"] == null)
        {

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataClassesDataContext dc = new DataClassesDataContext();

                hk_product hp = new hk_product();

                hp.name = dt.Rows[i][0].ToString();
                hp.descp = dt.Rows[i][1].ToString();
                dc.hk_products.InsertOnSubmit(hp);
                dc.SubmitChanges();

                /* string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
                 // string s1 = "insert into hk_product,hk_rproduct values('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "')";
                 string s1 = "insert into hk_product values('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "')";

                 SqlConnection conn = new SqlConnection(con);
                 SqlCommand cmd = new SqlCommand(s1, conn);

                 conn.Open();
                 j = cmd.ExecuteNonQuery();
                 if (j > 0)
                     break;

                 conn.Close();*/
            }
            string a = id();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataClassesDataContext dc = new DataClassesDataContext();

                hk_rproduct hp1 = new hk_rproduct();

                hp1.quantity = double.Parse(dt.Rows[i][2].ToString());
                hp1.rate = double.Parse(dt.Rows[i][3].ToString());
                hp1.total = double.Parse(a);
                hp1.rpid = int.Parse(id());
                dc.hk_rproducts.InsertOnSubmit(hp1);
                dc.SubmitChanges();

                /*string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
                string s2 = "insert into hk_rproduct values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + a + "')";
                SqlConnection conn = new SqlConnection(con);

                SqlCommand cmd1 = new SqlCommand(s2, conn);
                conn.Open();

                cmd1.ExecuteNonQuery();
                conn.Close();*/
            }


            Response.Redirect("showprolinq.aspx");

        }
        else
        {
            DataClassesDataContext dc = new DataClassesDataContext();
            int r = int.Parse(Request.QueryString["id"].ToString());

            hk_rproduct h = new hk_rproduct();


            var obj1 = dc.hk_rproducts.Where(a => a.rpid == r);
            //var obj = (from v in dc.hk_companies where v.cid.ToString() == GridView1.DataKeys[e.RowIndex].Value.ToString() select v).SingleOrDefault();

            dc.hk_rproducts.DeleteAllOnSubmit(obj1);

            dc.SubmitChanges();


            /*string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
            string s1 = "delete from hk_rproduct where rpid='" + Request.QueryString["id"].ToString() + "'";
            SqlConnection conn = new SqlConnection(con);
            SqlCommand cmd = new SqlCommand(s1, conn);

            conn.Open();
            // j = cmd.ExecuteNonQuery();
            cmd.ExecuteNonQuery();
            conn.Close();*/
            dt = (DataTable)Session["dtd"];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //DataClassesDataContext dc = new DataClassesDataContext();

                hk_rproduct hp1 = new hk_rproduct();

                hp1.quantity = double.Parse(dt.Rows[i][2].ToString());
                hp1.rate = double.Parse(dt.Rows[i][3].ToString());
                hp1.total = double.Parse(Request.QueryString["id"].ToString());
                hp1.rpid = int.Parse(id());
                dc.hk_rproducts.InsertOnSubmit(hp1);
                dc.SubmitChanges();
                //string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
                // string s1 = "delete from hk_rproduct where rpid='" + Request.QueryString["id"].ToString() + "'";
                //SqlConnection conn = new SqlConnection(con);

                /*string s2 = "insert into hk_rproduct values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + Request.QueryString["id"].ToString() + "')";

                // SqlCommand cmd = new SqlCommand(s1, conn);
                SqlCommand cmd1 = new SqlCommand(s2, conn);
                conn.Open();
                // j = cmd.ExecuteNonQuery();
                cmd1.ExecuteNonQuery();

                conn.Close();*/


            }
            Response.Redirect("showprolinq.aspx");


        }

    }



    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {



        //Label l = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label10");
        Label l1 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label1");
        Label l2 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label2");
        Label l3 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label3");
        Label l4 = (Label)GridView1.Rows[e.NewEditIndex].FindControl("Label4");
        // Session["id"] = l.Text;
        // txtcid.Text = l.Text;
        //txtcid.ReadOnly = true;
        temp = e.NewEditIndex;
        txtcnm.Text = l1.Text;
        txtpnm.Text = l2.Text;
        txtqu.Text = l3.Text;
        txtrate.Text = l4.Text;

        Button3.Visible = true;
        Button1.Visible = false;
        txtcnm.ReadOnly = true;
        txtpnm.ReadOnly = true;





    }
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

        /* if (Request.QueryString["id"] == null)
         {
             dt.Rows.RemoveAt(e.RowIndex);

             GridView1.DataSource = dt;
             GridView1.DataBind();
         }
         else
         {
             string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
             string s1 = "delete from hk_rproduct where rpid='" + Request.QueryString["id"].ToString() + "' AND quantity='"+dt.Rows[e.RowIndex][2].ToString()+"'";
             SqlConnection conn = new SqlConnection(con);
             SqlCommand cmd = new SqlCommand(s1, conn);

             conn.Open();
             // j = cmd.ExecuteNonQuery();
             cmd.ExecuteNonQuery();
             conn.Close();*/
        dt.Rows.RemoveAt(e.RowIndex);

        GridView1.DataSource = dt;
        GridView1.DataBind();






    }

    protected void Button4_Click(object sender, EventArgs e)
    {
        clear();
    }
    public void clear()
    {
        // txtcid.Text = "";

        // txtcnm.Text = "";
        //txtpnm.Text = "";
        txtqu.Text = "";

        txtrate.Text = "";

    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {

        Label l = null;
        Label l1 = null;
        Double tt = 0;
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            l = (Label)e.Row.FindControl("lblgrand");
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    tt += Double.Parse(dt.Rows[i][4].ToString());
                    l.Text = tt.ToString();
                }

            }
            else
            {
                l.Text = tot.ToString();
            }

        }


        if (e.Row.RowType == DataControlRowType.DataRow)
        {


            l1 = (Label)e.Row.FindControl("Label6");

            tot += Double.Parse(l1.Text);

        }







    }

    protected void Button4_Click1(object sender, EventArgs e)
    {
        int to = dt.Rows.Count;

        int cc = 0;
        for (int z = 0; z < to; z++)
        {
            CheckBox c = (CheckBox)GridView1.Rows[z].FindControl("CheckBox1");
            if (c.Checked)
            {

                if (cc == 0)
                    dt.Rows.RemoveAt(z);
                else
                    dt.Rows.RemoveAt(z - cc);
                cc++;
            }
        }

        GridView1.DataSource = dt;
        GridView1.DataBind();

    }
    protected void chkSelectAll_CheckedChanged(object sender, EventArgs e)
    {
        CheckBox chkAll =
    (CheckBox)GridView1.HeaderRow.FindControl("chkSelectAll");
        if (chkAll.Checked == true)
            for (int i = 0; i < dt.Rows.Count; i++)
            {

                CheckBox c = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
                c.Checked = true;
            }
        else
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {

                CheckBox c = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
                c.Checked = true;
            }
        }

    }

}




showprolinq.aspx:-


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="showprolinq.aspx.cs" Inherits="showprolinq" EnableEventValidation="false" %>

<!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 id="Head1" runat="server">
<style type="text/css">
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
        .Grid td
        {
            background-color: #A1DCF2;
            color: black;
            font-size: 10pt;
            line-height:200%
        }
        .Grid th
        {
            background-color: #3AC0F2;
            color: White;
            font-size: 10pt;
            line-height:200%
        }
        .ChildGrid td
        {
            background-color: #eee !important;
            color: black;
            font-size: 10pt;
            line-height:200%
        }
        .ChildGrid th
        {
            background-color: #6C6C6C !important;
            color: White;
            font-size: 10pt;
            line-height:200%
        }
    </style>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $("[src*=plus]").live("click", function () {
        $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>")
        $(this).attr("src", "images/minus.png");
    });
    $("[src*=minus]").live("click", function () {
        $(this).attr("src", "images/plus.png");
        $(this).closest("tr").next().remove();
    });
</script>

    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <asp:GridView ID="gvCustomers" runat="server" CssClass="Grid"
     
       Font-Size="Large"
        ForeColor="#333333" ShowFooter="True" CellPadding="4" GridLines="None"
          
              AutoGenerateColumns="False"  DataKeyNames="pid"
        
           onrowediting="GridView1_RowEditing" onrowdeleting="GridView1_RowDeleting" onrowdatabound="GridView1_RowDataBound1"
           
              >
         
        <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
        <Columns>
              <asp:TemplateField HeaderText="">
       <ItemTemplate>
       <img alt = "" style="cursor: pointer" src="images/plus.png" />
    
                     <asp:Panel ID="pnlOrders" runat="server" Style="display: none">
                    <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" CssClass = "ChildGrid">
                        <Columns>
                            <asp:BoundField ItemStyle-Width="150px" DataField="quantity" HeaderText="Quantity" />
                            <asp:BoundField ItemStyle-Width="150px" DataField="rate" HeaderText="Rate" />
                        </Columns>
                    </asp:GridView>
                </asp:Panel>
       </ItemTemplate>
    </asp:TemplateField>

   
       <asp:TemplateField HeaderText="ID" Visible="false">
                <ItemTemplate>
                    <asp:Label ID="Label10" runat="server" Text='<%# Eval("pid") %>'></asp:Label>
                </ItemTemplate>
                 <EditItemTemplate>
                     <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("pid") %>'></asp:TextBox>
                 </EditItemTemplate>
             </asp:TemplateField>
        
             <asp:TemplateField HeaderText="name">
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("name") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                     <asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("name") %>'></asp:TextBox>
                 </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Description">
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Eval("descp") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                     <asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("descp") %>'></asp:TextBox>
                 </EditItemTemplate>
                </asp:TemplateField>
             <asp:TemplateField HeaderText="Total">
                <ItemTemplate>
                    <asp:Label ID="Label5" runat="server"  Text='<%# Eval("total") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                <asp:Label ID="lblgrand" runat="server"></asp:Label>
                </FooterTemplate>
             
                </asp:TemplateField>
         
           <asp:TemplateField>
                <ItemTemplate>
            <asp:LinkButton ID="LinkButton2" runat="server" CommandName="Delete"  >Delete</asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
            <ItemTemplate>
             <asp:LinkButton ID="LinkButton1" runat="server"  CommandName="Edit" >Edit</asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
      
            </Columns>
        
    </asp:GridView>

    </div>
    </form>
</body>
</html>




 showprolinq.aspx.cs:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;


public partial class showprolinq : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        ClientScript.RegisterClientScriptBlock(this.GetType(), "key", "<script> alert('added successfully');</script>");
        grid();

    }
    public void grid()
    {
        DataTable ds = new DataTable();

        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
        // "select k_sale.id,k_order.mid,k_order.cid,k_order.pro_image,k_order.pr_name,k_order.prize,k_order.quantity,k_order.status,k_order.total,kclient.ccname,kclient.cstate,kclient.ccity,kclient.caddress from k_order INNER JOIN kclient  ON k_order.cid=kclient.id  INNER JOIN k_sale  ON k_sale.oid=k_order.id where k_order.mid ='" + Session["login"].ToString() + "' and k_order.status ='" + z + "'";
        string q = "SELECT hk_product.name, hk_product.descp, hk_product.pid,SUM(hk_rproduct.total) AS total  FROM hk_product INNER JOIN hk_rproduct ON hk_product.pid = hk_rproduct.rpid GROUP BY hk_product.pid, hk_product.name, hk_product.descp";

        SqlConnection conn = new SqlConnection(con);

        SqlCommand cmd = new SqlCommand(q, conn);



        SqlDataAdapter sa = new SqlDataAdapter();
        conn.Open();
        cmd.ExecuteNonQuery();
        sa.SelectCommand = cmd;
        sa.Fill(ds);
        gvCustomers.DataSource = ds;
        gvCustomers.DataBind();
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {



        Label l = (Label)gvCustomers.Rows[e.NewEditIndex].FindControl("Label10");
        Response.Redirect("linqwithlamdajoin.aspx?id=" + l.Text);




    }
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

        int id = Int32.Parse(gvCustomers.DataKeys[e.RowIndex].Value.ToString());
        string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
        //Menu inm = (Menu)this.Master.FindControl("Menu1");

        //string q1="DELETE FROM  hk_product t1 INNER JOIN hk_rproduct t2 ON ( t1.pid = t2.rpid )";
        string q1 = "delete  from hk_product where pid = " + id;

        // string q2 = "delete from hk_rproduct where rpid = " + id;
        SqlConnection cn = new SqlConnection(con);

        SqlCommand cmd = new SqlCommand(q1, cn);
        // SqlCommand cmd1 = new SqlCommand(q2, cn);
        cn.Open();

        // cmd1.ExecuteNonQuery();
        cmd.ExecuteNonQuery();
        gvCustomers.EditIndex = -1;
        grid();




    }



    protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            Label l = (Label)e.Row.FindControl("lblgrand");
            string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
            // "select k_sale.id,k_order.mid,k_order.cid,k_order.pro_image,k_order.pr_name,k_order.prize,k_order.quantity,k_order.status,k_order.total,kclient.ccname,kclient.cstate,kclient.ccity,kclient.caddress from k_order INNER JOIN kclient  ON k_order.cid=kclient.id  INNER JOIN k_sale  ON k_sale.oid=k_order.id where k_order.mid ='" + Session["login"].ToString() + "' and k_order.status ='" + z + "'";
            string q = "select sum(total) from hk_rproduct";

            SqlConnection conn = new SqlConnection(con);

            SqlCommand cmd = new SqlCommand(q, conn);
            conn.Open();
            l.Text = cmd.ExecuteScalar().ToString();

        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string customerId = gvCustomers.DataKeys[e.Row.RowIndex].Value.ToString();
            GridView gvOrders = e.Row.FindControl("gvOrders") as GridView;
            string con = @"Data Source=SQLDB;Initial Catalog=Demo;User ID=Demod;Password=Demo1@";
            // "select k_sale.id,k_order.mid,k_order.cid,k_order.pro_image,k_order.pr_name,k_order.prize,k_order.quantity,k_order.status,k_order.total,kclient.ccname,kclient.cstate,kclient.ccity,kclient.caddress from k_order INNER JOIN kclient  ON k_order.cid=kclient.id  INNER JOIN k_sale  ON k_sale.oid=k_order.id where k_order.mid ='" + Session["login"].ToString() + "' and k_order.status ='" + z + "'";
            string q = "select quantity,rate from hk_rproduct";

            SqlConnection conn = new SqlConnection(con);
            DataTable dt = new DataTable();
            SqlDataAdapter sa = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(q, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            sa.SelectCommand = cmd;
            sa.Fill(dt);
            gvOrders.DataSource = dt;
            gvOrders.DataBind();
        }

    }

}

4 comments:

  1. thank you sir but i want to insert update delete in gridview in mvc.
    plz help me

    ReplyDelete
    Replies
    1. I hope this post is useful for you.
      http://aspsolutionkirit.blogspot.in/2013/05/insert-update-delete-operation-in-mvc.html

      Delete
  2. i hope this is useful for you........

    http://aspsolutionkirit.blogspot.in/2013/03/how-to-temprory-insertupdatedelete.html

    ReplyDelete