Blog » C# » how to insert, update and delete using c# windows form application

how to insert, update and delete using c# windows form application

In This article will show how to Insert,Update,Delete and Display data in MySQL using C# Windows Form Application. This is simple and basic script. This script will usefulness for C# beginners.

source code


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; //import namespace

namespace Product_Info
{
  public partial class Form1 : Form
  {
    //double click db on solution exploer -> properties -> connection string
    SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=\"c:\\users\\M&S\\documents\\visual studio 2010\\Projects\\Product Info\\Product Info\\ProductDB.mdf\";Integrated Security=True;User Instance=True");
     
    public Form1()
    {
      InitializeComponent();
    }

    private void clearForm()
    {
      txtId.Clear();
      txtName.Clear();
      txtDescription.Clear();
      txtPrice.Clear();
      txtId.Focus();
    }

    private void showProducts()
    {
      try
      {
        SqlDataAdapter da = new SqlDataAdapter("select * from tblproduct",con);
        DataSet ds = new DataSet();
        da.Fill(ds, "Products");
        productsGrid.DataSource = ds;
        productsGrid.DataMember = "Products";
      }
      catch(Exception ex)
      {
        MessageBox.Show(ex.Message);
      }
    }

    private void btnInsert_Click(object sender, EventArgs e)
    {
      try
      {
        //open connection
        con.Open();
        //crete sql command
        SqlCommand cmd = new SqlCommand("insert into tblproduct values ("+txtId.Text+ ", '"+txtName.Text+"', '"+txtDescription.Text+"', "+txtPrice.Text+")", con);
        //execute command
        int n = cmd.ExecuteNonQuery(); //returns number of affected rows
        //show a message
        MessageBox.Show(n + " record inserted successfully");
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
      }
      finally
      {
        //clean up environment
        con.Close();
        clearForm();
        showProducts();
      }
    }

    private void txtId_KeyPress(object sender, KeyPressEventArgs e)
    {
      try
      {
        //if enter key pressed
        if (e.KeyChar == 13)
        {
          //open connection
          con.Open();
          //create sql command
          SqlCommand cmd = new SqlCommand("select * from tblproduct where id=" + txtId.Text, con);
          //execute sql command
          SqlDataReader rd = cmd.ExecuteReader(); //returns sql data reader object
          //extract data from data reader
          if (rd.Read())
          {
            //record exist
            txtName.Text = rd["productname"].ToString();
            txtDescription.Text = rd["description"].ToString();
            txtPrice.Text = rd["price"].ToString();
          }
          else
          {
            //record not exist
            MessageBox.Show("Record not exist");
          }
        }
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
      }
      finally
      {
        //clean up enviroment
        con.Close();
      }
    }

    private void btnEdit_Click(object sender, EventArgs e)
    {
      try
      {
        //open connection
        con.Open();
        //crete sql command
        SqlCommand cmd = new SqlCommand("update tblproduct set productname='"
          +txtName.Text+"', description='"+txtDescription.Text+"', price="
          +txtPrice.Text+" where id="+txtId.Text, con);
        //execute command
        int n = cmd.ExecuteNonQuery(); //returns number of affected rows
        //show a message
        MessageBox.Show(n + " record updated successfully");
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
      }
      finally
      {
        //clean up environment
        con.Close();
        clearForm();
        showProducts();
      }
    }

    private void btnDelete_Click(object sender, EventArgs e)
    {
      DialogResult dr = MessageBox.Show("Do you want to delete this record?", "Delete", 
        MessageBoxButtons.YesNo, MessageBoxIcon.Question);
      if (dr == DialogResult.Yes)
      {
        //delete record
        try
        {
          //open connection
          con.Open();
          //crete sql command
          SqlCommand cmd = new SqlCommand("delete from tblproduct where id=" + txtId.Text, con);
          //execute command
          int n = cmd.ExecuteNonQuery(); //returns number of affected rows
          //show a message
          MessageBox.Show(n + " record deleted successfully");
        }
        catch (Exception ex)
        {
          MessageBox.Show(ex.Message);
        }
        finally
        {
          //clean up environment
          con.Close();
          clearForm();
          showProducts();
        }
      }
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      showProducts();
    }
  }
}
ARTICLE POSTED BY : Admin

Hi, I'am k.g uththara. I'm a professional web developer. I write blog posts in my free time. if you want to contact me, send a message on contact page, thanks.