GridView Tutorial Series IV

Sorting a gridview that does not use any datasource control - Part 47

Suggested Videos 
Part 44 - Sorting a gridview that uses sqldatasource control
Part 45 - Sorting a gridview that uses objectdatasource control and a dataset
Part 46 - Sorting a gridview that uses objectdatasource control and business objects



In this video we will discuss about sorting a gridview control that does not use any datasource control.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.

Please watch Part 46, before proceeding with this video.



Step 1: Drag and drop a gridview on webform1.aspx

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }

    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees(string sortColumn)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                string sqlQuery = "Select * from tblEmployee";

                if (!string.IsNullOrEmpty(sortColumn))
                {
                    sqlQuery += " order by " + sortColumn;
                }

                SqlCommand cmd = new SqlCommand(sqlQuery, con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }
    }
}

Step 3: Generate event handler method, for Sorting event of GridView1 control.

Step 4: Copy and paste the following code in WebForm1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees("EmployeeId");
        GridView1.DataBind();
    }
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
    string strSortDirection =
        e.SortDirection == SortDirection.Ascending ? "ASC" : "DESC";

    GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees
        (e.SortExpression + " " + strSortDirection);
    GridView1.DataBind();
}

Run the application. Click the column header to sort data. The data gets sorted in ascending order. Now, click again to sort data in descending order. It does not work.

In our next video, we will discuss about correcting this issue.

Sorting an asp.net gridview in ascending and descending order - Part 48

Suggested Videos 
Part 45 - Sorting a gridview that uses objectdatasource control and a dataset
Part 46 - Sorting a gridview that uses objectdatasource control and business objects
Part 47 - Sorting a gridview that does not use any datasource control



Please watch Part 47, before proceeding with this video. The problem in Part 47 was, we were not able to sort the data in descending order. In this video we will discuss about fixing this issue.



Step 1: Drag and drop a gridview on webform1.aspx

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }

    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees(string sortColumn)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                string sqlQuery = "Select * from tblEmployee";

                if (!string.IsNullOrEmpty(sortColumn))
                {
                    sqlQuery += " order by " + sortColumn;
                }

                SqlCommand cmd = new SqlCommand(sqlQuery, con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }
    }
}

Step 3: Generate event handler method, for Sorting event of GridView1 control.

Step 4: Flip webform1.aspx to html source mode and set the following 2 custom attributes on GridView1 control.
CurrentSortField="EmployeeId" 
CurrentSortDirection="ASC"

At this point the HTML of your webform, should be as shown below.
<asp:GridView ID="GridView1"
    runat="server"
    AllowSorting="True" 
    onsorting="GridView1_Sorting"
    CurrentSortField="EmployeeId"
    CurrentSortDirection="ASC">
</asp:GridView>

Step 5: Copy and paste the following code in WebForm1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees("EmployeeId");
        GridView1.DataBind();
    }
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
    //Response.Write("Sort Expression = " + e.SortExpression);
    //Response.Write("<br/>");
    //Response.Write("Sort Direction = " + e.SortDirection.ToString());

    SortDirection sortDirection = SortDirection.Ascending;
    string sortField = string.Empty;

    SortGridview((GridView)sender, e, out sortDirection, out sortField);
    string strSortDirection = sortDirection == SortDirection.Ascending ? "ASC" : "DESC";

    GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees(e.SortExpression + " " + strSortDirection);
    GridView1.DataBind();
}
private void SortGridview(GridView gridView, GridViewSortEventArgs e, out SortDirection sortDirection, out string sortField)
{
    sortField = e.SortExpression;
    sortDirection = e.SortDirection;

    if (gridView.Attributes["CurrentSortField"] != null && gridView.Attributes["CurrentSortDirection"] != null)
    {
        if (sortField == gridView.Attributes["CurrentSortField"])
        {
            if (gridView.Attributes["CurrentSortDirection"] == "ASC")
            {
                sortDirection = SortDirection.Descending;
            }
            else
            {
                sortDirection = SortDirection.Ascending;
            }
        }

        gridView.Attributes["CurrentSortField"] = sortField;
        gridView.Attributes["CurrentSortDirection"] = (sortDirection == SortDirection.Ascending ? "ASC" : "DESC");
    }
}

How to include sort arrows when sorting an asp.net gridview control - Part 49

Suggested Videos
Part 46 - Sorting a gridview that uses objectdatasource control and business objects
Part 47 - Sorting a gridview that does not use any datasource control
Part 48 - Sorting an asp.net gridview in ascending and descending order



In this video we will discuss about including sort arrows, when sorting an asp.net gridview control. When the data is sorted in ascending order, we want to show UP ARROW and if the data is sorted in descending order we want to show DOWN ARROW.



Employee data sorted by name in ascending order


Employee data sorted by name in descending order


Please watch Part 48, before proceeding with this video.

Step 1: Drag and drop a gridview on webform1.aspx

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }

    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees(string sortColumn)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                string sqlQuery = "Select * from tblEmployee";

                if (!string.IsNullOrEmpty(sortColumn))
                {
                    sqlQuery += " order by " + sortColumn;
                }

                SqlCommand cmd = new SqlCommand(sqlQuery, con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }
    }
}

Step 3: Generate event handler methods, for the following events of GridView1 control
Sorting
RowCreated

Step 4: Flip webform1.aspx to html source mode and set the following 2 custom attributes on GridView1 control.
CurrentSortField="EmployeeId"
CurrentSortDirection="ASC"

At this point the HTML of your webform, should be as shown below.
<asp:GridView ID="GridView1" 
    runat="server" 
    AllowSorting="True" 
    onsorting="GridView1_Sorting"
    CurrentSortField="EmployeeId" 
    CurrentSortDirection="ASC"
    OnRowCreated="GridView1_RowCreated">
</asp:GridView>

