Delete records from ASP.Net GridView using CheckBox

RichardSa
 
on Nov 29, 2022 04:46 AM
511 Views

I am displaying my records in GridView based on a column named ("Name"), but my primary key is the column ("Id"). May I please ask if it is possible to delete a GridView row record based on the column "Name"?

If yes, may I please know how to do this? And will it have effect of the primary key (which in my case is the column ("Id").

I don't know how to do this but here is how my Stored Procedure looks like and my GridView display code.

Any assistance please?

Srored Procedure

 CREATE PROCEDURE [dbo].[UserData]
    @Name NVARCHAR(MAX)
    ,@PageIndex INT
    ,@PageSize INT
    ,@RecordCount INT OUT
 AS
 BEGIN
     SELECT ROW_NUMBER() OVER(ORDER BY Name) RowNumber
        ,Id
        ,UserName  
        ,email
        ,RegisteredDate    
     INTO #Temp 
     FROM UserTable
  WHERE Name = @Name
    
  SELECT @RecordCount = COUNT(*) FROM #Temp 
    
  SELECT * FROM #Temp 
     WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1 
       
     DROP TABLE #Temp 
    
  END
 RETURN 0

Gridview

private int PageSize = 5;
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.GetCustomersPageWise(1);
    }
}
 
private void GetCustomersPageWise(int pageIndex)
{
    using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security = True"))
    {
        using (SqlCommand cmd = new SqlCommand("UserData ", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Name", Textboxname.Text.Trim());
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", PageSize);
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    con.Close();
                    int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, pageIndex);
                }
            }
        }
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Nov 30, 2022 12:44 AM

Hi RichardSa,

Please refer below sample.

HTML

<asp:ScriptManager runat="server" />
<asp:UpdatePanel ID="panel" runat="server">
    <ContentTemplate>
        <div class="container-fluid p-3 mb5 bg-white rounded" id="card" style="margin: 0 auto; padding: 10px; border: 1.3px solid #e4e7e8;">
            <div class="grid-corner" style="width: 100%; background-color: white; font-size: 9pt; margin: 0 auto; padding: 5px; overflow: scroll;">
                <asp:GridView ID="GridView1" runat="server" GridLines="None" DataKeyNames="CustomerID" AllowPaging="true"
                    HeaderStyle-ForeColor="#05214d" HeaderStyle-Font-Bold="true" HeaderStyle-Font-Size="11pt" Font-Size="10pt"
                    AutoGenerateColumns="false" OnSelectedIndexChanged="OnSelectedIndexChanged" OnRowDataBound="OnRowDataBound"
                    OnPageIndexChanging="OnPageIndexChanging" class="table" Width="100%">
                    <EmptyDataTemplate>
                        <div style="text-align: center; font-weight: bolder;">
                            <hr />
                            <hr />
                            <asp:Label ID="labelTemp" runat="server" Font-Size="12pt" Font-Bold="true" Text="No Record"></asp:Label>
                            <hr />
                            <hr />
                        </div>
                    </EmptyDataTemplate>
                    <Columns>
                        <asp:TemplateField>
                            <HeaderTemplate>
                                <asp:CheckBox ID="chkAll" runat="server" />
                            </HeaderTemplate>
                            <ItemTemplate>
                                <asp:CheckBox ID="checker" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="CustomerID" HeaderText="Reference" />
                        <asp:BoundField DataField="ContactName" HeaderText="Created By" />
                        <asp:BoundField DataField="CompanyName" HeaderText="Client" />
                    </Columns>
                </asp:GridView>
                <div style="float: right; font-size: 10pt; margin-right: 1%;">
                    Showing&nbsp;Page&nbsp;
                    <asp:Label ID="lblPageIndex" runat="server" Text="Label" />
                    &nbsp;of&nbsp;
                    <asp:Label ID="lblTotalPage" runat="server" />
                    (<asp:Label ID="lblTotal" runat="server" />
                    Records)&nbsp;&nbsp;
                    <div class="dvPager">
                        <asp:Repeater ID="rptPager" runat="server">
                            <ItemTemplate>
                                <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                                    CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
                                    OnClick="Change_Page" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
                            </ItemTemplate>
                        </asp:Repeater>
                    </div>
                </div>
                <br />
                <br />
            </div>
        </div>
    </ContentTemplate>
</asp:UpdatePanel>
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />

Namespaces

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Code

 

private int PageSize = 5;
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.GetCustomersPageWise(1);
    }
}

private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", PageSize);
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    con.Close();
                    int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, pageIndex);
                }
            }
        }
    }
}

protected void Change_Page(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetCustomersPageWise(pageIndex);
}

protected void Page_Changed(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    //this.CustomersPage(pageIndex);
}

private void PopulatePager(int recordCount, int currentPage)
{
    double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
    int pageCount = (int)Math.Ceiling(dblPageCount);
    List<ListItem> pages = new List<ListItem>();
    if (pageCount > 0)
    {
        if (currentPage != 1)
        {
            pages.Add(new ListItem("Prev", (currentPage - 1).ToString()));
        }
        if (pageCount < 4)
        {
            for (int i = 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
        }
        else if (currentPage < 4)
        {
            for (int i = 1; i <= 4; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
        }
        else if (currentPage > pageCount - 4)
        {
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
            for (int i = currentPage - 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
        }
        else
        {
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
            for (int i = currentPage - 2; i <= currentPage + 2; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
        }
        if (currentPage != pageCount)
        {
            pages.Add(new ListItem("Next", (currentPage + 1).ToString()));
        }
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();

    lblPageIndex.Text = currentPage.ToString();
    lblTotalPage.Text = ((recordCount / PageSize) + ((recordCount % PageSize) > 0 ? 1 : 0)).ToString();
    lblTotal.Text = recordCount.ToString();
}

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.GetCustomersPageWise(1);
}

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        for (int i = 1; i < e.Row.Cells.Count; i++)
        {
            e.Row.Cells[i].Attributes["onclick"] = Page.ClientScript.GetPostBackClientHyperlink(GridView1, "Select$" + e.Row.RowIndex);
            e.Row.Cells[i].Style.Add("cursor", "pointer");
            e.Row.Cells[i].ToolTip = "View Details.";
        }
    }
}

protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
    foreach (GridViewRow row in GridView1.Rows)
    {
        if (row.RowIndex == GridView1.SelectedIndex)
        {
            int index = GridView1.SelectedIndex;
            Session["RowIndex"] = GridView1.DataKeys[index].Value.ToString();
            Response.Redirect("details.aspx");
        }
    }
}

protected void btnDelete_Click(object sender, EventArgs e)
{
    int i = 0;
    foreach (GridViewRow row in GridView1.Rows)
    {
        CheckBox chkdelete = (CheckBox)row.FindControl("checker");
        if (chkdelete.Checked)
        {
            /*Uncomment the below delete code.*/
            //string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            //using (SqlConnection con = new SqlConnection(constring))
            //{
            //    using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId=@CustomerId", con))
            //    {
            //        cmd.Parameters.AddWithValue("@CustomerID", i);
            //        con.Open();
            //        cmd.ExecuteNonQuery();
            //        con.Close();
            //    }
            //}
            string message = "Customer " + row.Cells[1].Text + " has been deleted.";
            ClientScript.RegisterStartupScript(this.GetType(), "alert" + i, "alert('" + message + "');", true);
        }
        i++;
    }
}

Screenshot