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.
Load Datagridview From Database In C#
  1. 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.
Load Datagridview From Database In C#
  1. Then a window will open called New Project that should look like below:
Load Datagridview From Database In C#
  1. 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.

Load Datagridview From Database In C#

  1. Create a new windows form like the one shown below.
Load Datagridview From Database In C#
Create a new class for the connection database and write the following program listing:
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using MySql.Data.MySqlClient;
  6. using System.Windows.Forms;
  7. using System.Data;
  9. namespace DataGridview_Connect_DB
  10. {
  11. class ConnectionDB
  12. {
  14. MySql.Data.MySqlClient.MySqlConnection conn;
  15. string myConnectionString;
  16. static string host = "localhost";
  17. static string database = "test";
  18. static string userDB = "camellab";
  19. static string password = "camellab";
  20. public static string strProvider = "server=" + host + ";Database=" + database + ";User ID=" + userDB + ";Password=" + password;
  23. public bool Open()
  24. {
  25. try
  26. {
  27. strProvider = "server=" + host + ";Database=" + database + ";User ID=" + userDB + ";Password=" + password;
  28. conn = new MySqlConnection(strProvider);
  29. conn.Open();
  30. return true;
  31. }
  32. catch (Exception er)
  33. {
  34. MessageBox.Show("Connection Error ! " + er.Message, "Information");
  35. }
  36. return false;
  37. }
  39. public void Close()
  40. {
  41. conn.Close();
  42. conn.Dispose();
  43. }
  45. public DataSet ExecuteDataSet(string sql)
  46. {
  47. try
  48. {
  49. DataSet ds = new DataSet();
  50. MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
  51. da.Fill(ds, "result");
  52. return ds;
  53. }
  54. catch (Exception ex)
  55. {
  56. MessageBox.Show(ex.Message);
  57. }
  58. return null;
  59. }
  61. public MySqlDataReader ExecuteReader(string sql)
  62. {
  63. try
  64. {
  65. MySqlDataReader reader;
  66. MySqlCommand cmd = new MySqlCommand(sql, conn);
  67. reader = cmd.ExecuteReader();
  68. return reader;
  69. }
  70. catch (Exception ex)
  71. {
  72. MessageBox.Show(ex.Message);
  73. }
  74. return null;
  75. }
  77. public int ExecuteNonQuery(string sql)
  78. {
  79. try
  80. {
  81. int affected;
  82. MySqlTransaction mytransaction = conn.BeginTransaction();
  83. MySqlCommand cmd = conn.CreateCommand();
  84. cmd.CommandText = sql;
  85. affected = cmd.ExecuteNonQuery();
  86. mytransaction.Commit();
  87. return affected;
  88. }
  89. catch (Exception ex)
  90. {
  91. MessageBox.Show(ex.Message);
  92. }
  93. return -1;
  94. }
  95. }
  96. } 

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





    
     
  1. using System;  
    2. 
     
  2. using System.Collections.Generic;  
    3. 
     
  3. using System.ComponentModel;  
    4. 
     
  4. using System.Data;  
    5. 
     
  5. using System.Drawing;  
    6. 
     
  6. using System.Linq;  
    7. 
     
  7. using System.Text;  
    8. 
     
  8. using System.Windows.Forms;  
    9. 
     
  9. using MySql.Data.MySqlClient;  
    10. 
     
  10. using System.Collections;  
    11. 
     
  11.   
    12. 
     
  12. namespace DataGridview_Connect_DB  
    13. 
     
  13. {  
    14. 
     
  14.     public partial class Form1 : Form  
    15. 
     
  15.     {  
    16. 
     
  16.         ConnectionDB con = new ConnectionDB();  
    17. 
     
  17.   
    18. 
     
  18.         //arraylist to getter and setter data  
    19. 
     
  19.         private static ArrayList ListID = new ArrayList();  
    20. 
     
  20.         private static ArrayList ListFirstname = new ArrayList();  
    21. 
     
  21.         private static ArrayList ListLastname = new ArrayList();  
    22. 
     
  22.         private static ArrayList ListTelephone = new ArrayList();  
    23. 
     
  23.         private static ArrayList ListAddress = new ArrayList();  
    24. 
     
  24.          
    25. 
     
  25.         public Form1()  
    26. 
     
  26.         {  
    27. 
     
  27.             InitializeComponent();  
    28. 
     
  28.         }  
    29. 
     
  29.   
    30. 
     
  30.         private void button1_Click(object sender, EventArgs e)  
    31. 
     
  31.         {  
    32. 
     
  32.             GetData();  
    33. 
     
  33.             if (ListID.Count > 0)  
    34. 
     
  34.             {  
    35. 
     
  35.                 updateDatagrid();  
    36. 
     
  36.             }  
    37. 
     
  37.             else  
    38. 
     
  38.             {  
    39. 
     
  39.                 MessageBox.Show("Data not found");  
    40. 
     
  40.             }  
    41. 
     
  41.         }  
    42. 
     
  42.   
    43. 
     
  43.         private void GetData()  
    44. 
     
  44.         {  
    45. 
     
  45.             try  
    46. 
     
  46.             {  
    47. 
     
  47.                 con.Open();  
    48. 
     
  48.                 string query = "select id,firstname,lastname,telephone,address from user";  
    49. 
     
  49.   
    50. 
     
  50.                 //MySqlDataReader row;  
    51. 
     
  51.                 MySqlDataReader row;  
    52. 
     
  52.                 row = con.ExecuteReader(query);  
    53. 
     
  53.                 if (row.HasRows)  
    54. 
     
  54.                 {  
    55. 
     
  55.                     while (row.Read())  
    56. 
     
  56.                     {  
    57. 
     
  57.                         ListID.Add(row["id"].ToString());  
    58. 
     
  58.                         ListFirstname.Add(row["firstname"].ToString());  
    59. 
     
  59.                         ListLastname.Add(row["lastname"].ToString());  
    60. 
     
  60.                         ListTelephone.Add(row["telephone"].ToString());  
    61. 
     
  61.                         ListAddress.Add(row["address"].ToString());  
    62. 
     
  62.                     }  
    63. 
     
  63.                 }  
    64. 
     
  64.                 else  
    65. 
     
  65.                 {  
    66. 
     
  66.                     MessageBox.Show("Data not found");  
    67. 
     
  67.                 }  
    68. 
     
  68.   
    69. 
     
  69.                 con.Close();  
    70. 
     
  70.             }  
    71. 
     
  71.             catch (Exception err)  
    72. 
     
  72.             {  
    73. 
     
  73.                 MessageBox.Show(err.ToString());  
    74. 
     
  74.             }  
    75. 
     
  75.   
    76. 
     
  76.         }  
    77. 
     
  77.   
    78. 
     
  78.         private void updateDatagrid()  
    79. 
     
  79.         {  
    80. 
     
  80.             dataGridView1.Rows.Clear();  
    81. 
     
  81.             for (int i = 0; i < ListID.Count; i++)  
    82. 
     
  82.             {  
    83. 
     
  83.                 DataGridViewRow newRow = new DataGridViewRow();  
    84. 
     
  84.   
    85. 
     
  85.                 newRow.CreateCells(dataGridView1);  
    86. 
     
  86.                 newRow.Cells[0].Value = ListID[i];  
    87. 
     
  87.                 newRow.Cells[1].Value = ListFirstname[i];  
    88. 
     
  88.                 newRow.Cells[2].Value = ListLastname[i];  
    89. 
     
  89.                 newRow.Cells[3].Value = ListTelephone[i];  
    90. 
     
  90.                 newRow.Cells[4].Value = ListAddress[i];  
    91. 
     
  91.                 dataGridView1.Rows.Add(newRow);  
    92. 
     
  92.             }  
    93. 
     
  93.         }  
    94. 
     
  94.     }  
    95. 
     
  95. }  
    
     
    96. 






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,

Load Datagridview From Database In C#
 
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.