Skip to main content

How to Save and Retrieve Data in the ListView Using C#

This time, I will teach you how to save and retrieve data in the listview using c# and MySql database. This is a simple method that you can do in no time. The program contains the following function such as saving data in the database and it will be retrieved in the listview control. Follow the step by step guide to know how the program works. I use Visual Studio 2015 to develop this program.

Creating a Database

Create a database in the “phpmyadmin” and named it “tuts_dbperson”. After that, execute the following query to create a table in the database that you have created.
  1.  
  2. CREATE TABLE `tuts_dbperson`.`tblperson` ( `PersonID` INT NOT NULL AUTO_INCREMENT ,`Fullname` VARCHAR(90) NOT NULL , `Address` VARCHAR(90) NOT NULL , PRIMARY KEY(`PersonID`)) ENGINE = InnoDB;

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application in c#. figure 1

Step 2

Add a ListView, two buttons, two texboxes, and two labels after that do the form just like shown below. figure 2

Step 3

Add MySQL.Data.dll as references.

Step 4

Press F7 to open the code editor. In the code editor, add a namespace to access MySQL libraries.
  1.  
  2. using MySql.Data.MySqlClient;

Step 5

Create a connection between c# and MySQL database and declare all the classes that are needed.
  1.  
  2. MySqlConnection con = new MySqlConnection("server=localhost;user id=root;Password=;Database=tuts_dbperson;sslMode=none");
  3. MySqlCommand cmd = new MySqlCommand();
  4. MySqlDataAdapter da = new MySqlDataAdapter();
  5. DataTable dt = new DataTable();
  6. string sql;
  7. int result;s

Step 6

Double click the “Save” button and do the following code for saving data in the database.
  1.  
  2. try
  3. {
  4. con.Open();
  5. cmd = new MySqlCommand();
  6. da = new MySqlDataAdapter();
  7. dt = new DataTable();
  8.  
  9. sql = "INSERT INTO `tblperson` (`Fullname`, `Address`) VALUES('" + txtName .Text + "','"+ txtAddress .Text + "')";
  10.  
  11. cmd.Connection = con;
  12. cmd.CommandText = sql;
  13. result = cmd.ExecuteNonQuery();
  14. if(result > 0)
  15. {
  16. MessageBox.Show("Data has been saved in the database");
  17. }
  18. else
  19. {
  20. MessageBox.Show("Error to execute the query");
  21. }
  22.  
  23. }
  24. catch (Exception ex)
  25. {
  26. MessageBox.Show(ex.Message);
  27. }
  28. finally
  29. {
  30. con.Close();
  31. }

Step 5

Double click the “Retrieve” button and do the following code for retrieving data in the database.
  1.  
  2. try
  3. {
  4. con.Open();
  5. cmd = new MySqlCommand();
  6. da = new MySqlDataAdapter();
  7. dt = new DataTable();
  8.  
  9. sql = "SELECT * FROM `tblperson`";
  10.  
  11. cmd.Connection = con;
  12. cmd.CommandText = sql;
  13.  
  14. da.SelectCommand = cmd;
  15. da.Fill(dt);
  16.  
  17. listView1.Items.Clear();
  18.  
  19. foreach(DataRow r in dt.Rows)
  20. {
  21. ListViewItem list = listView1.Items.Add(r.Field<string>(1));
  22. list.SubItems.Add(r.Field<string>(2));
  23. }
  24.  
  25.  
  26.  
  27. }
  28. catch (Exception ex)
  29. {
  30. MessageBox.Show(ex.Message);
  31. }
  32. finally
  33. {
  34. con.Close();
  35. da.Dispose();
  36. }
The complete source code is included. You can download it and run it on your computer. For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.

Add new comment