Managing GridView/DataGrid Control Programmatically in Visual Studio (C#) Web Form

• Download source code - 50.7 KB

Introduction

This article is for developers / programmers that are very familiar with Visual Studio Web Applications. The purpose of this simple project is to demonstrate how to manage GridView/Datagrid in Visual Studio web form programmatically. I said GridView or Datagrid control because the two controls have a lot of similarities in Visual Studio.

At times, while in a hurry to quickly deliver on a project, I just select any of the two controls and move on because programming them to me is very similar, but others may not see them that way. But for the purpose of this project, I used GridView to demonstrate my idea.

This project presents basically three (3) things that usually give developers challenges and consume their valuable time.

1. Populating and Formatting Gridview cells using Datatable
2. Aggregating column data (Sum, Count, Average, etc.)
3. Exporting the Data to physical disk.

Note that we are only looking at pure programming without formatting and setting fields/properties in Visual Studio design mode. We are aware it is possible to set some properties at design mode, but at times, you do not know the complete structure of your datatable and you want to do this programmatically. So also, you want to export the data to Excel, Word or PDF.

This project is not specific about your database connection that will generate your datatable. As long as you have a datatable that can be the datasource of your GridView, you are good to go.

For the purpose of this project, because we do not want to establish any database connection, I just created an arbitrary datatable and manually populated it for my demo (salary stuff). You can change the datatable to your own datatable coming from your database connection.

Background

This project presents basically three things that usually give developers challenges and consume their valuable time using GridView or Datagrid programmatically.

We will address these three areas of challenge:-

1. Populating and formatting Gridview cells
2. Aggregating column data (Sum, Count, Average, etc.). We will only deal with sum.
3. Exporting the Data to physical disk. We will only look at CSV/Excel.

Using the Code

The entire code is hereby attached, it was written in C# and in 3.5 Framework, may also work in lower or higher frameworks.

In order to adapt this project to your situation, you must add reference to "Microsoft.VisualBasic" and add the following to the two classes in the web form codebehind and the other class for CSV.

using Microsoft.VisualBasic; 

It is a very simple project with only one web form and a class.

The web form is the Start Page and the class is just to convert datatable to string using a stringbuilder object. The Web Form has a Button on it that exports the GridView data to CSV/Excel.

The web form is called "ManageGridView" and the only class is called CSV_Manager.

When you run the app, it automatically loads data on the GridView using the datatable I manually created in the class to populate the grid.

// Codebehind for web form

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using Microsoft.VisualBasic;

namespace ManageGridView
{
    public partial class ManageGridView : System.Web.UI.Page
    {
        private void InitializeComponent()
        {
            Button1.Click -= new System.EventHandler(this.Button1_Click);
            Button1.Click += new System.EventHandler(this.Button1_Click);            
            GridView1.RowDataBound -= new GridViewRowEventHandler
                                      (this.GridView1_RowDataBound);
            GridView1.RowDataBound += new GridViewRowEventHandler
                                      (this.GridView1_RowDataBound);
        }

        private void Page_Init(System.Object sender, System.EventArgs e)
        {
            //CODEGEN: This method call is required by the Web Form Designer
            //Do not modify it using the code editor.
            InitializeComponent();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }

        private void BindGrid(DataTable MyDt = null)
        {
            DataTable BindDt = new DataTable();

            if (MyDt == null)
            {
                BindDt = CSV_Manager.ArbitraryDatatable();  //Your datatable 
                                     //could come from any database connection
            }
            else
            {
                BindDt = MyDt;
            }

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

            using (BindDt)

            {
                double totalSalary = BindDt.AsEnumerable().Sum
                                     (row => row.Field<double>("Salary"));

                GridView1.FooterRow.Cells[2].Text = "Total ==>";
                GridView1.FooterRow.Cells[3].Text = totalSalary.ToString("N2");
                GridView1.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Right;
            }
        }

///================================================================
        //Export data to CSV/Excel
        protected void Button1_Click(object sender, EventArgs e) 
        {

            StringBuilder sbAgr = new StringBuilder();

            DataTable MyDt = CSV_Manager.ArbitraryDatatable();   //Your datatable 
                                                                 //can also come 
            //from any database connection, this Arbitrary Datatable is just for a test

            BindGrid(MyDt);

            if (MyDt != null)
            {

                string csvString = CSV_Manager.ExportDatatableToCSV(MyDt);  //Pass 
                              //the datasource (datatable) 
                              //of your gridview as the parameter to convert to csv

                double totalSalary = MyDt.AsEnumerable().Sum(row => 
                row.Field<double>("Salary")); //Summarize Salary to add to export data

                for (int i = 0; i < MyDt.Rows.Count; i++)
                {
                    if (i == 2)
                    {
                        sbAgr.Append("Total ==>" + ',');
                    }
                    else if (i == 3)
                    {
                        sbAgr.Append(totalSalary.ToString() + ',');
                    }
                    else
                    {
                        sbAgr.Append(" " + ',');
                    }
                }
                sbAgr.Append("\r\n");

                csvString = csvString + sbAgr.ToString();  //Add summary string

                if (csvString != "")
                {
                    Response.AddHeader("Content-Disposition", 
                    "attachment; filename=Saved_Datatable.csv");
                    Response.ContentType = "application/ms-excel";
                    Response.ContentEncoding = 
                             System.Text.Encoding.GetEncoding("utf-8");
                    Response.Write(csvString);
                    Response.End();
                }
            }
        }
        
        //Handle all column-specific events here like column alignment 
        //and formatting, etc
        protected void GridView1_RowDataBound(Object Sender, GridViewRowEventArgs E)
        {
            if (E.Row.RowType == DataControlRowType.DataRow)
            {
                double myText = Conversion.Val(E.Row.Cells[3].Text);
                string DateText = Formatted_Date(E.Row.Cells[2].Text);

                E.Row.Cells[3].HorizontalAlign = HorizontalAlign.Right; //Right align 
                                                 //the salary column bc it is a number
                E.Row.Cells[3].ForeColor = System.Drawing.Color.Green;  //Change salary 
                                                 //column color to green 
                E.Row.Cells[2].Text = DateText;  //Format data in each cell 
                                                 //to formatted date
                E.Row.Cells[3].Text = myText.ToString("N2"); //Format data in each cell 
                                                             //to currency format
            }
        }

        public static string Formatted_Date(string YourDate)
        {
            if (Information.IsDate(YourDate) == false)
            {
                return "";
            }
            DateTime moment = Convert.ToDateTime(YourDate);
            string monthName = moment.ToString("MMM");

            string theDay = moment.Day.ToString();
            if (moment.Day < 10)
            {
                theDay = "0" + theDay;
            }

            return theDay + "-" + monthName + "-" + moment.Year;
        }

        public ManageGridView()
        {
            Load += Page_Load;
            Init += Page_Init;
        }
    }    
} 

//The CSV Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;
//using System.Net.Http;
using System.Web.Script.Serialization;
using System.IO;
using System.Net;
using System.Text;
//using System.Threading.Tasks;
//using Newtonsoft.Json;
using System.Runtime.Serialization;
using System.Xml;
using System.Xml.Linq;
 
using System.Web.UI.HtmlControls;
using Microsoft.VisualBasic;

namespace ManageGridView
{
    public static class CSV_Manager
    {
        public static string ExportDatatableToCSV(DataTable GridViewDatatable) //Pass 
          //the datasource (datatable) of your gridview as the parameter 
        {
            string leftOne = "";
            StringBuilder sbldr = new StringBuilder();
            if (GridViewDatatable.Columns.Count != 0)
            {
                foreach (DataColumn col in GridViewDatatable.Columns)
                {
                    string colName = col.ColumnName;
                    sbldr.Append(colName + ',');
                }
                sbldr.Append("\r\n");
                foreach (DataRow row in GridViewDatatable.Rows)
                {
                    foreach (DataColumn column in GridViewDatatable.Columns)
                    {
                        string colData = ""  + row[column].ToString();

                        if (colData.Contains(','))
                        {
                            colData = String.Format("\"{0}\"", colData);                           
                        }

                        leftOne = Strings.Left(colData, 1);

                        if (column.DataType == typeof(string)) // Try to prevent 
                                                               // loss of leading zeroes
                        {
                            try
                            {
                                int result = int.Parse(leftOne);   //If it can convert, 
                                 //then it is a numeric, a way of testing 
                                 //is numeric in C#

                                if (result == 0 && leftOne != "-" && leftOne != ".") 
                                { colData = "'" + colData; }
                            }
                            catch
                            { }
                        }
                        
                        sbldr.Append(colData + ',');
                    }
                    sbldr.Append("\r\n");
                }
            }
            return sbldr.ToString();
        }

        public static DataTable ArbitraryDatatable()  //This is just to get 
                                                      //a single record datatable. 
                                                      //Your datatable could have 
                                                      //come from any database also
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));
            dt.Columns.Add("Date_Employed", typeof(DateTime));
            dt.Columns.Add("Salary", typeof(double));

            // Create a DataRow, add Name and Age data, and add to the DataTable
            DataRow dr1 = dt.NewRow();
            dr1["Name"] = "Raphael";              // or dr1[0]="Raphael";
            dr1["Age"] = 48;                      // or dr1[1]=48;
            dr1["Date_Employed"] = "03-Jan-2019"; // or dr1[1]="03-Jan-2019";;
            dr1["Salary"] = 78000;                // or dr1[2]=78000;
            dt.Rows.Add(dr1);

            DataRow dr2 = dt.NewRow();
            dr2["Name"] = "Mohammad";             // or dr2[0]="Mohammad";
            dr2["Age"] = 24;                      // or dr2[1]=24;
            dr2["Date_Employed"] = "01-Aug-2018"; // or dr2[1]="01-Aug-2018";;
            dr2["Salary"] = 35000;                // or dr2[2]=35000;
            dt.Rows.Add(dr2);

            DataRow dr3 = dt.NewRow();
            dr3["Name"] = "Nelson";               // or dr3[0]="Nelson";
            dr3["Age"] = 54;                      // or dr3[1]=54;
            dr3["Date_Employed"] = "01-Apr-2019"; // or dr3[1]="01-Apr-2019";;
            dr3["Salary"] = 65000.78;             // or dr3[2]=65000;
            dt.Rows.Add(dr3);

            DataRow dr4 = dt.NewRow();
            dr4["Name"] = "Lydia";                // or dr4[0]="Lydia";
            dr4["Age"] = 36;                      // or dr4[1]=36
            dr4["Date_Employed"] = "01-Jun-2019"; // or dr4[1]="01-Jun-2019";;
            dr4["Salary"] = 37500;                // or dr4[2]=37500;
            dt.Rows.Add(dr4);

            DataRow dr5 = dt.NewRow();
            dr5["Name"] = "Desiree";              // or d5[0]="Desiree";
            dr5["Age"] = 35;                      // or dr5[1]=35;
            dr5["Date_Employed"] = "01-Jun-2018"; // or dr5[1]="01-Jun-2018";;
            dr5["Salary"] = 28000;                // or dr5[2]=28000;
            dt.Rows.Add(dr5);
            
            return dt;
        }

        public static string Formatted_Date(string YourDate)
        {
            if (Information.IsDate(YourDate) == false)
            {
                return "";
            }
            DateTime moment = Convert.ToDateTime(YourDate);
            string monthName = moment.ToString("MMM");

            string theDay = moment.Day.ToString();
            if (moment.Day < 10)
            {
                theDay = "0" + theDay;
            }

            return theDay + "-" + monthName + "-" + moment.Year;
        }
    }
}

Points of Interest

Exporting grid data to Excel or any other format does not mean you have to loop through the cells of the grid itself. A grid must have a datatable or other datasource which you can target to export the data.

In this case, I did not export the data from the grid directly, but I targeted the datatable (its datasource), which is easier for me to manipulate anytime. You may also want to adopt this strategy in future.

Challenges

I had a lot of challenges programming GridView and Datagrid especially programming them before. From my experience, a datagrid will break down while binding to datasource if a field a column is bound to does not exist in its datatable used as datasource. So I prefer not to bind at design time, but do everything at runtime programmatically.

History

• 17^th April, 2022: Initial version