Step 5: Copy and paste the following code in WebForm1.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees("EmployeeId");
                GridView1.DataBind();
            }
        }
        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            //Response.Write("Sort Expression = " + e.SortExpression);
            //Response.Write("<br/>");
            //Response.Write("Sort Direction = " + e.SortDirection.ToString());

            SortDirection sortDirection = SortDirection.Ascending;
            string sortField = string.Empty;

            SortGridview((GridView)sender, e, out sortDirection, out sortField);
            string strSortDirection = sortDirection == SortDirection.Ascending ? "ASC" : "DESC";

            GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees(e.SortExpression + " " + strSortDirection);
            GridView1.DataBind();
        }
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {
            if (GridView1.Attributes["CurrentSortField"] != null && GridView1.Attributes["CurrentSortDirection"] != null)
            {
                if (e.Row.RowType == DataControlRowType.Header)
                {
                    foreach (TableCell tableCell in e.Row.Cells)
                    {
                        if (tableCell.HasControls())
                        {
                            LinkButton sortLinkButton = null;
                            if (tableCell.Controls[0] is LinkButton)
                            {
                                sortLinkButton = (LinkButton)tableCell.Controls[0];
                            }

                            if (sortLinkButton != null && GridView1.Attributes["CurrentSortField"] == sortLinkButton.CommandArgument)
                            {
                                Image image = new Image();
                                if (GridView1.Attributes["CurrentSortDirection"] == "ASC")
                                {
                                    image.ImageUrl = "~/Images/up_arrow.png";
                                }
                                else
                                {
                                    image.ImageUrl = "~/Images/down_arrow.png";
                                }
                                tableCell.Controls.Add(new LiteralControl("&nbsp;"));
                                tableCell.Controls.Add(image);
                            }
                        }
                    }
                }
            }
        }
        private void SortGridview(GridView gridView, GridViewSortEventArgs e, out SortDirection sortDirection, out string sortField)
        {
            sortField = e.SortExpression;
            sortDirection = e.SortDirection;

            if (gridView.Attributes["CurrentSortField"] != null && gridView.Attributes["CurrentSortDirection"] != null)
            {
                if (sortField == gridView.Attributes["CurrentSortField"])
                {
                    if (gridView.Attributes["CurrentSortDirection"] == "ASC")
                    {
                        sortDirection = SortDirection.Descending;
                    }
                    else
                    {
                        sortDirection = SortDirection.Ascending;
                    }
                }

                gridView.Attributes["CurrentSortField"] = sortField;
                gridView.Attributes["CurrentSortDirection"] = (sortDirection == SortDirection.Ascending ? "ASC" : "DESC");
            }
        }
    }
}

Images used in the demo.

Implement paging in an asp.net gridview that uses sqldatasource - Part 50

Suggested Videos 
Part 47 - Sorting a gridview that does not use any datasource control
Part 48 - Sorting an asp.net gridview in ascending and descending order
Part 49 - How to include sort arrows when sorting an asp.net gridview control



In this video we will discuss about implementing paging in a gridview control that uses sqldatasource control.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Drag and drop a gridview, a label  and a sqldatasource control on webform1.aspx

Step 2: Configure SqlDataSource1 control to retrieve data from tblEmployee table.

Step 3: Associate GridView1, with SqlDataSource1 control and make sure to select "Enable Paging" checkbox.

Step 4: To control the number of rows displayed in the gridview, set PageSize property. Since, I want to display 3 rows on a page, I have set the PageSize to 3.

Step 5: Generate GridView1 control's PreRender eventhandler method. Copy and paste the following code in code-behind file.
protected void GridView1_PreRender(object sender, EventArgs e)
{
    Label1.Text = "Displaying Page " + (GridView1.PageIndex + 1).ToString()
        + " of " + GridView1.PageCount.ToString();
}

That's it we are done. Run the application. Since we have 6 records in the database table, and as we have set the pagesize to 3, the GridView1 control shows 2 pages with 3 records on each page.

Here is the HTML
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
    AutoGenerateColumns="False" DataKeyNames="EmployeeId"
    DataSourceID="SqlDataSource1" PageSize="3"
    onprerender="GridView1_PreRender">
    <Columns>
        <asp:BoundField DataField="EmployeeId" ReadOnly="True"
            InsertVisible="False" HeaderText="EmployeeId"
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name"
            SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender"
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City"
            SortExpression="City" />
    </Columns>
</asp:GridView>
<asp:Label ID="Label1" runat="server"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>"
    SelectCommand="SELECT * FROM [tblEmployee]">
</asp:SqlDataSource>

Points to remember:
1. To control the number of rows displayed per page use PageSize property of GridView control.

2. When paging is enabled AllowPaging attribute of gridview control is set to True
AllowPaging="True"

3. PageIndex property of gridview control can be used to retrieve the page that is currently being viewed. PageIndex is ZERO based, so add 1 to it, to compute the correct page number that is being displayed in the gridview.

4. To get the total number of pages available, use PageCount property of GridView control.

5. When sqldatasource control is used, we get default paging. This means that, though we are displaying only 3 records on a page all the rows will be retrieved from the database and the gridview control then displays the correct set of 3 records depending on the page you are viewing. Everytime you click on a page, all the rows will be retrieved. Obviously this is bad for performance. We will discuss about custom paging in a later video session, which solves this problem. With custom paging it is possible to retrieve only the required number of rows and not all of them.

Implement default paging in an asp.net gridview that uses objectdatasource - Part 51

Suggested Videos 
Part 48 - Sorting an asp.net gridview in ascending and descending order
Part 49 - How to include sort arrows when sorting an asp.net gridview control
Part 50 - Implement paging in an asp.net gridview that uses sqldatasource



In this video we will discuss about implementing default paging in a gridview control that uses objectdatasource control.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Drag and drop a gridview, a label  and an objectdatasource control on webform1.aspx

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees()
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }
            return listEmployees;
        }
    }
}

Step 3: Configure ObjectDataSource1 control to retrieve data from Demo.EmployeeDataAccessLayer business object, using GetAllEmployees() method.

Step 4: Associate GridView1, with ObjectDataSource1 control, and make sure to select "Enable Paging" checkbox.

Step 5: To control the number of rows displayed in the gridview, set PageSize property. Since, I want to display 3 rows on a page, I have set the PageSize to 3.

Step 6: Generate GridView1 control's PreRender eventhandler method. Copy and paste the following code in code-behind file.
protected void GridView1_PreRender(object sender, EventArgs e)
{
    Label1.Text = "Displaying Page " + (GridView1.PageIndex + 1).ToString()
        + " of " + GridView1.PageCount.ToString();
}

That's it we are done. Run the application. Since we have 6 records in the database table, and as we have set the pagesize to 3, the GridView1 control shows 2 pages with 3 records on each page.

Here is the HTML
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
    AutoGenerateColumns="False" DataSourceID="ObjectDataSource1"
    PageSize="3" onprerender="GridView1_PreRender">
    <Columns>
        <asp:BoundField DataField="EmployeeId"
            HeaderText="EmployeeId" SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name"
            SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender"
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City"
            SortExpression="City" />
    </Columns>
