Click here to Skip to main content
15,352,974 members
Articles / Programming Languages / Visual Basic
Tip/Trick
Posted 17 Apr 2022

Tagged as

Stats

2.9K views
82 downloads
5 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
17 Apr 2022CPOL3 min read
Many a times, we need to programmatically manage GridView/DataGrid in Visual Studio web applications and this looks very difficult because we do not know what properties or events of the control to use. It may also be as a result of time constraint.
This project is a simple Visual Studio 2015 (Framework 3.5 ) project written in C# with a web form and GridView to show management of GridView programmatically. It shows population of cells and aggregation of data. It also shows how the data can be exported to Excel worksheet using comma separated values (CSV).

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.

VB.NET
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.

C#
// 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

  • 17th April, 2022: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raphael Adeniji
CEO Completesoft Global Solutions Limited
Unknown
Adeniji, Raphael ‘Gbenga BSc (Hons), MSc, MCSD
Mr. Adeniji Raphael Gbenga graduated with second class upper division from the Computer Science department of the Federal University of Technology, Akure in 1995. He also has Masters in Computer Science from the University of Lagos in 2005/2006..
He was one of the first few people to bag the highest level of Microsoft Certification, Microsoft Certified Solutions Developer (MCSD - Aug 2001), in the early days of Microsoft certifications- August 2001.

He has worked with different software houses at home and abroad. He has visited India, United Arab Emirates, United Kingdom and United States for the purpose of software development and boosting in Africa. He worked in Wadof Software Consulting, Computer Systems Associates (CSA) and Precise Financial Systems (PFS) before starting Completesoft Global Solutions Ltd in Jan 2007. He rose to the level of General Manager (GM) and Chief Technology Officer (CTO) in PFS before his final disengagement to form CompleteSoft Global Solutions Limited (Formerly Completesoft Company Ltd) with some of his colleagues. He has also worked as a Contractor and Consultant to Ericsson North America (Plano Texas, USA).
He currently works as Managing Partner and Cloud Architect/Consultant of the Company.

Comments and Discussions

 
-- There are no messages in this forum --