Welcome

Hello, Welcome to my blog. If you like feel free to refer others

Wednesday, 23 November 2011

Creating dynamic collapsible panel and mass update on datagrid

In my earlier post Read and Display Data From an Given Sheet of Excel File (.xsl or .xlsx) and Save in SQL Database in ASP.NET I have described how to upload the data from excel sheet to SQL server database.

In this topic I will be showing that uploaded data in the grid view category wise into a collapsible panel. I have made small change on my previous topic, I have added one extra column on the database i.e. ProjId and update 2 fields as bit. Added one new table to fetch the projects from database. So my new structure of the tables is like :

Table CodeReveiw:

CREATE TABLE [dbo].[CodeReview](
    [QID] [int] IDENTITY(1,1) NOT NULL,
    [Question_Description] [varchar](250) NULL,
    [Category] [varchar](50) NULL,
    [Reviewed] [bit] NOT NULL,
    [ReviewerComments] [varchar](8000) NULL,
    [DevelopmentStatus] [bit] NOT NULL,
    [DeveloperComments] [varchar](8000) NULL,
    [CreartedDate] [datetime] NULL,
    [Createdby] [varchar](50) NULL,
    [dcmVer] [varchar](5) NULL,
    [ProjId] [int] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CodeReview] ADD  CONSTRAINT [DF_CodeReview_Reviewed]  DEFAULT ((0)) FOR [Reviewed]
GO

ALTER TABLE [dbo].[CodeReview] ADD  CONSTRAINT [DF_CodeReview_DevelopmentStatus]  DEFAULT ((0)) FOR [DevelopmentStatus]
GO

ALTER TABLE [dbo].[CodeReview] ADD  CONSTRAINT [DF_CodeReview_CreartedDate]  DEFAULT (getdate()) FOR [CreartedDate]
GO

Table: ProjectDesc

CREATE TABLE [dbo].[ProjectDesc](
    [ProjectID] [int] IDENTITY(1,1) NOT NULL,
    [ProjectName] [varchar](50) NULL,
    [createtiondate] [datetime] NULL,
    [createdby] [varchar](50) NULL,
    [IsActive] [char](1) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ProjectDesc] ADD  CONSTRAINT [DF_ProjectDesc_createtiondate]  DEFAULT (getdate()) FOR [createtiondate]
GO

ALTER TABLE [dbo].[ProjectDesc] ADD  CONSTRAINT [DF_ProjectDesc_IsActive]  DEFAULT ('Y') FOR [IsActive]
GO

Note: Please add some data on ProjectDesc table to populate the data in dropdownlist

.aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Edit.aspx.cs" Inherits="ExcelApplication.Edit" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolKit" %>
<!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>Edit Data</title>
    <style type="text/css">
        tr.sectiontableentry1 td, tr.sectiontableentry2 td
        {
            padding: 4px;
        }
        tr.sectiontableentry1 td
        {
            padding: 8px 5px;
            background: url(hline.gif) repeat-x bottom;
        }
        tr.sectiontableentry2 td
        {
            padding: 8px 5px;
            background: url(hline.gif) repeat-x bottom #F2F2F2;
        }
       
        .cpHeader
        {
        color: white;
        background-color: #719DDB;
        font-size: 12px;
        font-family: Arial;
        font-weight: bold;
        cursor: pointer;
        height: 20px;
        padding: 2px;
        padding-top: 18px;
        padding-left: 7px;
        text-align: left;
        vertical-align: middle;
        }
       
        .cpBody
        {
            background-color: #DCE4F9;
            font-size: 11px;
            font-family: Arial;
            font-weight: normal;
            border: 10px solid #719DDB;
            padding: 4px;
            padding-top: 7px;           
        }
       
        .gridColumn
        {
            font-size: 11px;
            font-family: Arial;
            font-weight: normal;
        }    
       
       
    </style>
    <script type="text/javascript">
        function pageLoad()
        {
            var currentBehavior = null;
            var allBehaviors = Sys.Application.getComponents();
            for (var loopIndex = 0; loopIndex < allBehaviors.length; loopIndex++)
            {
                currentBehavior = allBehaviors[loopIndex];
                if (currentBehavior.get_name() == "CollapsiblePanelBehavior")
                {
                    currentBehavior._animation._fps = 0;
                    currentBehavior._animation._duration = 0;
                }
            }
        }   
    </script>
</head>