</asp:GridView>
<asp:Label ID="Label1" runat="server"></asp:Label>
<asp:ObjectDataSource ID="ObjectDataSource1"  SelectMethod="GetAllEmployees"
    runat="server" TypeName="Demo.EmployeeDataAccessLayer">
</asp:ObjectDataSource>

Points to remember:
1. To control the number of rows displayed per page use PageSize property of GridView control.

2. When paging is enabled AllowPaging attribute of gridview control is set to True
AllowPaging="True"

3. PageIndex property of gridview control can be used to retrieve the page that is currently being viewed. PageIndex is ZERO based, so add 1 to it, to compute the correct page number that is being displayed in the gridview.

4. To get the total number of pages available, use PageCount property of GridView control.

5. With objectdatasource it is possible to implement both default paging and custom paging. With default paging, though we are displaying only 3 records on a page all the rows will be retrieved from the database and the gridview control then displays the correct set of 3 records depending on the page you are viewing. Everytime you click on a page, all the rows will be retrieved. Obviously this is bad for performance. We will discuss about implementing custom paging using objectdatasource control in our next video session, which solves this problem. With custom paging it is possible to retrieve only the required number of rows and not all of them.

Implement custom paging in an asp.net gridview that uses objectdatasource - Part 52

Suggested Videos 
Part 49 - How to include sort arrows when sorting an asp.net gridview control
Part 50 - Implement paging in an asp.net gridview that uses sqldatasource
Part 51 - Implement default paging gridview that uses objectdatasource



In this video we will discuss about implementing custom paging in a gridview control that uses objectdatasource control.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Create a stored procedure to retrieve only the required number of rows.
Create proc spGetEmployees
@StartIndex int,
@MaximumRows int
as
Begin
 Set @StartIndex = @StartIndex + 1
 
 Select EmployeeId, Name, Gender, City from
 (Select ROW_NUMBER() over (order by EmployeeId) as RowNumber, EmployeeId, Name, Gender, City
 from tblEmployee) Employees
 Where RowNumber >= @StartIndex and RowNumber < (@StartIndex + @MaximumRows)
End

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". To implement custom paging the following 2 methods are required in EmployeeDataAccessLayer.
GetEmployees(int startRowIndex, int maximumRows) - This method is responsible for retrieving the set of required rows. The 2 parameters startRowIndex and maximumRows are used to calculate the correct set of required rows.
GetTotalCount() - This method is responsible for returning the total numbers of rows in the underlying table. This method is used by the gridview control to calculate the exact number of pages to display, in the pager area of the gridview control.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetEmployees(int startRowIndex, int maximumRows)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployees", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramStartIndex = new SqlParameter();
                paramStartIndex.ParameterName = "@StartIndex";
                paramStartIndex.Value = startRowIndex;
                cmd.Parameters.Add(paramStartIndex);

                SqlParameter paramMaximumRows = new SqlParameter();
                paramMaximumRows.ParameterName = "@MaximumRows";
                paramMaximumRows.Value = maximumRows;
                cmd.Parameters.Add(paramMaximumRows);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }
            return listEmployees;
        }

        public static int GetTotalCount()
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select Count(*) from tblEmployee", con);

                con.Open();
                return (int)cmd.ExecuteScalar();
            }
        }
    }
}

Step 3: Build the solution.

Step 4: Drag and drop a gridview, and an objectdatasource control on webform1.aspx

Step 5: Configure ObjectDataSource1 control to retrieve data from Demo.EmployeeDataAccessLayer business object, using GetEmployees() method. On "Define Parameters" screen, click finish.

Step 6: Associate "ObjectDataSource1" with "GridView1" control, and select "Enable Paging" checkbox. Set PageSize property to 3.

Step 7: At this point, flip webform1.aspx to HTML source mode and delete "SelectParameters" section from "ObjectDataSource1" control. Set the following properties of ObjectDataSource control.
MaximumRowsParameterName="maximumRows"
StartRowIndexParameterName="startRowIndex"
SelectCountMethod="GetTotalCount"
EnablePaging="true"

At this point the HTML of "WebForm1.aspx" should be as shown below.
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
    AutoGenerateColumns="False" DataSourceID="ObjectDataSource1"
    PageSize="3">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name"
            SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender"
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City"
            SortExpression="City" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    SelectMethod="GetEmployees"
    TypeName="Demo.EmployeeDataAccessLayer"
    MaximumRowsParameterName="maximumRows"
    StartRowIndexParameterName="startRowIndex"
    SelectCountMethod="GetTotalCount"
    EnablePaging="true">
</asp:ObjectDataSource>

What is the difference between default paging and custom paging?
This is a very common asp.net interview question. With default paging all the rows are retrieved from the database every time we navigate to a different page. For example, if there are 1000 rows in database table, and if the page size is set 10, the gridview control displays just 10 rows at a time, but all the 1000 rows will be retrieved. When we navigate to page 2, the second set of 10 records get displayed in the gridview control, but again all the 1000 rows will be retrieved. Obviously this leads to a lot of unnecessary network traffic between database and web servers and is bad for performance.

With custom paging we only retrieve the subset of rows that will be displayed in the gridview control. For example, if there are 1000 rows in database table, and if the page size is set 10, with custom paging we retrieve just those 10 records. So, custom paging is much better for performance over default paging. When compared with default paging, custom paging is relatively complex to implement.

Implement default paging in an asp.net gridview without using datasource controls - Part 53

Suggested Videos 
Part 50 - Implement paging in an asp.net gridview that uses sqldatasource
Part 51 - Implement default paging gridview that uses objectdatasource
Part 52 - Implement custom paging in gridview that uses objectdatasource



In this video we will discuss about implementing default paging in a gridview control that does not use any data source controls.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate this table.



Step 1: Add a class file with name = "EmployeeDataAccessLayer.cs".
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees()
        {
            List<Employee> listEmployees = new List<Employee>;

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }
            return listEmployees;
        }
    }
}

Step 2: Drag and drop a gridview control on webform1.aspx and set the following properties of GridView1 control
AllowPaging="true" 
PageSize="3" 

Step 3: Generate event handler method for PageIndexChanging event of GridView control.

At this point, the HTML of "WebForm1.aspx" should be as shown below.
<asp:GridView
    ID="GridView1" runat="server" 
    AllowPaging="true" PageSize="3" 
    onpageindexchanging="GridView1_PageIndexChanging">
