Paging and Sorting in GridView without using Datasource control

Paging and Sorting in Gridview can be enabled in an ASP.NET by handling the PageIndexChanging and Sorting events of the Gridview

ASP.NET

 

In the web page add the gridview from the toolbox

<asp:GridView
ID=”gridView”
OnPageIndexChanging=”gridView_PageIndexChanging” 

OnSorting=”gridView_Sorting”
runat=”server” />

 

Code Behind

 

I have a function ConvertSortDirection to display the direction of sort.

private string ConvertSortDirection(SortDirection sortDireciton)
{
   string newSortDirection = String.Empty;

   switch (sortDirection)
   {
      case
SortDirection.Ascending:
         newSortDirection = “ASC”;
         break;

      case
SortDirection.Descending:
         newSortDirection = “DESC”;
         break;
   }

   return newSortDirection
}

 

PageIndexChanging

 

protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
   gridView.PageIndex = e.NewPageIndex;
   gridView.DataBind();
}

 

Sorting

protected void gridView_Sorting(object sender, GridViewSortEventArgs e)
{
   DataTable dataTable = gridView.DataSource as
DataTable;

   if (dataTable != null)
   {
      DataView dataView = new
DataView(dataTable);
      dataView.Sort = e.SortExpression + ” “ + ConvertSortDirectionToSql(e.SortDirection);

      gridView.DataSource = dataView;
      gridView.DataBind();
   }
}

 

Happy coding J

Deleting Multiple Rows in 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”
/>

 
 

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. I would encourage you to read Scott Mitchell’s article for the same.

 
 

Conclusion

 
 

By default, the gridview provides the functionality to delete a single row at a time. In this article, we explored how to extend the functionality of the grid view and delete multiple rows. I hope this article was useful and I thank you for viewing it.

Add, Update, Delete Records in a Gridview using SqlDataSource

By default, the GridView control doesn’t have support for inserting new records. However you can use the built-in edit or delete functionality of the GridView control. Let us explore how to insert new records and Update and Delete existing records in Gridview. Just copy and paste the code in your project. We will be using the ‘Categories’ table in the ‘Northwind’ database.

GridView.aspx

        <asp:GridView
ID=”GridView1″
runat=”server”
AutoGenerateColumns=”False”
DataKeyNames=”CategoryID”

            DataSourceID=”SqlDataSource1″
ShowFooter=”true”
AllowPaging=”True”
AllowSorting=”True”
OnRowCommand=”GridView1_RowCommand”>

            <Columns>

           
 

                <asp:CommandField
ShowDeleteButton=”True”
ShowEditButton=”True”/>               

                <asp:TemplateField
HeaderText=”CategoryID”
InsertVisible=”False”
SortExpression=”CategoryID”>

                    <EditItemTemplate>

                        <asp:Label
ID=”Label1″
runat=”server”
Text=’<%# Eval(“CategoryID”) %>‘></asp:Label>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label1″
runat=”server”
Text=’<%# Bind(“CategoryID”) %>‘></asp:Label>

                    </ItemTemplate>                  

                </asp:TemplateField>

                <asp:TemplateField
HeaderText=”CategoryName”
SortExpression=”CategoryName”>

                    <EditItemTemplate>

                        <asp:TextBox
ID=”TextBox1″
runat=”server”
Text=’<%# Bind(“CategoryName”) %>‘></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label2″
runat=”server”
Text=’<%# Bind(“CategoryName”) %>‘></asp:Label>

                    </ItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox
ID=”CategoryNameTextBox”
Runat=”server”></asp:TextBox>

                        </FooterTemplate>

                </asp:TemplateField>

                <asp:TemplateField
HeaderText=”Description”
SortExpression=”Description”>

                    <EditItemTemplate>

                        <asp:TextBox
ID=”TextBox2″
runat=”server”
Text=’<%# Bind(“Description”) %>‘></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label3″
runat=”server”
Text=’<%# Bind(“Description”) %>‘></asp:Label>

                    </ItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox
ID=”DescriptionTextBox”
Runat=”server”></asp:TextBox>

                    </FooterTemplate>                 

                </asp:TemplateField>

                <asp:templatefield>                  

                        <footertemplate>

                              <asp:linkbutton
id=”btnNew”
runat=”server”
commandname=”New”
text=”New”
/>

                        </footertemplate>

                  </asp:templatefield>

               
 

            </Columns>

        </asp:GridView>

 

