Wednesday, 18 December 2013

Gridview Example with helper class

web.config

 <connectionStrings>
    <add name="connectionString" connectionString="Data
     Source=WINDOWSSERVER\MSSQLSERVER2008;Database=test;UID=test;PWD=test123"
      providerName="System.Data.SqlClient" />
</connectionStrings>

 <pages>
        <controls>
          <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
          <add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
          <add namespace="AjaxControlToolkit" assembly="AjaxControlToolkit" tagPrefix="ajaxToolkit" />
        </controls>
</pages>

App_code -> Helper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for Helpert
/// </summary>
namespace DbHelper
{
    public class Helper
    {
        SqlConnection Conn = new SqlConnection();

        public void createConnection()
        {
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
        }

        public DataTable ExecuteSelectCommand(string CommandName, CommandType cmdType, List<KeyValuePair<string, string>> Param)
        {
            createConnection();
            SqlCommand cmd = null;
            DataTable table = new DataTable();

            cmd = Conn.CreateCommand();

            cmd.CommandType = cmdType;
            cmd.CommandText = CommandName;
            for (int i = 0; i < Param.Count; i++)
            {
                SqlParameter param = new SqlParameter(Param[i].Key, Param[i].Value);
                cmd.Parameters.Add(param);
            }
            try
            {
                Conn.Open();

                SqlDataAdapter da = null;
                using (da = new SqlDataAdapter(cmd))
                {
                    da.Fill(table);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                Conn.Close();
            }

            return table;
        }

        public object ExecuteNonQuery(string CommandName, CommandType cmdType, List<KeyValuePair<string, string>> pars)
        {
            createConnection();
            SqlCommand cmd = null;
            object obj = null;

            cmd = Conn.CreateCommand();

            cmd.CommandType = cmdType;
            cmd.CommandText = CommandName;
            for (int i = 0; i < pars.Count; i++)
            {
                SqlParameter param = new SqlParameter(pars[i].Key, pars[i].Value);
                cmd.Parameters.Add(param);
            }

            try
            {
                Conn.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                Conn.Close();
            }

            return obj;
        }
    }
}

List Employee.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <div>
            <asp:Button ID="btnAdd" runat="server" Text="Add" onclick="btnAdd_Click" />
        </div>
        <asp:GridView ID="GvEmployee" runat="server" AllowPaging="true" PageSize="5" AllowSorting="true"
            AutoGenerateColumns="false" Width="50%" DataKeyNames="Id"
            OnSorting="GvEmployee_Sorting"
            onpageindexchanging="GvEmployee_PageIndexChanging"
            onrowcommand="GvEmployee_RowCommand" onrowdeleting="GvEmployee_RowDeleting">
            <AlternatingRowStyle BackColor="#BFE4FF" />
            <PagerStyle BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            <HeaderStyle Height="30px" BackColor="#6DC2FF" Font-Size="15px" BorderColor="#CCCCCC"
                BorderStyle="Solid" BorderWidth="1px" />
            <RowStyle Height="20px" Font-Size="13px" BorderColor="#CCCCCC" BorderStyle="Solid"
                BorderWidth="1px" />
            <Columns>
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField DataField="Designation" HeaderText="Designation" SortExpression="Designation" />
                <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
                <asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />
                <asp:BoundField DataField="CityName" HeaderText="City Name" SortExpression="CityName" />
                <asp:TemplateField HeaderText="Edit" ItemStyle-Width="30px" HeaderStyle-Width="30px">
                    <ItemStyle HorizontalAlign="Center" />
                    <ItemTemplate>
                        <asp:LinkButton ID="lnkBtnEdit" runat="server" Text="Edit" Style="background-color: Transparent;"
                            CommandName="Edit" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "Id") %>'/>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Delete" ItemStyle-Width="30px" HeaderStyle-Width="30px">
                    <ItemStyle HorizontalAlign="Center" />
                    <ItemTemplate>
                        <asp:LinkButton ID="lnkBtnDelete" Text="Delete" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "Id") %>' runat="server" Style="background-color: Transparent;"
                            CommandName="Delete" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

ListEmployee.aspx.cs

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


public partial class _Default : System.Web.UI.Page
{
    SqlConnection Conn = new SqlConnection();
    string Sort_Direction = "Employee Name ASC";
    protected void Page_Load(object sender, EventArgs e)
    {
        Conn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
        if (!IsPostBack)
        {
            ViewState["SortExpr"] = Sort_Direction;
            BindGrid();
        }
    }