</asp:GridView>

Step 4: Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees();
        GridView1.DataBind();
    }
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees();
    GridView1.DataBind();
}

Implement custom paging in an asp.net gridview without using datasource controls - Part 54

Suggested Videos 
Part 51 - Implement default paging gridview that uses objectdatasource
Part 52 - Implement custom paging in gridview that uses objectdatasource
Part 53 - Default paging in gridview without using datasource controls



In this video we will discuss about implementing custom paging in a gridview control that does not use any data source controls.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Create a stored procedure to retrieve employees by page index and page size. This procedure also has an output parameter to return the total number of rows in the database table.
Create proc spGetEmployees_by_PageIndex_and_PageSize
@PageIndex int,
@PageSize int,
@TotalRows int output
as  
Begin  
 Declare @StartRowIndex int
 Declare @EndRowIndex int
 
 Set @StartRowIndex = (@PageIndex * @PageSize) + 1;
    Set @EndRowIndex = (@PageIndex + 1) * @PageSize;
 
 Select EmployeeId, Name, Gender, City from  
 (Select ROW_NUMBER() over (order by EmployeeId) as RowNumber, EmployeeId, Name, Gender, City
 from tblEmployee) Employees
 Where RowNumber >= @StartRowIndex and RowNumber <= @EndRowIndex
 
 Select @TotalRows = COUNT(*) from tblEmployee
End

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs".
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetEmployees(int pageIndex, int pageSize, out int totalRows)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployees_by_PageIndex_and_PageSize", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramStartIndex = new SqlParameter();
                paramStartIndex.ParameterName = "@PageIndex";
                paramStartIndex.Value = pageIndex;
                cmd.Parameters.Add(paramStartIndex);

                SqlParameter paramMaximumRows = new SqlParameter();
                paramMaximumRows.ParameterName = "@PageSize";
                paramMaximumRows.Value = pageSize;
                cmd.Parameters.Add(paramMaximumRows);

                SqlParameter paramOutputTotalRows = new SqlParameter();
                paramOutputTotalRows.ParameterName = "@TotalRows";
                paramOutputTotalRows.Direction = ParameterDirection.Output;
                paramOutputTotalRows.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(paramOutputTotalRows);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }

                rdr.Close();
                totalRows = (int)cmd.Parameters["@TotalRows"].Value;
               
            }
            return listEmployees;
        }
    }
}

Step 3: Copy and paste the following HTML on WebForm1.aspx
<asp:GridView ID="GridView1" runat="server"
    AllowPaging="true" PageSize="3">
</asp:GridView>
<asp:Repeater ID="repeaterPaging" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="pagingLinkButton" runat="server"
            Text='<%#Eval("Text") %>'
            CommandArgument='<%# Eval("Value") %>'
            Enabled='<%# Eval("Enabled") %>'
            OnClick="linkButton_Click">
        </asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

Step 4: Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        int totalRows = 0;
        GridView1.DataSource = EmployeeDataAccessLayer.
            GetEmployees(0, GridView1.PageSize, out totalRows);
        GridView1.DataBind();

        DatabindRepeater(0, GridView1.PageSize, totalRows);
    }
}
protected void linkButton_Click(object sender, EventArgs e)
{
    int totalRows = 0;
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    pageIndex -= 1;
    GridView1.PageIndex = pageIndex;
    GridView1.DataSource = EmployeeDataAccessLayer.
        GetEmployees(pageIndex, GridView1.PageSize, out totalRows);
    GridView1.DataBind();
    DatabindRepeater(pageIndex, GridView1.PageSize, totalRows);
}
private void DatabindRepeater(int pageIndex, int pageSize, int totalRows)
{
    int totalPages = totalRows / pageSize;
    if ((totalRows % pageSize) != 0)
    {
        totalPages += 1;
    }

    List<ListItem> pages = new List<ListItem>();
    if (totalPages > 1)
    {
        for (int i = 1; i <= totalPages; i++)
        {
            pages.Add(new ListItem(i.ToString(), i.ToString(), i != (pageIndex + 1)));
        }
    }
    repeaterPaging.DataSource = pages;
    repeaterPaging.DataBind();
}

Custom paging and sorting in gridview - Part 55

Suggested Videos 
Part 52 - Implement custom paging in gridview that uses objectdatasource
Part 53 - Default paging in gridview without using datasource controls
Part 54 - Custom paging in gridview without using datasource controls



For explanation of the code in this article, please watch the video by clicking on the image at the bottom of this page.

In this video we will discuss about implementing custom sorting and paging in an asp.net gridview control that does not use any data source controls. Please watch Part 54 before proceeding with this video.



Step 1: Create a stored procedure to retrieve employees sorted by page index and page size. This procedure also has an output parameter to return the total number of rows in the database table.
Create proc spGetEmployees_by_PageIndex_and_PageSize
@PageIndex int,  
@PageSize int,
@SortExpression nvarchar(50),
@SortDirection nvarchar(50),
@TotalRows int output
as    
Begin    
 Declare @StartRowIndex int  
 Declare @EndRowIndex int 
 
 Set @StartRowIndex = (@PageIndex * @PageSize) + 1;
        Set @EndRowIndex = (@PageIndex + 1) * @PageSize;
 
 Select EmployeeId, Name, Gender, City from    
 (Select ROW_NUMBER() over 
  (
   order by
    case when @SortExpression = 'EmployeeId' and @SortDirection = 'ASC'
     then EmployeeId end asc,
    case when @SortExpression = 'EmployeeId' and @SortDirection = 'DESC'
      then EmployeeId end desc,
    case when @SortExpression = 'Name' and @SortDirection = 'ASC'
      then Name end asc,
    case when @SortExpression = 'Name' and @SortDirection = 'DESC'
      then Name end desc,
    case when @SortExpression = 'Gender' and @SortDirection = 'ASC'
      then Gender end asc,
    case when @SortExpression = 'Gender' and @SortDirection = 'DESC'
      then Gender end desc,
    case when @SortExpression = 'City' and @SortDirection = 'ASC'
      then City end asc,
    case when @SortExpression = 'City' and @SortDirection = 'DESC'
      then City end desc
  ) as RowNumber, EmployeeId, Name, Gender, City  
 from tblEmployee) Employees  
 Where RowNumber >= @StartRowIndex and RowNumber <= @EndRowIndex
  
 Select @TotalRows = COUNT(*) from tblEmployee
