a developer's notes – a semi-technical web development BLOG

January 28, 2012

ASP.NET Gridview Example

Filed under: ASP.NET,C# — Duy Nguyen @ 4:22 pm
Tags: , , , , , , , , , ,

1. Create the Gridview on the ASPX page.

            <asp:GridView ID="customersGrid" 
                runat="server"
                AllowPaging="true" 
                AllowSorting="true"                 
                PageSize="20" 
                OnPageIndexChanging="gridView_PageIndexChanging" 
                AutoGenerateColumns="false" 
                OnSorting="gridView_Sorting"
                CssClass="mGrid"  
                PagerStyle-CssClass="pgr"  
                AlternatingRowStyle-CssClass="alt"
                RowStyle-CssClass="normRow">
                
                <Columns>
                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" SortExpression="CustomerID" />
                    <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                    <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                    <asp:BoundField DataField="MiddleName" HeaderText="MiddleName" SortExpression="MiddleName" />
                    <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                    <asp:BoundField DataField="Suffix" HeaderText="Suffix" SortExpression="Suffix" />
                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
                    <asp:BoundField DataField="SalesPerson" HeaderText="SalesPerson" SortExpression="SalesPerson" />
                    <asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress" SortExpression="EmailAddress" />
                    <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
                </Columns> 

                <PagerSettings Mode="NumericFirstLast" PageButtonCount="5"  FirstPageText="First" LastPageText="Last"/> 

            </asp:GridView>

2. On the C# code behind, create a method to load the gridview. There are other ways of preserving the datasource other than using Session, but for this purpose, we will use Session.

        public void LoadCustomersGrid()
        {
            DataTable customersTable;

            if (!IsPostBack)
            {
                customersTable = CustomerService.GetAllCustomers();
                Session["session_customer_dataTable"] = customersTable;
                this.customersGrid.DataSource = customersTable;
            }
            else
            {
                DataView dataView = Session["session_customer_dataView"] as DataView;

                if (dataView != null)
                {
                    this.customersGrid.DataSource = dataView;
                }
                else
                {
                    customersTable = Session["session_customer_dataTable"] as DataTable;
                    this.customersGrid.DataSource = customersTable;
                }
            }

            this.customersGrid.DataBind();
        }

Here are the three methods for Paging and Sorting

        public void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            this.customersGrid.PageIndex = e.NewPageIndex;
            this.customersGrid.DataBind();
        }

        protected void gridView_Sorting(object sender, GridViewSortEventArgs e)
        {
            DataTable customersTable = this.customersGrid.DataSource as DataTable;
            DataView dataView;

            if (customersTable != null)
            {
                dataView = new DataView(customersTable);
            }
            else
            {
                dataView = this.customersGrid.DataSource as DataView;
            }

            if ((ViewState["gridSortDirection"] == null) || (ViewState["gridSortDirection"].ToString() == "DESC"))
            {
                dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(SortDirection.Ascending);
                ViewState["gridSortDirection"] = "ASC";
            }
            else
            {
                dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(SortDirection.Descending);
                ViewState["gridSortDirection"] = "DESC";
            }

            Session["session_customer_dataView"] = dataView;

            this.customersGrid.DataSource = dataView;

            //sets the page back to 0 if the user reorders the grid
            this.customersGrid.PageIndex = 0;
            this.customersGrid.DataBind();
        }

        private string ConvertSortDirectionToSql(SortDirection sortDirection)
        {
            string newSortDirection = String.Empty;

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

                case SortDirection.Descending:
                    newSortDirection = "DESC";
                    break;
            }

            return newSortDirection;
        }

And here is the data layer call to get the list to bind to the gridview.

        /// <summary>
        /// Uses a datatable so that you can sort the gridview
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllCustomers()
        {
            DataTable dt;

            string connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT2008R2;Integrated Security=SSPI";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                //in-line sql
                SqlCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = "select * from SalesLT.Customer";

                // For stored procedures
                //SqlCommand command = connection.CreateCommand();
                //command.CommandType = CommandType.StoredProcedure;
                //command.CommandText = "sp_get_all_customers";

                dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);
            }

            return dt;
        }
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: