nested gridediting in asp.net

code//
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.Configuration;

public partial class NestedGrid : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            gvDepartments.DataSource = GetData("select top 10 * from Departments");
            gvDepartments.DataBind();
        }
    }

    private static DataTable GetData(string query)
    {
        string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = query;
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataSet ds = new DataSet())
                    {
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }
    }

    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string DepartmentId = gvDepartments.DataKeys[e.Row.RowIndex].Value.ToString();
            GridView gvEmployees = e.Row.FindControl("gvEmployees") as GridView;
            gvEmployees.DataSource = GetData(string.Format("select top 3 Id,Name,Address,DepartmentId from Employees where DepartmentId='{0}'", DepartmentId));
            gvEmployees.DataBind();
        }
    }

    protected void gvEmployees_OnRowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView gvEmployees = sender as GridView;
        GridViewRow row = gvEmployees.Rows[e.NewEditIndex];
        int DepartmentId = Convert.ToInt32((row.Cells[0].FindControl("lblDepartmentId") as Label).Text);
        gvEmployees.EditIndex = e.NewEditIndex;

        gvEmployees.DataSource = GetData(string.Format("select top 3 Id,Name,Address,DepartmentId from Employees where DepartmentId='{0}'", DepartmentId));
        gvEmployees.DataBind();

    }

    protected void gvEmployees_OnRowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        GridView gvEmployees = sender as GridView;
        int Id = Convert.ToInt32(gvEmployees.DataKeys[e.RowIndex].Value);
        this.Delete(Id);
        gvEmployees.EditIndex = -1;
        Response.Redirect("CS.aspx");
    }

    private void Delete(int id)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string sqlStatment = "DELETE FROM  Employees WHERE Id = @id";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(sqlStatment, con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    protected void UpdateCustomer(object sender, GridViewUpdateEventArgs e)
    {
        GridView gvEmployees = sender as GridView;
        int id = Convert.ToInt32(((Label)gvEmployees.Rows[e.RowIndex].FindControl("lblId")).Text);
        string name = ((TextBox)gvEmployees.Rows[e.RowIndex].FindControl("txtName")).Text;
        string address = ((TextBox)gvEmployees.Rows[e.RowIndex].FindControl("txtAddress")).Text;
        int index = name.IndexOf(",");
        string finalName = name.Substring(index + 1);
        index = address.IndexOf(",");
        string finalAddress = address.Substring(index + 1);
        index = address.IndexOf(",");
        this.Update(id, finalName, finalAddress);
        gvEmployees.EditIndex = -1;
        gvDepartments.DataSource = GetData("select top 10 * from Departments");
        gvDepartments.DataBind();
    }

    private void Update(int id, string name, string address)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string sqlStatment = "UPDATE Employees SET Name = @Name,Address = @Address WHERE Id = @id";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(sqlStatment, con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Address", address);
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}




//design



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

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <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>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <asp:GridView ID="gvDepartments" runat="server" AutoGenerateColumns="false" CssClass="Grid"
                DataKeyNames="Dept_Id" OnRowDataBound="OnRowDataBound">
                <Columns>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <img alt="" style="cursor: pointer" src="images/plus.png" />
                            <asp:Panel ID="pnlEmployees" runat="server" Style="display: none">
                                <asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false" DataKeyNames="Id"
                                    OnRowDeleting="gvEmployees_OnRowDeleting" OnRowUpdating="UpdateCustomer" OnRowEditing="gvEmployees_OnRowEditing"
                                    CssClass="ChildGrid">
                                    <Columns>
                                        <asp:TemplateField HeaderText="DepartmentId" Visible="false">
                                            <ItemTemplate>
                                                <asp:Label ID="lblDepartmentId" Text='<%# Eval("DepartmentId") %>' runat="server"></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="Id">
                                            <ItemTemplate>
                                                <asp:Label ID="lblId" Text='<%# Eval("Id") %>' runat="server"></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField ItemStyle-Width="100px" HeaderText="Name">
                                            <ItemTemplate>
                                                <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name")%>'></asp:Label>
                                            </ItemTemplate>
                                            <EditItemTemplate>
                                                <asp:TextBox ID="txtName" runat="server" Text='<%# Bind("Name")%>'></asp:TextBox>
                                            </EditItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField ItemStyle-Width="100px" HeaderText="Address">
                                            <ItemTemplate>
                                                <asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address")%>'></asp:Label>
                                            </ItemTemplate>
                                            <EditItemTemplate>
                                                <asp:TextBox ID="txtAddress" runat="server" Text='<%# Bind("Address")%>'></asp:TextBox>
                                            </EditItemTemplate>
                                        </asp:TemplateField>
                                        <asp:ButtonField CommandName="Edit" Text="Edit" />
                                        <asp:ButtonField CommandName="Delete" Text="Delete" />
                                        <asp:ButtonField CommandName="Update" Text="Update" />
                                    </Columns>
                                </asp:GridView>
                            </asp:Panel>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField ItemStyle-Width="150px" DataField="Dept_Id" HeaderText="Dept Id" />
                    <asp:BoundField ItemStyle-Width="150px" DataField="Dept_Name" HeaderText="Dept Name" />
                </Columns>
            </asp:GridView>
        </ContentTemplate>
    </asp:UpdatePanel>
    </form>
</body>
</html>



//dadatabse



create database tempdb1
CREATE TABLE [dbo].[Departments](
    [Dept_Id] [int] IDENTITY(1,1) NOT NULL,
    [Dept_Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
(
    [Dept_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Employees](
    [Id] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Address] [varchar](100) NOT NULL,
    [DepartmentId] [int] NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

nested grid edit

http://www.aspforums.net/Threads/133072/Edit-Update-Delete-in-Nested-Child-GridView-in-ASPNet/

insert data with check exist or not

   if not exists (select AssessmentName from AssessmentDetails where ClientID=43  and ProgramName='Accenture (BPO)' and CourseName='Effective Business Writing' and AssessmentName='test' )
                        BEGIN
                            insert into AssessmentDetails (ClientId,ProgramName,CourseName,AssessmentName,MaximumScore,CreatedBy,Status) values (
                              43,'Accenture (BPO)','Effective Business Writing','test','688',578,'True')
                              select 1 as p
                                end
                              else
                                BEGIN
                                select 0 as p
                                end

create curser

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\BJS\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

returnjson

var Type;
var Url;
var Data;
var ContentType;
var DataType;
var ProcessData;
var UserLoginID;
var QuestionTitle;
var QuestionDesc;
var ExamID;
var TagID;
var Examname;
var PageIndex = 1, pageSize = 10, pageCount = 5;
var pages;

$(document).ready(function () {
    $(".pagination").hide();
    GetRecentQues();
    GetAllTags();
    GetExamForSelection();
    getautocompletetag();
    getArtists(PageIndex);
    //$("#fileupload").hide();
    if (getUrlVars()["title"]) {
        document.getElementById("txtTitle").value = getUrlVars()["title"];
    }
    if (getUrlVars()["ques"]) {
        document.getElementById("txtDesc").value = getUrlVars()["ques"];
    }
});

function getArtists(index) {
    $(".pagination").show();
    GetAllQuestion(index);
}

function AddQuesMaster(examId) {
    if (document.getElementById("fileslist")) {
        var questionid = document.getElementById("fileslist").value;
    }
    var UserLoginID = 1;
    ExamID = document.getElementById("txtExams").value;
    QuestionTitle = document.getElementById("txtTitle").value;
    QuestionDesc = document.getElementById("txtDesc").value;
    TagID = document.getElementById("txtTag").value;
 //   console.log(TagID);
    var parms = '{"userId":"' + UserLoginID + '","examId":"' + ExamID + '","questionTitle":"' + QuestionTitle + '","questionDesc":"' + QuestionDesc + '","tagId":"' + TagID + '","questionId":"' + questionid + '"}';
    //console.log(abc);
    Type = "POST";
    Url = questionanswersservicepath + "/AddQuestionMaster";
    Data = parms;
    ContentType = "application/json; charset=utf-8";
    DataType = "json"; ProcessData = false;
    // GetInsprofile();
    $.ajax({
        type: Type, //GET or POST or PUT or DELETE verb
        url: Url, // Location of the service
        data: Data, //Data sent to server
        contentType: ContentType, // content type sent to server
        dataType: DataType, //Expected data format from server
        processdata: ProcessData, //True or False
        success: function (msg) {
            $("#txtExams").tokenInput("clear");
            $("#txtTag").tokenInput("clear");
            document.getElementById("txtTitle").value = "";
            document.getElementById("txtDesc").value = "";
            alert('Question Submitted');
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}
function InsertQuesMaster(loginId, questionTitle, questionDesc) {
    var UserLoginID = loginId;
    ExamID = 0;
    QuestionTitle = questionTitle;
    QuestionDesc = questionDesc;
    TagID = 0;
    var parms = '{"userId":"' + UserLoginID + '","examId":"' + ExamID + '","questionTitle":"' + QuestionTitle + '","questionDesc":"' + QuestionDesc + '","tagId":"' + TagID + '","questionId":"' + questionid + '"}';

    //var parms = '{"userID":"' + UserLoginID + '","ExamID":"' + ExamID + '","QuestionTitle":"' + QuestionTitle + '","QuestionDesc":"' + QuestionDesc + '","TagID":"' + TagID + '","QuestionID":"' + questionid + '"}';
    //console.log(abc);
    Type = "POST";
    Url = questionanswersservicepath + "/AddQuestionMaster";
    Data = parms;
    ContentType = "application/json; charset=utf-8";
    DataType = "json"; ProcessData = false;
    // GetInsprofile();
    $.ajax({
        type: Type, //GET or POST or PUT or DELETE verb
        url: Url, // Location of the service
        data: Data, //Data sent to server
        contentType: ContentType, // content type sent to server
        dataType: DataType, //Expected data format from server
        processdata: ProcessData, //True or False
        success: function (msg) {
            alert('Question Submitted');
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}
function GetInsprofile() {
    $.ajax({
        type: Type, //GET or POST or PUT or DELETE verb
        url: Url, // Location of the service
        data: Data, //Data sent to server
        contentType: ContentType, // content type sent to server
        dataType: DataType, //Expected data format from server
        processdata: ProcessData, //True or False
        success: function (msg) {//On Successfull service call
            var dats = eval(msg.SelectInstituteAboutusDetailsResult);
            //  console.log(dats);
            $.each(dats, function (i, dat) {
                //console.log(dat.About);
                document.getElementById('About').innerHTML = dat.About;
                document.getElementById('Value').innerHTML = dat.Value;
                document.getElementById('Mission').innerHTML = dat.Mission;
                document.getElementById('centers').setAttribute("src", dat.ImgCenters);
                document.getElementById('winners').setAttribute("src", dat.ImgWinners);
                document.getElementById('learning').setAttribute("src", dat.ImgLearning);
            });
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}


function GetExam() {
    $.ajax({
        type: Type, //GET or POST or PUT or DELETE verb
        url: Url, // Location of the service
        data: Data, //Data sent to server
        contentType: ContentType, // content type sent to server
        dataType: DataType, //Expected data format from server
        processdata: ProcessData, //True or False
        success: function (msg) {//On Successfull service call
            var dats = eval(msg.GetExamsForSelectResult);
            console.log(dats);
            $("#txtExams").tokenInput(dats, {
                theme: "facebook"
            });
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}

function getautocompletetag() {
    $.ajax({
        type: "POST", //GET or POST or PUT or DELETE verb
        url: questionanswersservicepath + "/GetTagsForSelect", // Location of the service
        data: '{}', //Data sent to server
        contentType: "application/json; charset=utf-8", // content type sent to server
        dataType: "json", //Expected data format from server
        processdata: false, //True or False  
        success: function (msg) {//On Successfull service call
            var dats = eval(msg.GetTagsForSelectResult);
            $("#txtTag").tokenInput(dats, {
                theme: "facebook"
            });
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}

function GetExamForSelection() {
    var Examname = document.getElementById("txtExams").value;
    // alert(Examname);
    var abc = '{"examName":"' + Examname + '"}';
    //console.log(abc);
    Type = "POST";
    Url = questionanswersservicepath + "/GetExamsForSelect";
    Data = abc;
    ContentType = "application/json; charset=utf-8";
    DataType = "json"; ProcessData = false;
    GetExam();
}


function GetRecentQues() {
    Type = "POST";
    Data = '{}';
    Url = questionanswersservicepath + "/GetQuestions";
    ContentType = "application/json; charset=utf-8";
    DataType = "json"; ProcessData = false;
    GetQues();
}


function GetQues() {
    $.ajax({
        type: Type, //GET or POST or PUT or DELETE verb
        url: Url, // Location of the service
        data: Data, //Data sent to server
        contentType: ContentType, // content type sent to server
        dataType: DataType, //Expected data format from server
        processdata: ProcessData, //True or False
        success: function (msg) {//On Successfull service call
            // console.log(msg);
            var result = eval(msg.GetQuestionsResult);
        //    console.log(result);
            $.each(result, function (i, dat) {
                $('#output').append('<li><a>"' + dat.QuestionTitle + '"</a></li>');
            });
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}


function GetAllQuestion(index) {
    pageIndex = index;
    Type = "POST";
    Data = '{"pageIndex":"' + PageIndex + '","pageSize":"' + pageSize + '"}';
    Url = questionanswersservicepath + "/GetAllQuestions";
    ContentType = "application/json; charset=utf-8";
    DataType = "json"; ProcessData = false;
    GetAllQues();
}


function GetAllQues() {
    $.ajax({
        type: Type, //GET or POST or PUT or DELETE verb
        url: Url, // Location of the service
        data: Data, //Data sent to server
        contentType: ContentType, // content type sent to server
        dataType: DataType, //Expected data format from server
        processdata: ProcessData, //True or False
        success: function (msg) {//On Successfull service call
         //   console.log(msg);
            var result1 = eval(msg.GetAllQuestionsResult);
            //            console.log(JSON.stringify(msg));
            //            console.log(JSON.stringify(result1));
            //          
            //            console.log(result1);
            //$.each(result1, function (i, dat) {

            $('#outputall').html('');
            $('#paging').html('');
         
            for (var i = 0; i < result1.length - 1; i++) {
                $('#outputall').append(
                        '<div class="question-box">' +
                            '<div class="qus-user">' +
                                '<img src="' + result1[i].Image + '"></div>' +
                                    '<a href="QuestionAnswerDetails.aspx?quesid=qs-' + result1[i].questionid + '">' + result1[i].questionTitle + '</a></div>' +
                                        '<hr style="margin-bottom: 10px; margin-top: 10px;">');
                pages = result1[i + 1].NoOfPages;
            } if (PageIndex != 1) {
                $('#paging').append('<li><a href="#" onclick="previous();">Prev</a></li>');
            }
            $('#paging').append('<li><a href="#" onclick="pagecall(' + PageIndex + ');">' + PageIndex + '</a></li>');
            //                for (var j = PageIndex; j <= pages; j++) {
            //                  
            //                    $('#paging').append('<li><a href="#" onclick="pagecall(' + j + ');">' + j + '</a></li>');
            //                }
            $('#paging').append('<li><a> of </a></li>');
            $('#paging').append('<li><a>' + pages + '</a></li>');
            if (PageIndex != pages) {
                $('#paging').append('<li><a href="#" onclick="next();">Next</a></li>');
            }
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}

function previous() {
    if (PageIndex != 1) {
        PageIndex = PageIndex - 1;
    }
    GetAllQuestion(PageIndex);
}

function pagecall(index) {
    PageIndex = index;
    GetAllQuestion(index);
}

function next() {
    PageIndex = PageIndex + 1;
    //    if (pageSize = (pageIndex - 5)) {
    //        pageCount = pageIndex - 5;
    //    }
    GetAllQuestion(PageIndex);
}

function GetAllTags() {

    Type = "POST";
    Data = '{}';
    Url = questionanswersservicepath + "/GetTags";
    ContentType = "application/json; charset=utf-8";
    DataType = "json"; ProcessData = false;
    GetTags();
}

function GetTags() {
    $.ajax({
        type: Type, //GET or POST or PUT or DELETE verb
        url: Url, // Location of the service
        data: Data, //Data sent to server
        contentType: ContentType, // content type sent to server
        dataType: DataType, //Expected data format from server
        processdata: ProcessData, //True or False
        success: function (msg) {//On Successfull service call
         //   console.log(msg);
            var resultTag = eval(msg.GetTagsResult);

            $.each(resultTag, function (i, dat) {
                $('#outputTag').append('<li><a href="#">' + dat.Tag_Name + '</a></li>'
                );

            });
        },
        error: function ()
        { console.log('there is some error'); } // When Service call fails
    });
}

function attachfile() {
    $("#fileupload").show();
}

function getUrlVars() {
    var vars = [], hash;
    var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');
    for (var i = 0; i < hashes.length; i++) {
        hash = hashes[i].split('=');
        vars.push(hash[0]);
        vars[hash[0]] = hash[1];
    }
    return vars;
}