End

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetEmployees(int pageIndex, int pageSize,
            string sortExpression, string sortDirection, out int totalRows)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployees_by_PageIndex_and_PageSize", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramStartIndex = new SqlParameter();
                paramStartIndex.ParameterName = "@PageIndex";
                paramStartIndex.Value = pageIndex;
                cmd.Parameters.Add(paramStartIndex);

                SqlParameter paramMaximumRows = new SqlParameter();
                paramMaximumRows.ParameterName = "@PageSize";
                paramMaximumRows.Value = pageSize;
                cmd.Parameters.Add(paramMaximumRows);

                SqlParameter paramSortExpression = new SqlParameter();
                paramSortExpression.ParameterName = "@SortExpression";
                paramSortExpression.Value = sortExpression;
                cmd.Parameters.Add(paramSortExpression);

                SqlParameter paramSortDirection = new SqlParameter();
                paramSortDirection.ParameterName = "@SortDirection";
                paramSortDirection.Value = sortDirection;
                cmd.Parameters.Add(paramSortDirection);

                SqlParameter paramOutputTotalRows = new SqlParameter();
                paramOutputTotalRows.ParameterName = "@TotalRows";
                paramOutputTotalRows.Direction = ParameterDirection.Output;
                paramOutputTotalRows.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(paramOutputTotalRows);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }

                rdr.Close();
                totalRows = (int)cmd.Parameters["@TotalRows"].Value;
               
            }
            return listEmployees;
        }
    }
}

Step 3: Copy and paste the following HTML on webform1.aspx
<asp:GridView ID="GridView1" runat="server" AllowSorting="true"
    CurrentSortField="EmployeeId" CurrentSortDirection="ASC"
    AllowPaging="true" PageSize="3"
    OnSorting="GridView1_Sorting">
</asp:GridView>
<asp:Repeater ID="repeaterPaging" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="pagingLinkButton" runat="server"
            Text='<%#Eval("Text") %>'
            CommandArgument='<%# Eval("Value") %>'
            Enabled='<%# Eval("Enabled") %>'
            OnClick="linkButton_Click">
        </asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

Step 4: Copy and paste the following code in webform1.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                int totalRows = 0;
                GridView1.DataSource = EmployeeDataAccessLayer.
                    GetEmployees(GridView1.PageIndex, GridView1.PageSize,
                    GridView1.Attributes["CurrentSortField"],
                    GridView1.Attributes["CurrentSortDirection"], out totalRows);
                GridView1.DataBind();

                DatabindRepeater(GridView1.PageIndex, GridView1.PageSize, totalRows);
            }
        }
        protected void linkButton_Click(object sender, EventArgs e)
        {
            int totalRows = 0;
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            pageIndex -= 1;
            GridView1.PageIndex = pageIndex;
           
            GridView1.DataSource = EmployeeDataAccessLayer.
                GetEmployees(pageIndex, GridView1.PageSize,
                GridView1.Attributes["CurrentSortField"],
                GridView1.Attributes["CurrentSortDirection"], out totalRows);
            GridView1.DataBind();
            DatabindRepeater(pageIndex, GridView1.PageSize, totalRows);
        }
        private void DatabindRepeater(int pageIndex, int pageSize, int totalRows)
        {
            int totalPages = totalRows / pageSize;
            if ((totalRows / pageSize) != 0)
            {
                totalPages += 1;
            }

            List<ListItem> pages = new List<ListItem>();
            if (totalPages > 1)
            {
                for (int i = 1; i <= totalPages; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(),
                        i != (pageIndex + 1)));
                }
            }
            repeaterPaging.DataSource = pages;
            repeaterPaging.DataBind();
        }
        private void SortGridview(GridView gridView, GridViewSortEventArgs e,
            out SortDirection sortDirection, out string sortField)
        {
            sortField = e.SortExpression;
            sortDirection = e.SortDirection;

            if (gridView.Attributes["CurrentSortField"] != null &&
                gridView.Attributes["CurrentSortDirection"] != null)
            {
                if (sortField == gridView.Attributes["CurrentSortField"])
                {
                    if (gridView.Attributes["CurrentSortDirection"] == "ASC")
                    {
                        sortDirection = SortDirection.Descending;
                    }
                    else
                    {
                        sortDirection = SortDirection.Ascending;
                    }
                }

                gridView.Attributes["CurrentSortField"] = sortField;
                gridView.Attributes["CurrentSortDirection"] =
                    (sortDirection == SortDirection.Ascending ? "ASC" : "DESC");
            }
        }
        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            SortDirection sortDirection = SortDirection.Ascending;
            string sortField = string.Empty;

            SortGridview(GridView1, e, out sortDirection, out sortField);
            string strSortDirection =
                sortDirection == SortDirection.Ascending ? "ASC" : "DESC";

            int totalRows = 0;
            GridView1.DataSource = EmployeeDataAccessLayer.
                GetEmployees(GridView1.PageIndex, GridView1.PageSize,
                e.SortExpression, strSortDirection, out totalRows);
            GridView1.DataBind();
            DatabindRepeater(GridView1.PageIndex, GridView1.PageSize, totalRows);
        }
    }
}

asp.net gridview paging using a dropdownlist - Part 56

Suggested Videos 
Part 53 - Default paging in gridview without using datasource controls
Part 54 - Custom paging in gridview without using datasource controls
Part 55 - Custom sorting and paging in gridview



In this video we will discuss about implementing paging in gridview and controlling the page size and page numbers using a dropdownlist, as shown in the image below.


We will be modifying the example that we discussed in Part 55. Please watch Part 55, before proceeding with this video.



Step 1: Create a table with 2 rows. The first row should contain 4 TD's, and the second row should contain 1 TD. The first row should contain the following.
1. Text - Page Size
2. DropDownList - with page sizes(5, 10, 15, 20)
3. Text - Page Number
4. DropDownList - to list the pages. The number of pages available will be computed dynamically

Since the second row contains, only one TD, set colspan=4 for this TD, so that the width of this one TD spans across 4 columns. Since, we are now going to use a dropdownlist to display the pages for navigation, we can get rid of the repeater control. Also, set the page size of gridview control to 5. At this point the HTML of WebForm1.aspx should be as shown below.
<div style="font-family: Arial">
<table>
<tr>
<td style="color: #A55129">
    <strong>Page Size:</strong>
