How to Load Data in the Listview Using C# and MySQL Database

Submitted by janobe on Thursday, January 3, 2019 - 14:36.

The Listview control is used to display a collection of items. This also provides many ways to arrange and display items with item text and an icon (optionally) is used to determine the type of item. So, now I’m going to teach you how to load data in the ListView using C# and MySQL Database. This is just a simple program but I’m pretty sure that this method will help you when you encounter problem of displaying set of data in the database and can be displayed in the Listview. So let’s get started.

Creating Database

Create a database and named it “salesdb”; Execute the following query to add the table with data in the database.

CREATE TABLE `tblsales` (
  `SalesId` int(11) NOT NULL,
  `TRANSDATE` date NOT NULL,
  `Product` varchar(90) NOT NULL,
  `TotalSales` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tblsales`
--
 
INSERT INTO `tblsales` (`SalesId`, `TRANSDATE`, `Product`, `TotalSales`) VALUES
(1, '2018-01-30', 'Cellphone', 1400),
(2, '2018-02-28', 'Laptop', 800),
(3, '2018-03-31', 'Desktop', 5052),
(4, '2019-04-30', 'Ipod', 8030),
(5, '2019-05-31', 'Tablet', 10000);

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application. createapp

Step 2

Add a ListView inside the Form and do the Form just like this. formListView

Step 3

Open the code editor and add a namespace to access MySQL Library.

using MySql.Data.MySqlClient;

Step 4

Create a method for retrieving data in the database to be displayed in the ListView.

private void LoadData()
        {
            MySqlConnection con = new MySqlConnection("server=localhost;user id=root;password=;database=salesdb;sslMode=none");
            MySqlCommand cmd;
            MySqlDataAdapter da;
            DataTable dt;
             
 
            try
            {
                con.Open();
                cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "Select * FROM tblsales";
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                dt = new DataTable();
                da.Fill(dt);
 
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ListViewItem listitem = new ListViewItem(dr["SalesId"].ToString());
                    listitem.SubItems.Add(dr["TRANSDATE"].ToString());
                    listitem.SubItems.Add(dr["Product"].ToString());
                    listitem.SubItems.Add(dr["TotalSales"].ToString());
                    listView1.Items.Add(listitem);
                }
 
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close(); 
            }
        }

Step 5

Call the method that you have created and put it inside the Load event handler to perform in the first load of the Form

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

Add new comment

4214 views