DataGridView DataTable Bind to Fill Data Using C# VB.Net

Bind DataGridView DataTable:

DataGridView is basically used to display data in tabular format though data source could be different. It could be database, XML, JSON etc. Here in this C# & VB.Net code example I will explain the most common functionality – DataGridView DataTable data binding from a SQL Server Database table. So I will populate the DataGridView DataTable data from database and after that will display or bind data in DataGridView. Note that, DataGridView is such a powerful control in windows application, you can also over ruled almost all features at run time. I will also try to share how you can display records based on your selected columns at run time. After reading hope it will help you to bind datatable to datagridview in c# windows application.

c# datagridview datatable example

Also recommending to read more articles on “DataGridView” to enhance your knowledge in Windows applications.

Prepare Dummy Data for DataGridView DataTable:

To populate data in DataGridView control we have to prepare our backend DB table with some dummy data. To do that either you can do it at your own way or can follow the below scripts:

Create Table:

CREATE TABLE [dbo].[Customer](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](max) NULL,
	[Address] [varchar](max) NULL,
	[Email] [varchar](max) NULL,
	[Phone] [varchar](max) NULL,
	[Note] [varchar](max) NULL
) ON [PRIMARY]

Insert Data:

INSERT INTO CUSTOMER VALUES(1,'Edward Fista','California, USA','fista@gmail.com','+134789xxxx','Potential Buyer')
INSERT INTO CUSTOMER VALUES(2,'Charles Timber','Boston, USA','timber@gmail.com','+184567xxxx','Quote Send')
INSERT INTO CUSTOMER VALUES(3,'Carlos Mayer','Washington, USA','mayer@gmail.com','+128695xxxx','Individually Contacted')
INSERT INTO CUSTOMER VALUES(4,'Adword Thomas','London, UK','thomas@gmail.com','+108736xxxx','Have a meeting')
INSERT INTO CUSTOMER VALUES(5,'Eniesta George','Lanka, Srilanka','george@gmail.com','+1457689xxxx','Request Send')
INSERT INTO CUSTOMER VALUES(6,'Lin Tom','Delhi, India','tom@gmail.com','+938946xxxx','Subscribed')
INSERT INTO CUSTOMER VALUES(7,'Wasim Osman','Karachi, Pakistan','wasim@gmail.com','+259835xxxx','Pending @ Sales')
INSERT INTO CUSTOMER VALUES(8,'Afsarul Alam','Dhaka, Bangladesh','afsar@gmail.com','+88034789xxxx','2 times reached')
INSERT INTO CUSTOMER VALUES(9,'Jon haward','California, USA','jon@gmail.com','+134569xxxx','Over Phone Contacted')

Ok now our backend table is ready to bind into our DataGridView. Open your project & add a DataGridView control into your windows form. Go to the code view & write or paste below code block:

C# DataGridView DataTable Example Code:

        private void Form1_Load(object sender, EventArgs e)
        {
            Load_Data();
        }

        private void Reset_DataGridView()
        {
            //Clear previous datatable
            dataGridView1.DataSource = null;
            dataGridView1.Columns.Clear();
            dataGridView1.Refresh();
        }
        
        private void Load_Data()
        {
            Reset_DataGridView();
            dataGridView1.AutoGenerateColumns = true;
            string consString = ConfigurationSettings.AppSettings["DBConnection"];
            using (SqlConnection conn = new SqlConnection(consString))
            {
                SqlCommand cmd = new SqlCommand("SELECT ID,Name,Address,Email,Phone,Note FROM [Customer]", conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                SqlDataReader drStudents = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(drStudents);
                dataGridView1.DataSource = dt;
                dataGridView1.Refresh();
            }
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Load_Data();
        }

App.config Settings:

  <appSettings>
    <add key="DBConnection" value="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;"/>
    <!--<add key="DBConnection2" value="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;"/>-->
  </appSettings>

Make the settings as per your requirement.

Relevant C# Namespaces:

using System.Configuration;
using System.Data.SqlClient;

Don’t forget to add those in your form.

Bind Specific Columns to DataGridView Control Using C#

As promised earlier here I am sharing the way how we can bind datatable to datagridview with specific column name.

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Reset_DataGridView();
            string consString = ConfigurationSettings.AppSettings["DBConnection"];
            using (SqlConnection conn = new SqlConnection(consString))
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM [Customer]", conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                SqlDataReader drStudents = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(drStudents);

                //Set AutoGenerateColumns= False
                dataGridView1.AutoGenerateColumns = false;

                //Set your desired Columns Count
                dataGridView1.ColumnCount = 4;

                //Add Columns
                dataGridView1.Columns[0].Name = "Name";
                dataGridView1.Columns[0].HeaderText = "Name";
                dataGridView1.Columns[0].DataPropertyName = "Name";

                dataGridView1.Columns[1].HeaderText = "Address";
                dataGridView1.Columns[1].Name = "Address";
                dataGridView1.Columns[1].DataPropertyName = "Address";

                dataGridView1.Columns[2].Name = "Email";
                dataGridView1.Columns[2].HeaderText = "Email";
                dataGridView1.Columns[2].DataPropertyName = "Email";

                dataGridView1.Columns[3].Name = "Phone";
                dataGridView1.Columns[3].HeaderText = "Phone";
                dataGridView1.Columns[3].DataPropertyName = "Phone";

                dataGridView1.DataSource = dt;
                dataGridView1.Refresh();

            }
        }