</td>
<td>
    <asp:DropDownList ID="ddlPageSize" runat="server"
        AutoPostBack="True" onselectedindexchanged
        ="ddlPageSize_SelectedIndexChanged">
        <asp:ListItem>5</asp:ListItem>
        <asp:ListItem>10</asp:ListItem>
        <asp:ListItem>15</asp:ListItem>
        <asp:ListItem>20</asp:ListItem>
    </asp:DropDownList>
</td>
<td style="color: #A55129">
    <strong>Page Number:
    </strong>
</td>
<td>
    <asp:DropDownList ID="ddlPageNumbers" runat="server"
        AutoPostBack="True" onselectedindexchanged
        ="ddlPageNumbers_SelectedIndexChanged">
    </asp:DropDownList>
</td>
</tr>
<tr>
    <td colspan="4">
        <asp:GridView ID="GridView1" runat="server"
            AllowPaging="True" PageSize="5"
            CurrentSortField="EmployeeId"
            CurrentSortDirection="ASC" AllowSorting="True"
            BackColor="#DEBA84" BorderColor="#DEBA84"
            BorderStyle="None" BorderWidth="1px"
            CellPadding="3" CellSpacing="2"
            OnSorting="GridView1_Sorting">
            <FooterStyle BackColor="#F7DFB5"
            ForeColor="#8C4510" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True"
                ForeColor="White" />
            <PagerStyle ForeColor="#8C4510"
                HorizontalAlign="Center" />
            <RowStyle BackColor="#FFF7E7"
                ForeColor="#8C4510" />
            <SelectedRowStyle BackColor="#738A9C"
                Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#FFF1D4" />
            <SortedAscendingHeaderStyle BackColor="#B95C30" />
            <SortedDescendingCellStyle BackColor="#F1E5CE" />
            <SortedDescendingHeaderStyle BackColor="#93451F" />
        </asp:GridView>
    </td>
</tr>
</table>
</div>

Step 2: Copy and paste the following code in WebForm1.aspx.cs. Please note the following changes.
1. DatabindRepeater() method name is changed to Databind_DDLPageNumbers(). The page numbers are added to the dropdownlist(ddlPageNumbers) instead of repeater control.
2. In the Page_Load() event, we are invoking Databind_DDLPageNumbers() method and passing, ZERO as the page index and 5 as the page size.
3. Event handler method linkButton_Click() is deleted as we no longer need it.
4. In GridView1_Sorting(), notice that page size and page index is retrieved from ddlPageSize and ddlPageNumbers dropdownlist respectively.
5. Finally notice the implementations of ddlPageSize_SelectedIndexChanged() and ddlPageNumbers_SelectedIndexChanged().
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                int totalRows = 0;
                GridView1.DataSource = EmployeeDataAccessLayer.
                    GetEmployees(0, 5,
                    GridView1.Attributes["CurrentSortField"],
                    GridView1.Attributes["CurrentSortDirection"],
                    out totalRows);
                GridView1.DataBind();

                Databind_DDLPageNumbers(0, 5, totalRows);
            }
        }
        private void Databind_DDLPageNumbers
            (int pageIndex, int pageSize, int totalRows)
        {
            int totalPages = totalRows / pageSize;
            if ((totalRows % pageSize) != 0)
            {
                totalPages += 1;
            }

            if (totalPages > 1)
            {
                ddlPageNumbers.Enabled = true;
                ddlPageNumbers.Items.Clear();
                for (int i = 1; i <= totalPages; i++)
                {
                    ddlPageNumbers.Items.Add(new 
                        ListItem(i.ToString(), i.ToString()));
                }
            }
            else
            {
                ddlPageNumbers.SelectedIndex = 0;
                ddlPageNumbers.Enabled = false;
            }
        }

        private void SortGridview
            (GridView gridView, GridViewSortEventArgs e,
            out SortDirection sortDirection, out string sortField)
        {
            sortField = e.SortExpression;
            sortDirection = e.SortDirection;

            if (gridView.Attributes["CurrentSortField"] != null &&
                gridView.Attributes["CurrentSortDirection"] != null)
            {
                if (sortField == gridView.Attributes["CurrentSortField"])
                {
                    if (gridView.Attributes["CurrentSortDirection"] == "ASC")
                    {
                        sortDirection = SortDirection.Descending;
                    }
                    else
                    {
                        sortDirection = SortDirection.Ascending;
                    }
                }

                gridView.Attributes["CurrentSortField"] = sortField;
                gridView.Attributes["CurrentSortDirection"] =
                    (sortDirection == SortDirection.Ascending ? "ASC" : "DESC");
            }
        }
        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            SortDirection sortDirection = SortDirection.Ascending;
            string sortField = string.Empty;

            SortGridview(GridView1, e, out sortDirection, out sortField);
            string strSortDirection =
                sortDirection == SortDirection.Ascending ? "ASC" : "DESC";

            int totalRows = 0;

            int pageSize = int.Parse(ddlPageSize.SelectedValue);
            int pageIndex = int.Parse(ddlPageNumbers.SelectedValue) - 1;
           
            GridView1.DataSource =
                EmployeeDataAccessLayer.GetEmployees(pageIndex, pageSize,
                e.SortExpression, strSortDirection, out totalRows);
            GridView1.DataBind();
            ddlPageNumbers.SelectedValue = (pageNumber + 1).ToString();
        }

        protected void ddlPageSize_SelectedIndexChanged
            (object sender, EventArgs e)
        {
            int totalRows = 0;

            int pageSize = int.Parse(ddlPageSize.SelectedValue);
            int pageIndex = 0;

            GridView1.PageSize = pageSize;

            GridView1.DataSource = EmployeeDataAccessLayer.
                GetEmployees(pageIndex, pageSize,
                GridView1.Attributes["CurrentSortField"],
                GridView1.Attributes["CurrentSortDirection"],
                out totalRows);
            GridView1.DataBind();

            Databind_DDLPageNumbers(pageIndex, pageSize, totalRows);
        }
        protected void ddlPageNumbers_SelectedIndexChanged
            (object sender, EventArgs e)
        {
            int totalRows = 0;

            int pageSize = int.Parse(ddlPageSize.SelectedValue);
            int pageIndex = int.Parse(ddlPageNumbers.SelectedValue) - 1;

            GridView1.PageSize = pageSize;

            GridView1.DataSource = EmployeeDataAccessLayer.
                GetEmployees(pageIndex, pageSize,
                GridView1.Attributes["CurrentSortField"],
                GridView1.Attributes["CurrentSortDirection"],
                out totalRows);
            GridView1.DataBind();
        }
    }
}

