ASHIS KUMAR DAS: 2011

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>  
        <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..........

Tuesday 22 November 2011

How to find out second position of any character from a given string

declare @Input varchar(100)
set @Input = '*ashis*kumardas'
select charindex('*',@Input,CHARINDEX('*',@Input,1)+1)

Output: 7

Happy Learning............

Thursday 17 November 2011

Read and Display Data From an Given Sheet of Excel File (.xsl or .xlsx) and Save in SQL Database in ASP.NET

The page design :

<%@ Page Title="Home Page" Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
    Inherits="ExcelApplication._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</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;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table style="padding: 5px; font-size: 11px;" align="center" border="0">
            <tbody>
                <tr>
                    <td>
                        <strong>Please Select Excel File Containing The Details…</strong>
                    </td>
                </tr>
                <tr>
                    <td>
                        <div style="background: url(hline.gif) repeat-x bottom #F2F2F2; padding: 8px 5px;
                            border-bottom: 1px solid #ccc;">
                            <asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>  
                            <asp:Label ID="TextBox1" Text="Sheet Name: " Height="20px" runat="server"></asp:Label>  
                            <asp:TextBox ID="txtSheetName" runat="server" MaxLength="100"></asp:TextBox>  <asp:RequiredFieldValidator
                                ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtSheetName"
                                ErrorMessage="*" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>
 <asp:Button ID="btnUpload" runat="server" Text="Upload"
                                onclick="btnUpload_Click" /><br />
                            <asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label>
                        </div>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:GridView ID="grvExcelData" runat="server" EmptyDataText="No Record Found!!" >
                            <RowStyle CssClass="sectiontableentry2" />
                            <AlternatingRowStyle CssClass="sectiontableentry1" />
                        </asp:GridView>
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
    </form>
</body>
</html>

Code Behind File :

namespace ExcelApplication
{
    public partial class _Default : System.Web.UI.Page
    {
        string SheetNameExcel = null;

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if ((txtFilePath.HasFile))
            {

                OleDbConnection conn = new OleDbConnection();
                OleDbCommand cmd = new OleDbCommand();
                OleDbDataAdapter da = new OleDbDataAdapter();
                DataSet ds = new DataSet();

                string query = null;
                string connString = "";
                string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
                string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();

                //Check file type
                if (strFileType == ".xls" || strFileType == ".xlsx")
                {
                    //save the file on server
                    txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
                }
                else
                {
                    lblMessage.Text = "Only excel files allowed";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Visible = true;
                    return;
                }

                //Fetch the path of the file
                string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);


                //Connection String to Excel Workbook
                if (strFileType.Trim() == ".xls")
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (strFileType.Trim() == ".xlsx")
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }

                //query = "SELECT * FROM [Sheet1$]";

                string SheetName = txtSheetName.Text.Trim() + "$";

                //Create the connection object
                conn = new OleDbConnection(connString);
                //Open connection
                if (conn.State == ConnectionState.Closed) conn.Open();

                //Check sheet name
                if (FindExcelSheetName(conn, SheetName))
                {

                    //Create the query dynamically
                    query = "SELECT * FROM [" + SheetNameExcel + "]";

                    //Create the command object
                    cmd = new OleDbCommand(query, conn);
                    da = new OleDbDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds);

                    //Get the xmldata in string
                    string excelData = ds.GetXml().ToString();