<body>
    <form id="form1" runat="server">
    <div style="font-family:Arial;">
        <asp:Label ID="lblProject" runat="server" Text="Select Project: " Font-Bold="true" Height="20px"></asp:Label>&nbsp;&nbsp;
        <asp:DropDownList ID="ddlProject" runat="server" Height="20px" OnSelectedIndexChanged="ddlProject_SelectedIndexChanged" AutoPostBack="True"></asp:DropDownList>
        <asp:RequiredFieldValidator ID="rfvProject" Height="20px" runat="server" ControlToValidate="ddlProject" ErrorMessage="*" ForeColor="Red" InitialValue="0" SetFocusOnError="True"></asp:RequiredFieldValidator>
        <br />
        <asp:Label ID="lblMessage" runat="server"></asp:Label>
    </div>
    <div id="Main" runat="server" visible="false" width="100%" style="font-family:Arial;">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <h3><b>Category Details : </b></h3>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
        <asp:DataList ID="Repeater1" runat="server"
            OnItemDataBound="Repeater1_ItemDataBound" RepeatDirection="Horizontal" Width="100%" >
            <ItemTemplate>
                <tr>
                    <td>
                        <asp:Panel ID="Panel1" runat="server" BackColor="#F2F2F2" Font-Size="Medium">
                            <div>
                                <asp:Panel ID="TitlePanel" runat="server" CssClass="cpHeader">
                                    <asp:Label ID="Label2" runat="server" Font-Bold="true" ForeColor="White"><%# DataBinder.Eval(Container.DataItem, "CategoryName")%></asp:Label>
                                    <asp:Image ID="Image1" runat="server" ImageAlign="Right" ImageUrl="~/Images/expand_blue.jpg" />
                                    <asp:Label ID="Label1" runat="server" ForeColor="Blue" Text="(Show details)"></asp:Label></asp:Panel>
                                <asp:Panel ID="ContentPanel" runat="server" CssClass="cpBody">
                                    <asp:Label ID="lblCategoryName" runat="server" Visible="false" Text='<%# DataBinder.Eval(Container.DataItem, "CategoryName")%>'></asp:Label>
                                    <asp:Label ID="lblEmptyData" runat="server" ForeColor="Red" Text="" Visible="false"></asp:Label>
                                    <asp:GridView ID="GridView1" runat="server" EmptyDataText="No Record Exists!" AutoGenerateColumns="false" AutoGenerateDeleteButton="True" OnRowDeleting="GridView1_RowDeleting" onrowdatabound="GridView1_RowDataBound">
                                        <Columns>
                                            <asp:TemplateField HeaderText="QID" Visible="false">
                                                <ItemTemplate>
                                                    <asp:Label ID="lblQID" runat="server" Text='<%#Eval("QID") %>' Visible="false"></asp:Label>
                                                </ItemTemplate>
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Question Description" ItemStyle-CssClass="gridColumn" ItemStyle-Width="25%" ItemStyle-HorizontalAlign="Center">
                                                <ItemTemplate>
                                                    <%#Eval("Question_Description")%>
                                                </ItemTemplate>                                               
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Category" ItemStyle-CssClass="gridColumn" ItemStyle-Width="10%"  ItemStyle-HorizontalAlign="Center">
                                                <ItemTemplate>
                                                    <asp:Label ID="lblCategory" runat="server" Text='<%#Eval("Category") %>'></asp:Label>
                                                </ItemTemplate>
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Reviewed"  ItemStyle-CssClass="gridColumn" ItemStyle-Width="5%" ItemStyle-HorizontalAlign="Center">
                                                <ItemTemplate>
                                                    <asp:CheckBox ID="chkReviewed" runat="server" Checked='<%#Eval("Reviewed") %>'></asp:CheckBox>
                                                </ItemTemplate>                                               
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Reviewer Comments"  ItemStyle-CssClass="gridColumn" ItemStyle-Width="25%">
                                                <ItemTemplate>
                                                    <asp:TextBox ID="txtReviewerComments" Width="95%" MaxLength="8000" TextMode="MultiLine" runat="server" Text='<%#Eval("ReviewerComments") %>'></asp:TextBox>
                                                </ItemTemplate>                                               
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Development Status"  ItemStyle-CssClass="gridColumn" ItemStyle-Width="5%" ItemStyle-HorizontalAlign="Center">
                                                <ItemTemplate>
                                                    <asp:CheckBox ID="chkDevelopmentStatus" runat="server" Checked='<%#Eval("DevelopmentStatus") %>'></asp:CheckBox>
                                                </ItemTemplate>                                               
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Developer Comments"  ItemStyle-CssClass="gridColumn"  ItemStyle-Width="25%">
                                                <ItemTemplate>
                                                    <asp:TextBox ID="txtDeveloperComments" Width="95%" MaxLength="8000" TextMode="MultiLine" runat="server" Text='<%#Eval("DeveloperComments") %>'></asp:TextBox>
                                                </ItemTemplate>                                               
                                            </asp:TemplateField>
                                        </Columns>
                                    </asp:GridView> 
                                    <br />
                                                                           
                                    <asp:Button ID="btnSave" runat="server" Text="SAVE" OnClick="UpdateButton_Click"></asp:Button>                                 
                                   
                                </asp:Panel>
                                <ajaxToolKit:CollapsiblePanelExtender ID="cpe" runat="server" TargetControlID="ContentPanel"
                                    ExpandControlID="TitlePanel" CollapseControlID="TitlePanel" ExpandedText="(hide details)"
                                    CollapsedText="(show details)" CollapsedImage="~/Images/expand_blue.jpg" ExpandedImage="~/Images/collapse_blue.jpg"
                                    ImageControlID="Image1" EnableViewState="true" TextLabelID="Label1" Collapsed="true">
                                </ajaxToolKit:CollapsiblePanelExtender>
                            </div>
                        </asp:Panel>
                    </td>
                </tr>
            </ItemTemplate>
        </asp:DataList>
        <br />
        </ContentTemplate>
        </asp:UpdatePanel>
    </div>
    </form>