    public void BindGrid()
    {
        KeyValuePair<string, string>[] array1;
        List<KeyValuePair<string, string>> list1 = new List<KeyValuePair<string, string>>();
        list1.Add(new KeyValuePair<string, string>("@mode", "SelectAll"));
        //list1.Add(new KeyValuePair<string, string>("two", 2));
        array1 = list1.ToArray();
        Helper objHelper = new Helper();
        DataTable dtgrid = objHelper.ExecuteSelectCommand("SP_Employee", CommandType.StoredProcedure, list1);
        GvEmployee.DataSource = dtgrid;
        GvEmployee.DataBind();
        Session["Table"] = dtgrid;
    }

    protected void GvEmployee_Sorting(object sender, GridViewSortEventArgs e)
    {
        DataTable dtSortTable = Session["Table"] as DataTable;

        if (dtSortTable != null)
        {
            DataView dvSortedView = new DataView(dtSortTable);

            dvSortedView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);

            GvEmployee.DataSource = dvSortedView;
            GvEmployee.DataBind();
        }
    }
    private string GetSortDirection(string column)
    {

        // By default, set the sort direction to ascending.
        string sortDirection = "ASC";

        // Retrieve the last column that was sorted.
        string sortExpression = ViewState["SortExpression"] as string;

        if (sortExpression != null)
        {
            // Check if the same column is being sorted.
            // Otherwise, the default value can be returned.
            if (sortExpression == column)
            {
                string lastDirection = ViewState["SortDirection"] as string;
                if ((lastDirection != null) && (lastDirection == "ASC"))
                {
                    sortDirection = "DESC";
                }
            }
        }
        // Save new values in ViewState.
        ViewState["SortDirection"] = sortDirection;
        ViewState["SortExpression"] = column;

        return sortDirection;
    }

    protected void GvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GvEmployee.PageIndex = e.NewPageIndex;
        BindGrid();
    }
    protected void GvEmployee_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("Edit"))
        {
            Response.Redirect("AddEmployee.aspx?type=edit&id=" + Convert.ToString(e.CommandArgument));
        }
        if (e.CommandName.Equals("Delete"))
        {
            KeyValuePair<string, string>[] array1;
            List<KeyValuePair<string, string>> list1 = new List<KeyValuePair<string, string>>();
            list1.Add(new KeyValuePair<string, string>("@mode", "Delete"));
            list1.Add(new KeyValuePair<string, string>("@Id", Convert.ToString(e.CommandArgument)));
            array1 = list1.ToArray();
            Helper objHelper = new Helper();
            int res = Convert.ToInt32(objHelper.ExecuteNonQuery("SP_Employee", CommandType.StoredProcedure, list1));
            BindGrid();
          
        }
    }
    protected void GvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        Response.Redirect("AddEmployee.aspx");
    }
}

Add Employee.aspx

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