//

        <asp:SqlDataSource
ID=”SqlDataSource1″
runat=”server”
ConnectionString=”Data Source=SUPROTIM;Initial Catalog=Northwind;Integrated Security=True”

            DeleteCommand=”DELETE FROM [Categories] WHERE [CategoryID] = @CategoryID”
InsertCommand=”INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)”

            ProviderName=”System.Data.SqlClient”
SelectCommand=”SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]”

            UpdateCommand=”UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = @Description WHERE [CategoryID] = @CategoryID”>

            <DeleteParameters>

                <asp:Parameter
Name=”CategoryID”
Type=”Int32″
/>

            </DeleteParameters>

            <UpdateParameters>

                <asp:Parameter
Name=”CategoryName”
Type=”String”
/>

                <asp:Parameter
Name=”Description”
Type=”String”
/>

                <asp:Parameter
Name=”CategoryID”
Type=”Int32″
/>

            </UpdateParameters>

            <InsertParameters>

                <asp:Parameter
Name=”CategoryName”
Type=”String”
/>

                <asp:Parameter
Name=”Description”
Type=”String”
/>

            </InsertParameters>

        </asp:SqlDataSource>

   
 

 
 

GridView.aspx.cs

protected
void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        SqlConnection conn = new
SqlConnection(

                    ConfigurationManager.ConnectionStrings[“NorthwindConnectionString”].ConnectionString);

        try

        {

            if (e.CommandName.Equals(“New”))

            {

                LinkButton btnNew = e.CommandSource as
LinkButton;

                GridViewRow row = btnNew.NamingContainer as
GridViewRow;

                if (row == null)

                {

                    return;

                }

                TextBox txtCatName = row.FindControl(“CategoryNameTextBox”) as
TextBox;

                TextBox txtDescription = row.FindControl(“DescriptionTextBox”) as
TextBox;               

                SqlCommand cmd = new
SqlCommand(

                    “INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)”,

                    conn);

                cmd.Parameters.AddWithValue(“CategoryName”, txtCatName.Text);

                cmd.Parameters.AddWithValue(“Description”,txtDescription.Text);

                conn.Open();

                if (cmd.ExecuteNonQuery() == 1)

                {

                    GridView1.DataBind();

                }

            }

        }

        catch (Exception ex)

        {

 
 

        }

        finally

        {

            conn.Close();

        }

    }

Web.config

<connectionStrings>

            <addname=NorthwindConnectionStringconnectionString=Data Source =.;Integrated Security = SSPI; Initial Catalog=Northwind;/>

           
 

</connectionStrings>

A scrollable ASP.NET Gridview with a fixed header

 

Introduction

ASP.NET Gridview has the provision of providing paging, set a particular pagesize etc.. But at times our clients are too adamant to demand for scrollable gridview with a steady header.Gridview as such doesn’t have a scrollable feature . But still a work around is possible. This is being discussed in this article that provides us a convenient scrollable grid with a steady header.

STEP 1- Scrollable GridView

Add the gridview into a asp:Panel control

<asp:Panel
runat=”server”
ScrollBars=”auto”
Width=”500px”>

            <asp:GridView
ID=”GridView1″
runat=”server”>

            </asp:GridView>

        </asp:Panel>

This Panel has a scroll bar feature so it tends to scroll the grid. But our problem is not yet solved. What is the next issue- Constant header.
The above code makes the whole panel content to scroll. But most of the application needs a constant header. Is it possible? This is explained in the next section 

STEP 2- Stable header
This can be done in 2 ways

1. Add the css to the Panel

01..fixedHeader

02. {

03. overflow: auto;

04. height: 145px;

05.  

06. }

07. table th

08. {

09.   

10. position: relative;

11.   

12. }

2. In the above way we have added style to the Panel as a whole.
Again we can achieve stable header by adding header style for the grid.
This is the style:

1..fixedHeader

2. {

3. background-color: aqua;

4. position: relative;

5. top: expression(this.offsetParent.scrollTop);

6. }


And the HTML becomes 

1.<HeaderStyle CssClass=”fixedHeader”
/>

 
 

Conclusion
This article deals with a scrollable gridview that has a stable header.Though this is not possible as such as the gridview doesnot have a scrollable property it can be done by a work around. I hope it helps.Happy Coding.

Thank you

Baimey