</body>
</html>

Note:
1. Please add the reference of the AjaxControlToolkit dll in the project. you can download it from internet.

Desinger file :

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace ExcelApplication {
   
   
    public partial class Edit {
       
        /// <summary>
        /// form1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.HtmlControls.HtmlForm form1;
       
        /// <summary>
        /// lblProject control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.Label lblProject;
       
        /// <summary>
        /// ddlProject control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.DropDownList ddlProject;
       
        /// <summary>
        /// rfvProject control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.RequiredFieldValidator rfvProject;
       
        /// <summary>
        /// lblMessage control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.Label lblMessage;
       
        /// <summary>
        /// Main control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.HtmlControls.HtmlGenericControl Main;
       
        /// <summary>
        /// ScriptManager1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.ScriptManager ScriptManager1;
       
        /// <summary>
        /// UpdatePanel1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.UpdatePanel UpdatePanel1;
       
        /// <summary>
        /// Repeater1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.DataList Repeater1;
    }
}


Code Behind file :

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;
using System.Text;

namespace ExcelApplication
{
    public partial class Edit : System.Web.UI.Page
    {

        // create SqlConnection
        SqlConnection myConnection;
        SqlDataAdapter da;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                BindProject(); 
        }

        protected void ddlProject_SelectedIndexChanged(object sender, EventArgs e)
        {
            Main.Visible = true;
            BindRepeater();
        }

