Deleting Multiple Rows in a GridView

The sample makes use of the Northwind database. We will be pulling data from the Employee table. For this sample to work, drop all the Foreign Key relationships on the Employee Table. To do so, in Sql Server Management Studio, browse to the Northwind database and open the Employee table in design view. Right click in the Table designer on the right hand side and choose ‘Relationships’. Select all the relationships like FK_Orders_Employees,  FK_EmployeeTerritories_Employees etc and delete them. This step is necessary as we will get a constraint violation exception if we do not do so.

Once we are through with the task of removing the relationships in the Employee table, let us explore the steps to create a gridview with functionality to delete multiple rows at a time.

Perform the following steps :

Step 1: Create an .aspx page and add a GridView and a SqlDataSource control to it.

Step 2: Configure the connection of SqlDataSource to point to the Northwind database.  Create queries for the Select and Delete commands. The resultant code will look similar as given below :

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"

    SelectCommand="SELECT EmployeeID, LastName, City FROM Employees"

    DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID"

    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" >

       <DeleteParameters>

           <asp:Parameter Name="EmployeeID" />

       </DeleteParameters>

</asp:SqlDataSource>                                   

Step 3: Once the SqlDataSource has been configured, bind the gridview with this data source.

Step 4: To create a checkbox in each row, follow these steps:

1.    Create a TemplateField inside the <Columns> to add custom content to each column.

2.    Inside the TemplateField, create an ItemTemplate with a CheckBox added to it.

<asp:TemplateField>

       <ItemTemplate>

             <asp:CheckBox ID="chkRows" runat="server"/>

      </ItemTemplate>

 </asp:TemplateField>

This will add a checkbox to each row in the grid.

Step 5: Add a button control, and rename it to btnMultipleRowDelete.

The resultant markup in the design view will look similar to the code below :

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">

  

   <Columns>

      <asp:TemplateField>

          <ItemTemplate>

            <asp:CheckBox ID="cbRows" runat="server"/>

          </ItemTemplate>

       </asp:TemplateField>

 

<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />

<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />

<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />

   </Columns>

</asp:GridView>

 

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"

SelectCommand="SELECT EmployeeID, LastName, City FROM Employees"

DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID"

ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" >

   <DeleteParameters>

       <asp:Parameter Name="EmployeeID" />

   </DeleteParameters>

</asp:SqlDataSource>

 

<asp:Button

   ID="btnMultipleRowDelete"

   OnClick="btnMultipleRowDelete_Click"

   runat="server"

   Text="Delete Rows" />

 

In Code behind file (.cs) for C# and (.vb) for VB.NET, code the button click event. Our code will first loop through all the rows in the GridView. If a row is checked, the code retrieves the EmployeeID and passes the selected value to the Delete Command.

C#

 

 

protected void btnMultipleRowDelete_Click(object sender, EventArgs e)

{

        // Looping through all the rows in the GridView

        foreach (GridViewRow row in GridView1.Rows)

        {

            CheckBox checkbox = (CheckBox)row.FindControl("cbRows");

 

            //Check if the checkbox is checked.

//value in the HtmlInputCheckBox's Value property is set as the //value of the delete command's parameter.

            if (checkbox.Checked)

            {

                // Retreive the Employee ID

int employeeID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);

// Pass the value of the selected Employye ID to the Delete //command.

SqlDataSource1.DeleteParameters["EmployeeID"].DefaultValue = employeeID.ToString();

                SqlDataSource1.Delete();

            }

        }

 }

 Run the code, and select a few rows in the grid. ‘Delete Rows’ button, the selected rows get deleted. Rather than deleting rows one at a time, deleting them in a batch is a good practice

 

 

Binding CheckBoxList Control the ADO.NET way.

Note that in this demo, I’m using the Northwind database. Now let’s set up the connection string.


STEP 1: Setting up the Connection string

In your webconfig file set up the connection string there as shown below:

<connectionStrings>

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

Note: DBConnection is the name of the Connection String that we can use as a reference in our codes.

In code behind, you can reference that connection string like this:

private string GetConnectionString()

{

   return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

}

STEP 2: Binding the CheckBoxList Control

