Load Datagridview from a Database in C#

Introduction 

 

In this article, we will learn how to load datagridview from a database in C#. C# datagridview loads data from a MySQL database. This tutorial takes a specific table from a database and displays it on a DataGridView. This is done with a DataReader and data logic. A visual representation of data is the end result.

 

Let’s follow the steps to learn how to load data in Datagridview/

1. Create a database in MySQL with name “test” and create a table with the name “user”, like shown below.

2. Create a new application project. In Visual Studio, on the menu click File> New  > Project. For more details, see the following menu on the display.

3. Then a window will open called New Project that should look like below:

4. Write down the name of the project that will be created on a field Name. Specify the directory storage project by accessing the field Location. Next, give the name of the solution in the Solution Name. Then click OK.

5. Create a new windows form like the one shown below.

Create a new class for the connection database and write the following program listing:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;

namespace DataGridview_Connect_DB
{
    class ConnectionDB
    {

        MySql.Data.MySqlClient.MySqlConnection conn;
        string myConnectionString;
        static string host = "localhost";
        static string database = "test";
        static string userDB = "camellab";
        static string password = "camellab";
        public static string strProvider = "server=" + host + ";Database=" + database + ";User ID=" + userDB + ";Password=" + password;


        public bool Open()
        {
            try
            {
                strProvider = "server=" + host + ";Database=" + database + ";User ID=" + userDB + ";Password=" + password;
                conn = new MySqlConnection(strProvider);
                conn.Open();
                return true;
            }
            catch (Exception er)
            {
                MessageBox.Show("Connection Error ! " + er.Message, "Information");
            }
            return false;
        }

        public void Close()
        {
            conn.Close();
            conn.Dispose();
        }

        public DataSet ExecuteDataSet(string sql)
        {
            try
            {
                DataSet ds = new DataSet();
                MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                da.Fill(ds, "result");
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return null;
        }

        public MySqlDataReader ExecuteReader(string sql)
        {
            try
            {
                MySqlDataReader reader;
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                reader = cmd.ExecuteReader();
                return reader;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return null;
        }

        public int ExecuteNonQuery(string sql)
        {
            try
            {
                int affected;
                MySqlTransaction mytransaction = conn.BeginTransaction();
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                affected = cmd.ExecuteNonQuery();
                mytransaction.Commit();
                return affected;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return -1;
        }
    }
}

Next step, Back to windows form and view code to write the following program listing,

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Collections;

namespace DataGridview_Connect_DB
{
    public partial class Form1 : Form
    {
        ConnectionDB con = new ConnectionDB();

        //arraylist to getter and setter data
        private static ArrayList ListID = new ArrayList();
        private static ArrayList ListFirstname = new ArrayList();
        private static ArrayList ListLastname = new ArrayList();
        private static ArrayList ListTelephone = new ArrayList();
        private static ArrayList ListAddress = new ArrayList();

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            GetData();
            if (ListID.Count > 0)
            {
                updateDatagrid();
            }
            else
            {
                MessageBox.Show("Data not found");
            }
        }

        private void GetData()
        {
            try
            {
                con.Open();
                string query = "select id,firstname,lastname,telephone,address from user";

                //MySqlDataReader row;
                MySqlDataReader row;
                row = con.ExecuteReader(query);
                if (row.HasRows)
                {
                    while (row.Read())
                    {
                        ListID.Add(row["id"].ToString());
                        ListFirstname.Add(row["firstname"].ToString());
                        ListLastname.Add(row["lastname"].ToString());
                        ListTelephone.Add(row["telephone"].ToString());
                        ListAddress.Add(row["address"].ToString());
                    }
                }
                else
                {
                    MessageBox.Show("Data not found");
                }

                con.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }

        }

        private void updateDatagrid()
        {
            dataGridView1.Rows.Clear();
            for (int i = 0; i < ListID.Count; i++)
            {
                DataGridViewRow newRow = new DataGridViewRow();

                newRow.CreateCells(dataGridView1);
                newRow.Cells[0].Value = ListID[i];
                newRow.Cells[1].Value = ListFirstname[i];
                newRow.Cells[2].Value = ListLastname[i];
                newRow.Cells[3].Value = ListTelephone[i];
                newRow.Cells[4].Value = ListAddress[i];
                dataGridView1.Rows.Add(newRow);
            }
        }
    }
}

After you write down the program listings, press the F5 key to run the program and if you successfull connect your database the result is,

 

We have explained how to make a program in C# datagridview load data from a database. For those of you who want to download the source code of the program, you also can. Hopefully this discussion was helpful to you.

 

You can see Load Datagridview From Database C# from the Github project Here.

 

Thank you for reading this article! I hope it was useful to you. Visit My Github about .Net Csharp Here.