        protected void BindProject()
        {
            try
            {
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);
                SqlCommand myCommand = new SqlCommand("usp_GetProjects",myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
               
                myConnection.Open();               
                da = new SqlDataAdapter(myCommand);
                DataSet aus = new DataSet();
                da.Fill(aus);               

                if (aus.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow Dr in aus.Tables[0].Rows)
                    {
                        ddlProject.Items.Add(new ListItem(Dr["ProjectName"].ToString(), Dr["ProjectID"].ToString()));
                    }
                }

                ddlProject.Items.Insert(0, "---Select---");
                ddlProject.Items[0].Value = "0";
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Visible = true;
            }
            finally
            {
                //Destroy the adapter and close the connection
                da.Dispose();
                myConnection.Close();
                myConnection.Dispose();
            }
        }

        protected void BindRepeater()
        {
            try
            {
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);
                SqlCommand myCommand = new SqlCommand("usp_GetPanels",myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myConnection.Open();

                SqlParameterCollection spc = myCommand.Parameters;
                spc.AddWithValue("@ProjID", ddlProject.SelectedValue); 
               
                da = new SqlDataAdapter(myCommand);
                DataSet aus = new DataSet();
                da.Fill(aus);

                if (aus.Tables[0].Rows.Count > 0)
                {
                    Repeater1.DataSource = aus;
                    Repeater1.DataBind();
                    lblMessage.Visible = false;
                }
                else
                {
                    lblMessage.Text = "<br />No record exists!";                   
                    lblMessage.Visible = true;
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    Main.Visible = false;
                }
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Visible = true;
            }
            finally
            {
                //Destroy the adapter and close the connection
                da.Dispose();
                myConnection.Close();
                myConnection.Dispose();
            }       
        }

        protected void BindGrid(string ProjectID,string CategoryName,GridView DG)
        {
            DataListItem ri = (DataListItem)DG.NamingContainer;
            Label lblEmptyData = (Label)ri.FindControl("lblEmptyData");
            Button btnSave = (Button)ri.FindControl("btnSave");

            try
            {
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);
                SqlCommand myCommand = new SqlCommand("usp_GetGridData",myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;               
                myConnection.Open();               

                SqlParameterCollection spc = myCommand.Parameters;
                spc.AddWithValue("@ProjectID", ProjectID);
                spc.AddWithValue("@CategoryName", CategoryName);               

                da = new SqlDataAdapter(myCommand);
                DataSet aus = new DataSet();
                da.Fill(aus);

                DG.DataSource = aus;
                DG.DataBind();

                if (aus.Tables[0].Rows.Count > 0)
                {
                    btnSave.Visible = true;
                }
                else
                {                  
                    btnSave.Visible = false;
                }
            }
            catch (Exception ex)
            {
                lblEmptyData.Text = ex.Message;
                lblEmptyData.ForeColor = System.Drawing.Color.Red;
                lblEmptyData.Visible = true;
            }
            finally
            {
                //Destroy the adapter and close the connection
                da.Dispose();
                myConnection.Close();
                myConnection.Dispose();
            }
        }

        protected void Repeater1_ItemDataBound(object sender, DataListItemEventArgs e)
        {
            Label Category = (Label)e.Item.FindControl("lblCategoryName");
            GridView DataGrid = (GridView)e.Item.FindControl("GridView1");           
            BindGrid(ddlProject.SelectedValue, Category.Text,DataGrid);
        }       

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
          
            GridView GridView1 = (GridView)sender;

            DataListItem ri = (DataListItem)GridView1.NamingContainer;
            Label lblEmptyData = (Label)ri.FindControl("lblEmptyData");

            GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
            Label lbldeleteID = (Label)row.FindControl("lblQID");
            Label lbl = (Label)row.FindControl("lblCategory");

            try
            {
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);
                myConnection.Open();

                SqlCommand myCommand = new SqlCommand("usp_DeleteGridData", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                SqlParameterCollection spc = myCommand.Parameters;
                spc.AddWithValue("@QID", lbldeleteID.Text);
                spc.AddWithValue("@CategoryName", lbl.Text);               
                spc.AddWithValue("@ProjectID", ddlProject.SelectedValue); 

                myCommand.ExecuteNonQuery();

                lblEmptyData.Text = "Record deleted successfully";
                lblEmptyData.ForeColor = System.Drawing.Color.Green;
                lblEmptyData.Visible = true;

                BindGrid(ddlProject.SelectedValue, lbl.Text, GridView1);
            }
            catch (Exception ex)
            {
                lblEmptyData.Text = ex.Message;
                lblEmptyData.ForeColor = System.Drawing.Color.Red;
                lblEmptyData.Visible = true;
            }
            finally
            {
                //Destroy the adapter and close the connection
                myConnection.Close();
                myConnection.Dispose();
            }
           
        }       

        protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)
        {
            GridView GridView1 = (GridView)sender;
            DataListItem ri = (DataListItem)GridView1.NamingContainer;
            Label lblEmptyData = (Label)ri.FindControl("lblEmptyData");

            try
            {
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
                    CheckBox chkReviewed = (CheckBox)e.Row.FindControl("chkReviewed");
                    TextBox txtReviewerComments = (TextBox)e.Row.FindControl("txtReviewerComments");
                    CheckBox chkDevelopmentStatus = (CheckBox)e.Row.FindControl("chkDevelopmentStatus");
                    TextBox txtDeveloperComments = (TextBox)e.Row.FindControl("txtDeveloperComments");
                    if (chkReviewed.Checked)
                    {
                        chkReviewed.Enabled = false;
                        txtReviewerComments.ReadOnly = true;
                    }
                    else
                    {
                        chkReviewed.Enabled = true;
                        txtReviewerComments.ReadOnly = false;
                    }

                    if (chkDevelopmentStatus.Checked)
                    {
                        chkDevelopmentStatus.Enabled = false;
                        txtDeveloperComments.ReadOnly = true;
                    }
                    else
                    {
                        chkDevelopmentStatus.Enabled = true;
                        txtDeveloperComments.ReadOnly = false;
                    }  
                }            
            }
            catch (Exception ex)
            {
                lblEmptyData.Text = ex.Message;
                lblEmptyData.Visible = true;
            }          
        }

        protected void UpdateButton_Click(object sender, EventArgs e)
        {
            Button btnSave = (Button)sender;           
            DataListItem ri = (DataListItem)btnSave.NamingContainer;
            Label lblEmptyData = (Label)ri.FindControl("lblEmptyData");
            GridView gv = (GridView)ri.FindControl("GridView1");
            Label lblCategoryName = (Label)ri.FindControl("lblCategoryName");

            StringBuilder sb = new StringBuilder();
            sb.Append("<root>");
            for (int i = 0; i < gv.Rows.Count; i++)
            {               
                Label lblQID = (Label)gv.Rows[i].FindControl("lblQID");
                CheckBox chkReviewed = (CheckBox)gv.Rows[i].FindControl("chkReviewed");
                TextBox txtReviewerComments = (TextBox)gv.Rows[i].FindControl("txtReviewerComments");
                CheckBox chkDevelopmentStatus = (CheckBox)gv.Rows[i].FindControl("chkDevelopmentStatus");
                TextBox txtDeveloperComments = (TextBox)gv.Rows[i].FindControl("txtDeveloperComments");

                sb.Append("<row QID='" + lblQID.Text + "' Reviewed='" + chkReviewed.Checked + "' ReviewerComments='" + txtReviewerComments.Text.Trim() +
                          "' DevelopmentStatus='" + chkDevelopmentStatus.Checked + "' DeveloperComments='" + txtDeveloperComments.Text.Trim() + "'/>");

            }
            sb.Append("</root>");

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand("usp_UpdateGrid", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@XMLGridData", sb.ToString());

            try
            {                               
                myCommand.ExecuteNonQuery();               
                lblEmptyData.Text = "Record(s) updated successfully";
                lblEmptyData.Visible = true;
                lblEmptyData.ForeColor = System.Drawing.Color.Green;
            }
            catch (Exception ex)
            {
                lblEmptyData.Text = ex.Message;
                lblEmptyData.Visible = false;
                lblEmptyData.ForeColor = System.Drawing.Color.Red;
            }
            finally
            {
                myConnection.Close();
                myConnection.Dispose();
            }

            BindGrid(ddlProject.SelectedValue, lblCategoryName.Text, gv);
        }      
       
    }
}