EmployeeDataAccessLayer and the stored procedure used will not change in any way.

Export gridview to excel in asp.net - Part 57

Suggested Videos 
Part 54 - Custom paging in gridview without using datasource controls
Part 55 - Custom sorting and paging in gridview
Part 56 - Gridview paging using a dropdownlist



In this video, we will discuss about exporting gridview data to excel. We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Create an asp.net web application project.

Step 2: Drag and drop a gridview control and a button control on webform1.aspx. Autoformat the gridview control to use "BrownSugar" scheme. Set the following properties of the button control.
ID="btnExportToExcel"
Text="Export to excel"
OnClick="btnExportToExcel_Click"

At this point the HTML on webform1.aspx should be as shown below.
<asp:GridView ID="GridView1" runat="server" BackColor="#DEBA84"
    BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
    CellPadding="3" CellSpacing="2">
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True"
        ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
        ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:Button ID="btnExportToExcel" runat="server"
    Text="Export to excel"
    OnClick="btnExportToExcel_Click" />

Step 3: Copy and paste the following code in webform1.aspx.cs. The code is well commented and is self-explanatory.
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindData();
    }
}

private void BindData()
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlDataAdapter da = new SqlDataAdapter("Select * from tblEmployee", con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
    // Clear all content output from the buffer stream
    Response.ClearContent();
    // Specify the default file name using "content-disposition" RESPONSE header
    Response.AppendHeader("content-disposition", "attachment; filename=Employees.xls");
    // Set excel as the HTTP MIME type
    Response.ContentType = "application/excel";
    // Create an instance of stringWriter for writing information to a string
    System.IO.StringWriter stringWriter = new System.IO.StringWriter();
    // Create an instance of HtmlTextWriter class for writing markup 
    // characters and text to an ASP.NET server control output stream
    HtmlTextWriter htw = new HtmlTextWriter(stringWriter);

    // Set white color as the background color for gridview header row
    GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

    // Set background color of each cell of GridView1 header row
    foreach (TableCell tableCell in GridView1.HeaderRow.Cells)
    {
        tableCell.Style["background-color"] = "#A55129";
    }

    // Set background color of each cell of each data row of GridView1
    foreach (GridViewRow gridViewRow in GridView1.Rows)
    {
        gridViewRow.BackColor = System.Drawing.Color.White;
        foreach (TableCell gridViewRowTableCell in gridViewRow.Cells)
        {
            gridViewRowTableCell.Style["background-color"] = "#FFF7E7";
        }
    }

    GridView1.RenderControl(htw);
    Response.Write(stringWriter.ToString());
    Response.End();
}

Step 4: Run the application and click on "Export to excel" button. You will get an error stating - Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server. This is because, we are calling GridView1.RenderControl(htmlTextWriter) method. This causes .net to think that a Server-Control is being rendered outside of a Form. To fix this error, we need to override VerifyRenderingInServerForm() method. Copy and paste the following method in webform1.aspx.cs.
public override void VerifyRenderingInServerForm(Control control)
{
}

At this point, run the application and click on "Export to excel button. The data gets exported to excel as expected. However, when you try to open the file you will get a warning message stating - The file you are trying to open, 'Employee.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

According to MSDN - The warning message is a user-notification function that was added to Excel 2007. The warning message can help prevent unexpected problems that might occur because of possible incompatibility between the actual content of the file and the file name extension.

One way to resolve this error, is to edit the registry. Microsoft knowledge base article related to this error
http://support.microsoft.com/kb/948615

If you want to export the same data to Microsoft word, there are only 2 changes that you need to do.
First Change: Change the file extension to ".doc" instead of ".xls"
Response.AppendHeader("content-disposition", "attachment; filename=Employees.doc");

Second Change: Set the content type to word
Response.ContentType = "application/word";

Export gridview to pdf in asp.net - Part 58

Suggested Videos 
Part 55 - Custom sorting and paging in gridview
Part 56 - Gridview paging using a dropdownlist
Part 57 - Export gridview to excel in asp.net



In this video, we will discuss about exporting gridview data to PDF. We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Create an asp.net web application project.

Step 2: Drag and drop a gridview control and a button control on webform1.aspx. Autoformat the gridview control to use "BrownSugar" scheme. Double click on the button control, to generate click event handler method.

At this point the HTML on webform1.aspx should be as shown below.
<asp:GridView ID="GridView1" runat="server" BackColor="#DEBA84"
    BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
    CellPadding="3" CellSpacing="2">
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True"
        ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
        ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Button"
onclick="Button1_Click" />

Step 3: To generate PDF documents we will be using open source assembly - iTextSharp.dll. This assembly can be downloaded from http://sourceforge.net/projects/itextsharp.

After you download the assembly, add a reference to it, from your web application.
a) In Solution Explorer, right click on the "References" folder and select "Add Reference"
b) Browse to the folder where you have downloaded the assembly and Click OK.

Step 4: Add the following "USING" statements, in your code-behind file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;

Step 5: Copy and paste the following code. The code is well commented and is self-explanatory.
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridViewData();
    }
}