<!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></title>
</head>
<body>
    <form id="AddEmp" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <div>
        <div>
            <div>
                Emp Name:
            </div>
            <div>
                <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvName" ValidationGroup="Employee" runat="server"
                    Display="None" ControlToValidate="txtName" ErrorMessage="<b>Required Field Missing</b><br />Please Enter Name."
                    SetFocusOnError="True" />
                <ajaxToolkit:ValidatorCalloutExtender ID="vceName" runat="Server" TargetControlID="rfvName" />
            </div>
        </div>
        <div>
            <div>
                Birthdate:
            </div>
            <div>
                <asp:TextBox ID="txtBirthdate" runat="server"></asp:TextBox>
                <ajaxToolkit:CalendarExtender ID="ceDateGiven" TargetControlID="txtBirthdate" runat="server"
                    PopupPosition="TopLeft" />
                <asp:RequiredFieldValidator ID="rfvBirthdate" ValidationGroup="Employee" runat="server"
                    Display="None" ControlToValidate="txtBirthdate" ErrorMessage="<b>Required Field Missing</b><br />Please Enter Date."
                    SetFocusOnError="True" />
                <ajaxToolkit:ValidatorCalloutExtender ID="vceBirthdate" runat="Server" TargetControlID="rfvBirthdate" />
            </div>
        </div>
        <div>
            <div>
                Designation
            </div>
            <div>
                <asp:TextBox ID="txtDesignation" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvDesignation" ValidationGroup="Employee" runat="server"
                    Display="None" ControlToValidate="txtDesignation" ErrorMessage="<b>Required Field Missing</b><br />Please Enter Designation."
                    SetFocusOnError="True" />
                <ajaxToolkit:ValidatorCalloutExtender ID="vceDesignation" runat="Server" TargetControlID="rfvDesignation" />
            </div>
        </div>
        <div>
            <div>
                Department
            </div>
            <div>
                <asp:TextBox ID="txtDepartment" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvDepartment" ValidationGroup="Employee" runat="server"
                    Display="None" ControlToValidate="txtDepartment" ErrorMessage="<b>Required Field Missing</b><br />Please Enter Department."
                    SetFocusOnError="True" />
                <ajaxToolkit:ValidatorCalloutExtender ID="vceDepartment" runat="Server" TargetControlID="rfvDepartment" />
            </div>
        </div>
        <div>
            <div>
                Salary
            </div>
            <div>
                <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvSalary" ValidationGroup="Employee" runat="server"
                    Display="None" ControlToValidate="txtSalary" ErrorMessage="<b>Required Field Missing</b><br />Please Enter Salary."
                    SetFocusOnError="True" />
                <ajaxToolkit:ValidatorCalloutExtender ID="vceSalary" runat="Server" TargetControlID="rfvSalary" />
            </div>
        </div>
        <div>
            <div>
                State
            </div>
            <div>
                <asp:DropDownList ID="ddlState" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlState_SelectedIndexChanged">
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvState" ValidationGroup="Employee" runat="server"
                    ErrorMessage="<b>Required Field Missing</b><br />Please Select State." ControlToValidate="ddlState"
                    InitialValue="0" Display="None" />
                <ajaxToolkit:ValidatorCalloutExtender ID="vceState" runat="Server" TargetControlID="rfvState" />
            </div>
        </div>
        <div>
            <div>
                City
            </div>
            <div>
                <asp:UpdatePanel ID="upddlCity" runat="server">
                    <Triggers>
                        <asp:AsyncPostBackTrigger ControlID="ddlState" EventName="SelectedIndexChanged" />
                    </Triggers>
                    <ContentTemplate>
                        <asp:DropDownList ID="ddlCity" runat="server">
                            <asp:ListItem Value="0">--select City--</asp:ListItem>
                        </asp:DropDownList>
                    </ContentTemplate>
                </asp:UpdatePanel>
                <asp:RequiredFieldValidator ID="rfvCity" ValidationGroup="Employee" runat="server"
                    ErrorMessage="<b>Required Field Missing</b><br />Please Select City." ControlToValidate="ddlCity"
                    InitialValue="0" Display="None" />
                <ajaxToolkit:ValidatorCalloutExtender ID="vceCity" runat="Server" TargetControlID="rfvCity" />
            </div>
        </div>
        <div>
            <asp:Button ID="btnSave" runat="server" Text="Save" ValidationGroup="Employee" OnClick="btnSave_Click" />&nbsp;
            <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" />
        </div>
    </div>
    </form>
</body>
</html>

AddEmployee.aspx.cs

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