Now I have created some procedure to fetch,update and delete the data.

1. To retrieve the distint category which will be my name of collapsible panel.

CREATE Procedure [dbo].[usp_GetPanels]
@ProjID varchar(10)
AS
Begin
    SELECT distinct(Category) as CategoryName FROM CodeReview WHERE projId=@ProjID
END

2. Get the list of active projects. To populate the projects dropdown.

CREATE Procedure [dbo].[usp_GetProjects]

AS
Begin
    Select ProjectID,ProjectName from ProjectDesc where IsActive='Y'
END

3. Get the data to fill datagrid for each panel.

CREATE Procedure [dbo].[usp_GetGridData]
@ProjectID varchar(10),
@CategoryName varchar(50)
AS
Begin
    SELECT [QID] ,
    [Question_Description],
    [Category],
    [Reviewed],
    [ReviewerComments],
    [DevelopmentStatus],
    [DeveloperComments] FROM CodeReview WHERE projId=@ProjectID and Category=@CategoryName
END

4. Update the grid data using mass update.

CREATE PROCEDURE [dbo].[usp_UpdateGrid]
(
 @XMLGridData XML
)

AS

BEGIN

      UPDATE CodeReview
            SET Reviewed=TempGridData.Item.value('@Reviewed', 'Bit'),
                  ReviewerComments=TempGridData.Item.value('@ReviewerComments', 'VARCHAR(8000)'),
                  DevelopmentStatus=TempGridData.Item.value('@DevelopmentStatus', 'Bit'),
                  DeveloperComments=TempGridData.Item.value('@DeveloperComments', 'VARCHAR(8000)')
      FROM @XMLGridData.nodes('/root/row') AS TempGridData(Item)
      WHERE QID=TempGridData.Item.value('@QID', 'VARCHAR(50)')

RETURN 0

END 

5. Delete the grid data.

CREATE Procedure [dbo].[usp_DeleteGridData]
@QID varchar(10),
@CategoryName varchar(50),
@ProjectID varchar(50)
AS
Begin
    Delete From CodeReview WHERE projId=@ProjectID and Category=@CategoryName and QID=@QID
END


Note: Don't forget the connection string [ConnectionInfo] which need to add in the web.config file :)

Please let me know if you have any doubts.

Happy learning..........




















Posted by at
Labels:

1 comment:

Subscribe to: Post Comments (Atom)