// ADO.NET code to retrieve data from database
private void BindGridViewData()
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlDataAdapter da = new SqlDataAdapter("Select * from tblEmployee", con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

protected void Button1_Click(object sender, EventArgs e)
{
    int columnsCount = GridView1.HeaderRow.Cells.Count;
    // Create the PDF Table specifying the number of columns
    PdfPTable pdfTable = new PdfPTable(columnsCount);

    // Loop thru each cell in GrdiView header row
    foreach(TableCell gridViewHeaderCell in GridView1.HeaderRow.Cells)
    {
        // Create the Font Object for PDF document
        Font font = new Font();
        // Set the font color to GridView header row font color
        font.Color = new BaseColor(GridView1.HeaderStyle.ForeColor);

        // Create the PDF cell, specifying the text and font
        PdfPCell pdfCell = new PdfPCell(new Phrase(gridViewHeaderCell.Text, font));

        // Set the PDF cell backgroundcolor to GridView header row BackgroundColor color
        pdfCell.BackgroundColor = new BaseColor(GridView1.HeaderStyle.BackColor);

        // Add the cell to PDF table
        pdfTable.AddCell(pdfCell);
    }

    // Loop thru each datarow in GrdiView
    foreach (GridViewRow gridViewRow in GridView1.Rows)
    {
        if (gridViewRow.RowType == DataControlRowType.DataRow)
        {
            // Loop thru each cell in GrdiView data row
            foreach (TableCell gridViewCell in gridViewRow.Cells)
            {
                Font font = new Font();
                font.Color = new BaseColor(GridView1.RowStyle.ForeColor);

                PdfPCell pdfCell = new PdfPCell(new Phrase(gridViewCell.Text, font));

                pdfCell.BackgroundColor = new BaseColor(GridView1.RowStyle.BackColor);

                pdfTable.AddCell(pdfCell);
            }
        }
    }

    // Create the PDF document specifying page size and margins
    Document pdfDocument = new Document(PageSize.A4, 10f, 10f, 10f, 10f);
    
    PdfWriter.GetInstance(pdfDocument, Response.OutputStream);

    pdfDocument.Open();
    pdfDocument.Add(pdfTable);
    pdfDocument.Close();

    Response.ContentType = "application/pdf";
    Response.AppendHeader("content-disposition", 
        "attachment;filename=Employees.pdf");
    Response.Write(pdfDocument);
    Response.Flush();
    Response.End();
}

At this point, run the application and click on the button. The data gets exported to PDF as expected. If you are using Google chrome as your browser, you may get a browser warning stating - this type of file can harm your computer do you want to keep Employees.pdf anyway? This is a know issue with Google chrome. Internet explorer, works fine.

Repeater control in asp.net - Part 60

Suggested Videos 
Part 57 - Export gridview to excel
Part 58 - Export gridview to pdf
Part 59 - Generate pdf document from gridview data and store on web server



In this video we will discuss about 
1. The similarities and differences between gridview and repeater controls
2. When to use gridview control over repeater and viceversa



Similarities:
1. Just like gridview, a repeater is also a data-bound control.
2. To bind data, you use DataSource property and invoke DataBind() method.
3. The following 3 events are supported by both the controls
     a) ItemCommand
     b) ItemCreated
     c) ItemDataBound

Differences:
1. By default, a repeater control will not render anything if templates are not specified, but where as with GridView control, the data from the underlying data source will be rendered in a tabular format.
2. GridView is more suitable and designed for displaying data in a tabular format, where as Repeater is used when you want to have complete control over how you want the data to be rendered. For example, if I want the data from tblEmployee table to be rendered as shown below, then repeater is a better control. But please remember, it is also possible to do the same thing with GridView using Template fields.


3. By default, the repeater control does not offer editing, paging and sorting capabilities, but where as a gridview control does. However, with a bit of effort and code it is possible to implement these features using a repeater control.

When do you use gridview control over repeater and vice versa?
A repeater is a very light weight control, and if you want to have the complete control over how the data is rendered, and if you don't want editing, paging or sorting capabilities, then repeater should be your choice. On the other hand if you want the data to be displayed in a tabular format and you also need editing, paging and sorting capabilities, then gridview should be your choice.

For sql script to create and populate tblEmployee table, please refer to Part 30 by clicking here.

Webform1.aspx HTML used in the demo
<table>
<tr>
<td>
    <asp:GridView ID="GridView1" ShowHeader="false"
        GridLines="None" AutoGenerateColumns="false"
        runat="server">
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <table style="border: 1px solid #A55129;
                        background-color: #FFF7E7">
                        <tr>
                            <td style="width: 200px">
                                <asp:Image ID="imgEmployee"
                                    ImageUrl='<%# Eval("PhotoPath")%>'
                                    runat="server" />
                            </td>
                            <td style="width: 200px">
                                <table>
                                    <tr>
                                        <td>
                                            <b>Id:</b>
                                        </td>
                                        <td>
                                            <asp:Label ID="lblId"
                                            runat="server"
                                            Text='<%#Eval("EmployeeId") %>'>
                                            </asp:Label>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            <b>Name:</b>
                                        </td>
                                        <td>
                                            <asp:Label ID="lblName"
                                            runat="server"
                                            Text='<%#Eval("Name") %>'>
                                            </asp:Label>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            <b>Gender:</b>
                                        </td>
                                        <td>
                                            <asp:Label ID="lblGender"
                                            runat="server"
                                            Text='<%#Eval("Gender") %>'>
                                            </asp:Label>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            <b>City:</b>
                                        </td>
                                        <td>
                                            <asp:Label ID="lblCity"
                                            runat="server"
                                            Text='<%#Eval("City") %>'>
                                            </asp:Label>
                                        </td>
                                    </tr>
                                </table>
                            </td>
                        </tr>
                    </table>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</td>
<td>
    <asp:Repeater ID="Repeater1" runat="server">
        <ItemTemplate>
            <table style="border: 1px solid #A55129;
                background-color: #FFF7E7">
                <tr>
                    <td style="width: 200px">
                        <asp:Image ID="imgEmployee"
                        ImageUrl='<%# Eval("PhotoPath")%>'
                        runat="server" />
                    </td>
                    <td style="width: 200px">
                        <table>
                            <tr>
                                <td>
                                    <b>Id:</b>
                                </td>
                                <td>
                                    <asp:Label ID="lblId"
                                    runat="server"
                                    Text='<%#Eval("EmployeeId") %>'>
                                    </asp:Label>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    <b>Name:</b>
                                </td>
                                <td>
                                    <asp:Label ID="lblName"
                                    runat="server"
                                    Text='<%#Eval("Name") %>'>
                                    </asp:Label>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    <b>Gender:</b>
                                </td>
                                <td>
                                    <asp:Label ID="lblGender"
                                    runat="server"
                                    Text='<%#Eval("Gender") %>'>
                                    </asp:Label>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    <b>City:</b>
                                </td>
                                <td>
                                    <asp:Label ID="lblCity"
                                    runat="server"
                                    Text='<%#Eval("City") %>'>
                                    </asp:Label>
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
            </table>
        </ItemTemplate>
        <SeparatorTemplate>
            <asp:Image ID="Image1"
            ImageUrl="~/Images/1x1PixelImage.png"
            runat="server" />
        </SeparatorTemplate>
    </asp:Repeater>
</td>
</tr>
</table>

WebForm1.aspx.cs code:
protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds = GetData();

    GridView1.DataSource = ds;
    GridView1.DataBind();

    Repeater1.DataSource = ds;
    Repeater1.DataBind();
}
private DataSet GetData()
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlDataAdapter da = new SqlDataAdapter("Select * from tblEmployee", con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}