public partial class AddEmployee : System.Web.UI.Page
{
    string strQSID, strQSType = string.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!string.IsNullOrEmpty(Request.QueryString["id"]))
            strQSID = Request.QueryString["id"];

        if (!string.IsNullOrEmpty(Request.QueryString["type"]))
            strQSType = Request.QueryString["type"];

        if (!IsPostBack)
        {
            bindState();
            if (strQSType.Equals("edit"))
            {
                bindState();
                GetData(Convert.ToInt32(strQSID));
            }
        }
    }
    public void GetData(int Id)
    {
        if (Id > 0)
        {
            KeyValuePair<string, string>[] array1;
            List<KeyValuePair<string, string>> list1 = new List<KeyValuePair<string, string>>();
            list1.Add(new KeyValuePair<string, string>("@mode", "SelectById"));
            list1.Add(new KeyValuePair<string, string>("@Id",Convert.ToString(Id)));
            Helper objHepler = new Helper();
            DataTable dtEmp = objHepler.ExecuteSelectCommand("SP_Employee", CommandType.StoredProcedure, list1);
            if (dtEmp != null)
            {
                txtName.Text = dtEmp.Rows[0]["Name"].ToString();
                DateTime dt = Convert.ToDateTime(dtEmp.Rows[0]["Birthdate"]);
                txtBirthdate.Text = dt.Date.ToString("MM/dd/yyyy");
                txtDesignation.Text = dtEmp.Rows[0]["Designation"].ToString();
                txtDepartment.Text = dtEmp.Rows[0]["Department"].ToString();
                txtSalary.Text = dtEmp.Rows[0]["Salary"].ToString();
                ddlState.SelectedValue = dtEmp.Rows[0]["StateId"].ToString();
                bindCity(Convert.ToInt32(ddlState.SelectedValue));
                ddlCity.SelectedValue = dtEmp.Rows[0]["CityID"].ToString();
            }
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (strQSType.Equals("edit"))
            Savedata("Update");
        else
            Savedata("Insert");
    }

    public void Savedata(string Mode)
    {
        KeyValuePair<string, string>[] array1;
        List<KeyValuePair<string, string>> list1 = new List<KeyValuePair<string, string>>();
        list1.Add(new KeyValuePair<string, string>("@mode", Mode));
        if(Convert.ToInt32(strQSID) > 0)
            list1.Add(new KeyValuePair<string, string>("@Id",Convert.ToString(strQSID)));
        list1.Add(new KeyValuePair<string, string>("@Name", txtName.Text));
        list1.Add(new KeyValuePair<string, string>("@Birthdate", txtBirthdate.Text));
        list1.Add(new KeyValuePair<string, string>("@Designation", txtDesignation.Text));
        list1.Add(new KeyValuePair<string, string>("@Department", txtDepartment.Text));
        list1.Add(new KeyValuePair<string, string>("@Salary", txtSalary.Text));
        list1.Add(new KeyValuePair<string, string>("@stateId", Convert.ToString(ddlState.SelectedItem.Value)));
        list1.Add(new KeyValuePair<string, string>("@CityID", Convert.ToString(ddlCity.SelectedItem.Value)));
        Helper objHelper = new Helper();
        int res = Convert.ToInt32(objHelper.ExecuteNonQuery("SP_Employee", CommandType.StoredProcedure, list1));
        if (res > 0)
            Response.Redirect("ListEmployee.aspx");
    }

    public void bindCity(int stateId)
    {
        KeyValuePair<string, string>[] array1;
        List<KeyValuePair<string, string>> list1 = new List<KeyValuePair<string, string>>();
        list1.Add(new KeyValuePair<string, string>("@mode", "SelectCity"));
        list1.Add(new KeyValuePair<string, string>("@stateId", Convert.ToString(stateId)));
        array1 = list1.ToArray();
        Helper objHelper = new Helper();
        DataTable dtcity = objHelper.ExecuteSelectCommand("SP_Employee", CommandType.StoredProcedure, list1);
        if (dtcity != null)
        {
            ddlCity.DataSource = dtcity;
            ddlCity.DataTextField = "Cityname";
            ddlCity.DataValueField = "CityId";
            ddlCity.DataBind();

            ddlCity.Items.Insert(0, "--Select City--");
            ddlCity.Items[0].Value = "0";
        }
    }
    public void bindState()
    {
        KeyValuePair<string, string>[] array1;
        List<KeyValuePair<string, string>> list1 = new List<KeyValuePair<string, string>>();
        list1.Add(new KeyValuePair<string, string>("@mode", "SelectState"));
        array1 = list1.ToArray();
        Helper objHelper = new Helper();
        DataTable dtcity = objHelper.ExecuteSelectCommand("SP_Employee", CommandType.StoredProcedure, list1);
        if (dtcity != null)
        {
            ddlState.DataSource = dtcity;
            ddlState.DataTextField = "StateName";
            ddlState.DataValueField = "Stateid";
            ddlState.DataBind();

            ddlState.Items.Insert(0, "--Select state--");
            ddlState.Items[0].Value = "0";
        }
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        Response.Redirect("ListEmployee.aspx");
    }
    protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
    {
        bindCity(Convert.ToInt32(ddlState.SelectedValue));
    }
}


Store Procedure

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[SP_Employee]    Script Date: 12/19/2013 10:51:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
Create PROCEDURE [dbo].[SP_Employee]
    @mode varchar(20)='',
    @Id int=0,
    @Name varchar(50)='',
    @Designation varchar(50)='',
    @Department varchar(50)='',
    @Salary varchar(50)='',
    @stateId int=0,
    @CityID int=0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    If @mode = 'Insert'
    Begin
        insert into employees
        (
            Name,
            Designation,
            Department,
            Salary,
            StateId,
            CityID
        )
        Values
        (
            @Name,
            @Designation,
            @Department,
            @Salary,
            @stateId,
            @CityID
        )
        select SCOPE_IDENTITY() as returnval
    End
   
    If @mode = 'Update'
    Begin
        update employees set
            Name = @Name,
            Designation = @Designation,
            Department = @Department,
            Salary = @Salary,
            StateId=@stateId,
            CityID = @CityID
        where Id=@Id
        select @Id as returnval
    End
   
    -- SP_Employee  @mode = 'SelectAll'
    If @mode = 'SelectAll'
    Begin
        select emp.*,C.CityName from employees emp inner join Citymasters C on C.CityID = emp.CityID
    End
   
    If @mode = 'SelectById'
    Begin
        select * from employees where Id = @Id
    End
    -- SP_Employee  @mode = 'Delete',@Id=2
    If @mode = 'Delete'
    Begin
        delete from employees where Id=@Id
        select @Id as Returnval
    End
   
    If @mode = 'SelectCity'
    Begin
        select * from Citymasters where StateId = @stateId
    End
   
    If @mode = 'SelectState'
    Begin
        select * from statemaster
    End
END
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)