Same procedure as what we did for DropDownList and ListBox..

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Binding CheckBoxList</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:CheckBoxList ID="CheckBoxList1" runat="server" Width="200px">

        </asp:CheckBoxList>   

    </div>

    </form>

</body>

</html>

Here are relevant codes for binding the CheckBoxList in the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Collections.Specialized;

using System.Text;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindCheckBoxList();

        }

    }

    private string GetConnectionString()

    {

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

    }

    private void BindCheckBoxList()

    {

        DataTable dt = new DataTable();

        SqlConnection connection = new SqlConnection(GetConnectionString());

        try

        {

            connection.Open();

            string sqlStatement = "SELECT Top(20)* FROM Customers";

            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);

            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                CheckBoxList1.RepeatColumns = 4; // set the number of columns in the CheckBoxList

                CheckBoxList1.DataSource =dt;

                CheckBoxList1.DataTextField = "ContactName"; // the items to be displayed in the list items

                CheckBoxList1.DataValueField = "CustomerID"; // the id of the items displayed

                CheckBoxList1.DataBind();

            }

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Fetch Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally

        {

            connection.Close();

        }

    }

}

Binding ListBox Control the ADO.NET way.

Note that in this demo, I’m using the Northwind database. Now let’s set up the connection string.


STEP 1: Setting up the Connection string

In your webconfig file set up the connection string there as shown below:

<connectionStrings>

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

Note: DBConnection is the name of the Connection String that we can use as a reference in our codes.

In code behind, you can reference that connection string like this:

private string GetConnectionString()

{

   return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

}

STEP 2: Binding the ListBox Control

We can bind the ListBox control the same way as what we did for binding the DropDownList by simply setting its DataTextField and DataValueField.

Now let’s grab a ListBox control from the visual studio toolbox. The mark up should look something like this:

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Binding ListBox</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

       <asp:ListBox ID="ListBox1" runat="server"Height="200px" Width="100px">

       </asp:ListBox>

    </div>

    </form>

</body>

</html>

 

And here are the relevant codes for binding the ListBox in the code behind.

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Collections.Specialized;

using System.Text;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindListBox();

        }

    }

    private string GetConnectionString()

    {

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

    }

    private void BindListBox()

    {

        DataTable dt = new DataTable();

        SqlConnection connection = new SqlConnection(GetConnectionString());

        try

        {

            connection.Open();

            string sqlStatement = "SELECT Top(20)* FROM Customers";

            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);

            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                ListBox1.DataSource =dt;

                ListBox1.DataTextField = "ContactName"; // the items to be displayed in the list items

                ListBox1.DataValueField = "CustomerID"; // the id of the items displayed

                ListBox1.DataBind();

            }

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Fetch Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally

        {

            connection.Close();

        }

    }

}

Binding DropDownList Control the ADO.NET way.

Note that in this demo, I’m using the Northwind database. Now let’s set up the connection string.


STEP 1: Setting up the Connection string

In your webconfig file set up the connection string there as shown below:

<connectionStrings>

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

Note: DBConnection is the name of the Connection String that we can use as a reference in our codes.

In code behind, you can reference that connection string like this:

private string GetConnectionString()

{

   return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

}

Since we are done setting up the connection string then we can start populating the Controls, first let’s start populating the DropDownList.

STEP 2: Populating the DropDownList

To start, grab a DropDownList control from the visual studio ToolBox and place it the WebForm. The ASPX source should look something like this:

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Binding DropDownList</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:DropDownList ID="DropDownList1" runat="server">

        </asp:DropDownList>

    </div>

    </form>

</body>

</html>

 Here’s the code block for binding the DropDownList in the code behind:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Collections.Specialized;

using System.Text;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindDropDownList();

        }

    }

    private string GetConnectionString()

    {

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

    }

    private void BindDropDownList()

    {

        DataTable dt = new DataTable();

        SqlConnection connection = new SqlConnection(GetConnectionString());

        try

        {

            connection.Open();

            string sqlStatement = "SELECT Top(20)* FROM Customers";

            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);

            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                DropDownList1.DataSource =dt;

                DropDownList1.DataTextField = "ContactName"; // the items to be displayed in the list items

                DropDownList1.DataValueField = "CustomerID"; // the id of the items displayed

                DropDownList1.DataBind();

            }

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Fetch Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally

        {

            connection.Close();

        }

    }

}