C# Program for Database Connection with MySQL

Sample C# Windows application program to connect MySql Database to do operation such as Select, Add, Edit, Delete and update.

Database Name : Viber
Table Name : Viber_Users
Columns in Table : Member_Id, Member_Name, State, Country

Screen Design

   
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;
using MySql.Data.MySqlClient;
namespace WinApp
{
    public partial class Viber2 : Form
    {
		Database db = new Database();
		public Viber2()
        {
            InitializeComponent();
        }

        private void Viber2_Load(object sender, EventArgs e)
        {

        }


		class Database
		{
			private MySqlConnection con;
			private MySqlCommand cmd;
			private MySqlDataAdapter da;

			public Database() // Default Constructor
			{
				con = new MySqlConnection("server=localhost;user=ganesh;database=viber;password=DSHUc4kzLJCp1Gho");
			}
			public void openConnection()
			{
				con.Open();
			}

			public void closeConnection()
			{
				con.Close();
			}

			public int save_update_delete(string a)
			{
				openConnection();
				cmd = new MySqlCommand(a, con);
				int i = cmd.ExecuteNonQuery();
				closeConnection();
				return i;
			}
			public DataTable getData(string a)
			{
				openConnection();
				da = new MySqlDataAdapter(a, con);
				DataTable dt = new DataTable();
				da.Fill(dt);
				closeConnection();
				return dt;
			}
		}

		private void btn_add_Click(object sender, EventArgs e)
		{
			int i = db.save_update_delete("Insert into Viber_Users values (" + Convert.ToInt16(txt_memid.Text) 
				+ ",'" 	+ txt_memname.Text + "', '" + txt_state.Text + "', '" + txt_country.Text + "')");
			if (i == 1)
				MessageBox.Show("Data Inserted Succesfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
			else
				MessageBox.Show("Data not Inserted", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
		}

		private void btn_list_Click(object sender, EventArgs e)
		{
			dataGridView1.DataSource = db.getData("select * from viber_users");
		}

		private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)		
		{
			txt_memid.Text= dataGridView1.CurrentRow.Cells[0].Value.ToString();
			txt_memname.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
			txt_state.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
			txt_country.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
		
		}

		private void btn_edit_Click(object sender, EventArgs e)
		{
			DialogResult dr = MessageBox.Show("Do you really want to Update?", "Infromation", MessageBoxButtons.YesNo, MessageBoxIcon.Information);

			if (dr.ToString() == "Yes")
			{
				int i = db.save_update_delete("Update Viber_Users set Member_Name= '" + txt_memname.Text
					+ "', State= '" + txt_state.Text
					+ "', Country= '" + txt_country.Text + "'"
					+ " where Member_Id= " + Convert.ToInt16(txt_memid.Text));

				if (i == 1)
					MessageBox.Show("Data Update Successfully", "Infromation", MessageBoxButtons.OK, MessageBoxIcon.Information);
				else
					MessageBox.Show("Data Cannot Update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

		private void btn_delete_Click(object sender, EventArgs e)
		{
			DialogResult dr = MessageBox.Show("Do you really want to Delete?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);

			if (dr.ToString() == "Yes")
			{
				int i = db.save_update_delete("Delete from Viber_Users where Member_Id= " + Convert.ToInt16(txt_memid.Text));
				if (i == 1)
					MessageBox.Show("Data Delete Successfully", "Infromation", MessageBoxButtons.OK, MessageBoxIcon.Information);
				else
					MessageBox.Show("Data Cannot Delete", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

		private void btn_search_Click(object sender, EventArgs e)
		{
			if (txt_search.TextLength > 0)
			{
				if (rbn_id.Checked == true)
				{
					dataGridView1.DataSource = db.getData("select * from Viber_Users where Member_Id =" + Convert.ToInt16(txt_search.Text));
				}
				else
				{
					dataGridView1.DataSource = db.getData("select * from Viber_Users where Member_Name like '" + txt_search.Text + "%' ");
				}
			}
			else
			{
				dataGridView1.DataSource = db.getData("select * from Viber_Users");
			}
		}
	}
}
  

---