Objective and Prerequisites

Objective: This segment introduces implementation of how to insert (records using CSV file) and retrieve the records from a table in MySQL using C#.

Prerequisites: MySQL setup is installed and running in the background, and the database DB and table holders are created in MySQL. If not, then you need to create it by following C# program to create a database in MySQL and C# program to create a table in MySQL.

Start MySQL
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE DB;
Database changed

mysql> SHOW TABLES;
+--------------+
| Tables_in_db |
+--------------+
| holders      |
+--------------+
1 row in set (0.00 sec)

mysql> DESC holders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| account_no | bigint      | NO   | PRI | NULL    |       |
| name       | varchar(30) | NO   |     | NULL    |       |
| bank       | varchar(10) | YES  |     | NULL    |       |
| amount     | bigint      | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.20 sec)
kodingwindow@kw:~/csharp$
C# MySQL to insert and retrieve the records from a table
In the following program, we will be inserting records into a table using CSV (comma-separated values) file. Once all the records inserted, we will retrieve those records using MySQL SELECT statement and WHERE clause.
kw.csv
account_no,name,bank,amount
25622348989,James Moore,Barclays,5000
25622348990,Donald Taylor,Citi,7000
25622348991,Edward Parkar,ICICI,95000
25622348992,Ryan Bakshi,Citi,50000
25622348993,Marie Peters,DZBank,12250
Program.cs
using MySql.Data.MySqlClient;
using System;
using System.Linq;
namespace Kodingwindow
{
    class Program
    {
        public static void Main(string[] args)
        {
            MySqlConnection con = new MySqlConnection("server=localhost;database=DB;user=root;port=3306;password=passwd;");
            MySqlCommand cmd;
            con.Open();
            cmd = new MySqlCommand("INSERT INTO holders(account_no,name,bank,amount) VALUES(@account_no,@name,@bank,@amount)", con);

            string[] lines = System.IO.File.ReadAllLines(@"D:/Data/kw.csv").Skip(1).ToArray();
            cmd.Parameters.Add(new MySqlParameter("@account_no", "account_no"));
            cmd.Parameters.Add(new MySqlParameter("@name", "name"));
            cmd.Parameters.Add(new MySqlParameter("@bank", "bank"));
            cmd.Parameters.Add(new MySqlParameter("@amount", "amount"));

            foreach (string line in lines)
            {
                string[] column = line.Split(',');
                cmd.Parameters["@account_no"].Value = column[0];
                cmd.Parameters["@name"].Value = column[1];
                cmd.Parameters["@bank"].Value = column[2];
                cmd.Parameters["@amount"].Value = column[3];
                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }
            Console.WriteLine("Records inserted successfully");

            cmd = new MySqlCommand("SELECT * FROM holders", con);
            MySqlDataReader dr = cmd.ExecuteReader();
            Console.WriteLine("List of all the records");
            while (dr.Read())
            {
                Console.WriteLine(dr[0] + " " + dr[1] + " " + dr[2] + " " + dr[3]);
            }
            dr.Close();

            cmd = new MySqlCommand("SELECT * FROM holders WHERE amount >= 10000", con);
            dr = cmd.ExecuteReader();
            Console.WriteLine("\nList of the records after applying WHERE clause");
            while (dr.Read())
            {
                Console.WriteLine(dr[0] + " " + dr[1] + " " + dr[2] + " " + dr[3]);
            }
            dr.Close();
            con.Close();

            Console.ReadKey();
        }
    }
}
Output
Records inserted successfully
List of all the records
25622348989 James Moore Barclays 5000
25622348990 Donald Taylor Citi 7000
25622348991 Edward Parkar ICICI 95000
25622348992 Ryan Bakshi Citi 50000
25622348993 Marie Peters DZBank 12250

List of the records after applying WHERE clause
25622348991 Edward Parkar ICICI 95000
25622348992 Ryan Bakshi Citi 50000
25622348993 Marie Peters DZBank 12250
MySQL Instance
mysql> SELECT * FROM holders;
+-------------+---------------+----------+--------+
| account_no  | name          | bank     | amount |
+-------------+---------------+----------+--------+
| 25622348989 | James Moore   | Barclays |   5000 |
| 25622348990 | Donald Taylor | Citi     |   7000 |
| 25622348991 | Edward Parkar | ICICI    |  95000 |
| 25622348992 | Ryan Bakshi   | Citi     |  50000 |
| 25622348993 | Marie Peters  | DZBank   |  12250 |
+-------------+---------------+----------+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM holders WHERE amount >= 10000;
+-------------+---------------+--------+--------+
| account_no  | name          | bank   | amount |
+-------------+---------------+--------+--------+
| 25622348991 | Edward Parkar | ICICI  |  95000 |
| 25622348992 | Ryan Bakshi   | Citi   |  50000 |
| 25622348993 | Marie Peters  | DZBank |  12250 |
+-------------+---------------+--------+--------+
3 rows in set (0.00 sec)
kodingwindow@kw:~/csharp$
Advertisement