                    ds=AddExcelData(excelData);
                    if (ds.Tables[0].Rows.Count >0)
                    {
                        grvExcelData.DataSource = ds.Tables[0];
                        grvExcelData.DataBind();

                        lblMessage.Text = "Data retrieved successfully! Total Records:" + ds.Tables[0].Rows.Count;
                        lblMessage.ForeColor = System.Drawing.Color.Green;
                        lblMessage.Visible = true;

                        //Destroy the adapter and close the connection
                        da.Dispose();
                        conn.Close();
                        conn.Dispose();

                        //Delete the file from server
                        if (File.Exists(strNewPath))
                            File.Delete(strNewPath);
                    }
                    else
                    {
                        lblMessage.Text = "Sheet Does Not Contain Any Records!!";
                        lblMessage.ForeColor = System.Drawing.Color.Red;
                        lblMessage.Visible = true;

                        //Destroy the adapter and close the connection
                        da.Dispose();
                        conn.Close();
                        conn.Dispose();

                        //Delete the file from server
                        if (File.Exists(strNewPath))
                            File.Delete(strNewPath);
                    }
                }
                else
                {
                    lblMessage.Text = "Sheet Name Does Not Exists!";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Visible = true;

                    //Destroy the adapter and close the connection
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();

                    //Delete the file from server
                    if (File.Exists(strNewPath))
                        File.Delete(strNewPath);
                }
            }
            else
            {
                lblMessage.Text = "Please select an excel file first!!";
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Visible = true;
            }
        }

        protected DataSet AddExcelData(string excelData)
        {
            SqlCommand myCommand = new SqlCommand("usp_AddExcelData");
            myCommand.CommandType = CommandType.StoredProcedure;

            //Creat and add the parameters
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@excelData";
            param.Value = excelData;
            myCommand.Parameters.Add(param);

            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@Createdby";
            param1.Value = "Ashis";
            myCommand.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.ParameterName = "@dcmVer";
            param2.Value = "1";
            myCommand.Parameters.Add(param2);

            // create SqlConnection
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);

            myConnection.Open();

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

            //Destroy the adapter and close the connection
            da.Dispose();
            myConnection.Close();
            myConnection.Dispose();

            return aus;
        }

        protected bool FindExcelSheetName(OleDbConnection conn,string sheetName)
        {
            //Get the all sheet name
            DataTable dt = null;
            bool find=false;

            //Get the sheet name from schema
            dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt != null)
            {
                foreach (DataRow row in dt.Rows)
                {
//Here you can iterate through all the sheets as well if you don't want to specify a sheetname
                    string excelSheetName = row["TABLE_NAME"].ToString();

                    //Matching the sheetname with input and schema
                    if (excelSheetName.ToUpper() == sheetName.ToUpper())
                    {
                        find = true;

                        //Setting the sheetname from schema
                        SheetNameExcel = excelSheetName;
                    }
                }
            }

            return find;
        }
}
}

SQL Script :

Table Creation :

/****** Object: Table [dbo].[CodeReview]    Script Date: 11/17/2011 11:27:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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

GO

SET ANSI_PADDING OFF
GO

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

Procedure :

/****** Object: StoredProcedure [dbo].[usp_AddExcelData]    Script Date: 11/17/2011 11:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[usp_AddExcelData]
(
@ExcelData varchar(max),
@Createdby varchar(50),
@dcmVer varchar(5)
)
AS
BEGIN
DECLARE @idoc int

DECLARE @MyTableVar table(
ID INT identity(1,1),
[Question_Description] nvarchar(255),
[Category] nvarchar(255),
[Reviewed] nvarchar(255),
[ReviewerComments] nvarchar(255),
[DevelopmentStatus] nvarchar(255),
[DeveloperComments] nvarchar(255),
CreatedBy Nvarchar(50),
dcmVer nvarchar(5)
)

EXEC sp_xml_preparedocument @idoc OUTPUT, @ExcelData
print @idoc

INSERT INTO @MyTableVar ([Question_Description],[Category],[Reviewed],[ReviewerComments],[DevelopmentStatus],[DeveloperComments])
SELECT [Question_Description],[Category],[Reviewed],[ReviewerComments],[DevelopmentStatus],[DeveloperComments]
FROM OPENXML (@idoc, '/NewDataSet/Table', 1)
WITH
(Question_Description nvarchar(255) './Question_Description',
Category nvarchar(255) './Category',
Reviewed nvarchar(255) './Reviewed',
ReviewerComments nvarchar(255) './ReviewerComments',
DevelopmentStatus nvarchar(255) './DevelopmentStatus',
DeveloperComments nvarchar(255) './DeveloperComments'
)

EXEC sp_xml_removedocument @idoc

Declare @TotalCount AS INT
Declare @i as INT
Declare @MaxID as INT

Update @MyTableVar set CreatedBy=@Createdby,dcmVer=@dcmVer

SET @i=1
SET @TotalCount=(select count(*) from @MyTableVar)
SET @MaxID=(select max(QID) from CodeReview)

WHILE @i<=@TotalCount
BEGIN
    Insert CodeReview SELECT [Question_Description]
      ,[Category]
      ,[Reviewed]
      ,[ReviewerComments]
      ,[DevelopmentStatus]
      ,[DeveloperComments],GETDATE(),CreatedBy,dcmVer from @MyTableVar where id=@i

    SET @i = @i + 1
END

Select * from CodeReview where QID > ISNULL(@MaxID,0)

END

GO

ASHIS KUMAR DAS

Welcome