• September 23, 2021

PHSARIT

Bring Technology Ideas with you

 Simple Insert Data into MySQL Server using C#

For simple select statement by getting data from MySQL Server with create connection to it, please visit Simple Select Data From MySQL Server into Datagridview using C# as our previous post. Now to work from your C# Window Form by add data from the textbox into MySql server, please go to your Window Application Form and design as your wishes. For example:

    You can create any database or table by yourself. The above just an example of how we can insert data from textbox in your C# Window Form into MySql server and retrieve into Datagridview. By designing as the image the above, please add Button and put the name as Save and other textbox names as your own one.There are 3 steps.

    First you need to create connection to MySQL as the code below:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using MySql.Data.MySqlClient;
    
    namespace SQL_Insert
    {
        public partial class Form1 : Form
        {
           MySqlConnection cn;
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                //declare connection to mysql server
                String StringConnection;
                StringConnection = "server=localhost; userid=root; password=; database=pos;";
                cn = new MySqlConnection(StringConnection);
                try
                {
                    cn.Open(); //open connection
                    MessageBox.Show("Connected successfully");
                }
                catch (Exception ex)
                {
                    //if there is no connection, it will show
                    MessageBox.Show(ex.ToString());
                }

    To insert data into MySQL Server, please copy the code as below:

    private void bntSave_Click(object sender, EventArgs e)
            {
                try
                {
                 string insert_data= "insert into tbl_product(Product_ID, ProductName,Category, Amount, Unit_Price) values('" + txtid.Text +"','"+txtname.Text+"','" + txtcate +"','"+ txtamount.Text +"','"+ txtunitprice.Text +"')";
                MySqlCommand cm = new MySqlCommand(insert_data, cn);
                cm.CommandType = CommandType.Text;
                cm.ExecuteNonQuery();
                MessageBox.Show("Save successfully");
                    loaddata();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }

    To load Data from MySQL Server into Datagridview both after you inserted and in form load, you can create void class as below code

    void loaddata()
            {
                string mysqlselectdata;
                mysqlselectdata = "select * from tbl_product"; // select data from mysqlserver with the table name is tbl_product
                MySqlDataAdapter Adapter = new MySqlDataAdapter(mysqlselectdata, cn); //put data into memory
                MySqlCommandBuilder cmd = new MySqlCommandBuilder(Adapter); // execute table with filling from memory storing
                DataTable table = new DataTable(); // create a new table to get data
                Adapter.Fill(table); // fill data into the new table
                dataGridView1.DataSource = table;
    
            }

    Call this class on the event Form_Load in order to load data into Datagridview

      After running, you can see as the image below:

        Related post