Deleting Multiple Rows in a GridView

If you have used Hotmail or any other similar email client, you might have observed that  we have the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them. In this article, we will replicate this scenario for a gridview.

A gridview allows us to delete only a single row at a time. We will extend this functionality to select multiple rows and delete all of the selected rows in a single stroke. In this article, I assume that you are aware of creating asp.net web applications and have worked with 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" />

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.

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(); 

        }

    } 

} 

Hope this will Help you !!!

Data Access Layer – C#

This data access layer is used in turn by other program modules to access and manipulate the data within the data store without having to deal with the complexities inherent in this access.

For example, instead of using commands such as insert, delete, and update to access a specific table in a database, a class and a few stored procedures could be created in the database. The procedures would be called from a method inside the class, which would return an object containing the requested values. Or, the insert, delete and update commands could be executed within simple functions like registeruser or loginuser stored within the data access layer.

Web.config:

<connectionStrings>

    <add 

        name="ConnectionString" 

        connectionString="Data Source=server;InitialCatalog=TestDatabase;Persist Security Info=True;User ID=sa;Password=sa"

        providerName="System.Data.SqlClient" />

</connectionStrings>

Step 1 :  Create WebConfiguration.cs under App_Code with the following code:

using System;

using System.Configuration;

/// <;summary>

/// Repository for configuration settings

/// <;/summary>

public static class WebConfiguration

{

    // Caches the connection string

    private readonly static string dbConnectionString;

    // Caches the data provider name

    private readonly static string dbProviderName;

    // Initialize various properties in the constructor

    static WebConfiguration()

    {

        dbConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        dbProviderName = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;

    }

    // Returns the connection string for the database

    public static string DbConnectionString

    {

        get

        {

            return dbConnectionString;

        }

    }

    // Returns the data provider name

    public static string DbProviderName

    {

        get

        {

            return dbProviderName;

        }

    }

}

Step 2 : Create Class GenericDataAccess.cs with following code:

using System;

using System.Data;

using System.Data.Common;

using System.Configuration;

/// <;summary>

/// Class contains generic data access functionality to be accessed from

/// the business tier

/// <;/summary>

public class GenericDataAccess

{

    // static constructor

    static GenericDataAccess()

    {

    }    

    // execute a command and return the results as a DataTable object

    public DataTable ExecuteSelectCommand(DbCommand command)

    {

        // The DataTable to be returned

        DataTable table;

        // Execute the command making sure the connection gets closed in the end

        try

        {

            // Open the data connection

            command.Connection.Open();

            // Execute the command and save the results in a DataTable

            DbDataReader reader = command.ExecuteReader();

            table = new DataTable();

            table.Load(reader);

            // Close the reader

            reader.Close();

        }

        catch (Exception ex)

        {

            //Utilities.LogError(ex);

            throw ex;

        }

        finally

        {

            // Close the connection

            command.Connection.Close();

        }

        return table;

    }

    // execute an update, delete, or insert command

    // and return the number of affected rows

    public int ExecuteNonQuery(DbCommand command)

    {

        // The number of affected rows

        int affectedRows = -1;

        // Execute the command making sure the connection gets closed in the end

        try

        {

            // Open the connection of the command

            command.Connection.Open();

            // Execute the command and get the number of affected rows

            affectedRows = command.ExecuteNonQuery();

        }

        catch (Exception ex)

        {

            // Log eventual errors and rethrow them

            //Utilities.LogError(ex);

            throw ex;

        }

        finally

        {

            // Close the connection

            command.Connection.Close();

        }

        // return the number of affected rows

        return affectedRows;

    }

    // execute a select command and return a single result as a string

    public string ExecuteScalar(DbCommand command)

    {

        // The value to be returned

        string value = "";

        // Execute the command making sure the connection gets closed in the end

        try

        {

            // Open the connection of the command

            command.Connection.Open();

            // Execute the command and get the number of affected rows

            value = command.ExecuteScalar().ToString();

        }

        catch (Exception ex)

        {

            //Utilities.LogError(ex);

            //throw ex;

        }

        finally

        {

            // Close the connection

            command.Connection.Close();

        }

        // return the result

        return value;

    }

    // creates and prepares a new DbCommand object on a new connection

    public DbCommand CreateCommand()

    {

        // Obtain the database provider name

        string dataProviderName = WebConfiguration.DbProviderName;

        // Obtain the database connection string

        string connectionString = WebConfiguration.DbConnectionString;

        // Create a new data provider factory

        DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);

        // Obtain a database specific connection object

        DbConnection conn = factory.CreateConnection();

        // Set the connection string

        conn.ConnectionString = connectionString;

        // Create a database specific command object

        DbCommand comm = conn.CreateCommand();

        // Set the command type to stored procedure

        comm.CommandType = CommandType.StoredProcedure;

        // Return the initialized command object

        return comm;

    }

    //creates and prepares a new DbCommand object on a new connection(For QueryText)

    public DbCommand CreateCommand4QueryText()

    {

        // Obtain the database provider name

        string dataProviderName = WebConfiguration.DbProviderName;

        // Obtain the database connection string

        string connectionString = WebConfiguration.DbConnectionString;

        // Create a new data provider factory

        DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);

        // Obtain a database specific connection object

        DbConnection conn = factory.CreateConnection();

        // Set the connection string

        conn.ConnectionString = connectionString;

        // Create a database specific command object

        DbCommand comm = conn.CreateCommand();

        // Set the command type to stored procedure

        comm.CommandType = CommandType.Text;

        // Return the initialized command object

        return comm;

    }

}

Step 3: Create BAL Folder under App_Code and start creating your BAL Classes & Use it on Presentation layer

using System;

using System.Data.Common;

using PropertyPortal.Common;

using System.Data;

public class BAL_AddAdImage

{

    public BAL_AddAdImage()

    {

    }

    public static DataTable getPropertyImageAll(int PropertyID)

    {

        // get a configured DbCommand object

        GenericDataAccess gda = new GenericDataAccess();

        DbCommand comm = gda.CreateCommand();

        // set the stored procedure name

        comm.CommandText = "PropertyDB_GetPropertyImageDetails_All";

        // create a new parameter

        DbParameter param = comm.CreateParameter();

        param.ParameterName = "@PropertyID";

        param.Value = PropertyID;

        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);

        // return the result table

        DataTable table = gda.ExecuteSelectCommand(comm);

        return table;

    }

    public static string GetImagePath(int ImageID)

    {

        // get a configured DbCommand object

        GenericDataAccess gda = new GenericDataAccess();

        DbCommand comm = gda.CreateCommand();

        // set the stored procedure name

        comm.CommandText = "PropertyDB_GetPropertyImagePath";

        // create a new parameter

        DbParameter param = comm.CreateParameter();

        param.ParameterName = "@ImageID";

        param.Value = ImageID;

        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);

        string path = gda.ExecuteScalar(comm).ToString();

        return path;

    }

    public static bool Delete(int ImageID)

    {

        // get a configured DbCommand object

        GenericDataAccess gda = new GenericDataAccess();

        DbCommand comm = gda.CreateCommand();

        // set the stored procedure name

        comm.CommandText = "PropertyDB_PropertyImageDetails_Delete";

        // create a new parameter

        DbParameter param = comm.CreateParameter();

        param.ParameterName = "@ImageID";

        param.Value = ImageID;

        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);

        int retval = gda.ExecuteNonQuery(comm);

        if (retval >; 0)

            return true;

        else

            return false;

    }

}