C# DataGridView Datatable Example Code Explanation:

Here I am using a link control to display all columns loaded in DataTable from SQL. And it will be done within the Page_Load event. If you look at the Load_Data() method you find SQL Server connection & SqlCommand to fetch the data from underlying table. After that I will populate DataTable through SqlDataReader. Finally bind the DataGridView control using the already loaded DataTable. Plain & simple. Isn’t it? Finally i would like to explain the auto generate column & datasource properties. These both are important because whenever you change the number of column to display at run time you should set it false & null respectively.

VB.Net Code to Fill DataGridView DataTable:

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Load_Data()
    End Sub
    Private Sub Reset_DataGridView()
        'Clear previous datatable
        DataGridView1.DataSource = Nothing
        DataGridView1.Columns.Clear()
        DataGridView1.Refresh()
    End Sub
    Private Sub Load_Data()
        Reset_DataGridView()
        DataGridView1.AutoGenerateColumns = True
        Dim consString As String = ConfigurationSettings.AppSettings("DBConnection")
        Using conn As New SqlConnection(consString)
            Dim cmd As New SqlCommand("SELECT ID,Name,Address,Email,Phone,Note FROM [Customer]", conn)
            cmd.CommandType = CommandType.Text
            conn.Open()
            Dim drStudents As SqlDataReader = cmd.ExecuteReader()
            Dim dt As New DataTable()
            dt.Load(drStudents)
            DataGridView1.DataSource = dt
            DataGridView1.Refresh()
        End Using
    End Sub

    Private Sub LinkLabel2_LinkClicked(sender As Object, e As LinkLabelLinkClickedEventArgs) Handles LinkLabel2.LinkClicked

        Reset_DataGridView()
        Dim consString As String = ConfigurationSettings.AppSettings("DBConnection")
        Using conn As New SqlConnection(consString)
            Dim cmd As New SqlCommand("SELECT * FROM [Customer]", conn)
            cmd.CommandType = CommandType.Text
            conn.Open()
            Dim drStudents As SqlDataReader = cmd.ExecuteReader()
            Dim dt As New DataTable()
            dt.Load(drStudents)

            'Set AutoGenerateColumns= False
            DataGridView1.AutoGenerateColumns = False

            'Set your desired Columns Count
            DataGridView1.ColumnCount = 4

            'Add Columns
            DataGridView1.Columns(0).Name = "Name"
            DataGridView1.Columns(0).HeaderText = "Name"
            DataGridView1.Columns(0).DataPropertyName = "Name"

            DataGridView1.Columns(1).HeaderText = "Address"
            DataGridView1.Columns(1).Name = "Address"
            DataGridView1.Columns(1).DataPropertyName = "Address"

            DataGridView1.Columns(2).Name = "Email"
            DataGridView1.Columns(2).HeaderText = "Email"
            DataGridView1.Columns(2).DataPropertyName = "Email"

            DataGridView1.Columns(3).Name = "Phone"
            DataGridView1.Columns(3).HeaderText = "Phone"
            DataGridView1.Columns(3).DataPropertyName = "Phone"

            DataGridView1.DataSource = dt

            DataGridView1.Refresh()
        End Using
    End Sub

    Private Sub LinkLabel1_LinkClicked(sender As Object, e As LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
        Load_Data()
    End Sub

Demo:

If you face any difficulties to understand the code please read C# code explanation because this VB.Net code block is the converted code of the above C# code.

Hope this C# DataGridView DataTable example will help you to answer “how to bind datagridview in c# windows application”. If you have any confusion or question on how to display data in datagridview in c# net windows application please share through comment.

Download C# & VB.NET Source Code:

Download C# Code Example        Download Code Example VB.Net

Posted in .Net, C#, SQL Server, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*