Insert Update Delete and Search Data in local databases Using C#

This article shows you how to insert, update, delete,search by name ,Roll no,marks and display data in Listbox 

Introduction

Here I am using  local databases.

Note: You need to include this assembly reference.

 using MySql.Data.MySqlClient;

SQL Connection: 

First, you need to  establish Local database connection for  databases 

namespace SQL_Database
{
    public partial class Form1 : Form
    {// Delcare Sql class objects
        SqlConnection cn;
        SqlDataAdapter da;
        DataSet ds;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            String con_str = @"Data Source=(local);Initial Catalog=BZU;Integrated Security=True";
            cn = new SqlConnection(con_str);

            try
            {
                cn.Open();   // open database connection
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Insert Data

private void button1_Click(object sender, EventArgs e)
        {
            String query = "Insert into Student(RollNo, Name, Marks) Values(@RollNo, @Name, @Marks)";
            SqlCommand cmd = new SqlCommand(query, cn);

            cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 15);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@Marks", SqlDbType.Int);

            cmd.Parameters["@RollNo"].Value = textBox1.Text;
            cmd.Parameters["@Name"].Value = textBox2.Text;
            cmd.Parameters["@Marks"].Value = textBox3.Text;

            try
            {
                int i = cmd.ExecuteNonQuery();
                if (i == 0)
                    MessageBox.Show("No Record inserted");
                if (i == 1)
                    MessageBox.Show("One Record inserted into database Successfully");
                ClearFields();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void ClearFields()
        {
            textBox1.Clear();
            textBox2.Clear();
            textBox3.Clear();
        }

Update Data

 private void button2_Click(object sender, EventArgs e)
        {
            string query = "Update Student Set Name=@Name,Marks=@Marks where RollNo=@RollNo ";
            SqlCommand cmd = new SqlCommand(query, cn);

            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@Marks", SqlDbType.Int);
            cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 15);

            cmd.Parameters["@Name"].Value = textBox2.Text;
            cmd.Parameters["@Marks"].Value = int.Parse(textBox3.Text);
            cmd.Parameters["@RollNo"].Value = textBox1.Text;
            try
            {
                int i = cmd.ExecuteNonQuery();
                if (i == 0)
                    MessageBox.Show("Record not found");
                if (i == 1)
                    MessageBox.Show("One Record updated successfully");
                ClearFields();
            }

            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Delete Data

private void button3_Click(object sender, EventArgs e)
        {
            String query = "Delete from Student Where RollNo=@RollNo";
            SqlCommand cmd = new SqlCommand(query, cn);
            cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 10);
            cmd.Parameters["@RollNo"].Value = textBox1.Text;

            try
            {
                int i = cmd.ExecuteNonQuery();
                if (i == 0)
                    MessageBox.Show("Record not found");
                if (i == 1)
                    MessageBox.Show("One Record Deleted Successfully");
                ClearFields();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Display Data

   private void button6_Click(object sender, EventArgs e)
        {
            try
            {
                String query = "Select * from Student";
                da = new SqlDataAdapter(query, cn);
                ds = new DataSet();
                da.Fill(ds, "Students");
                dgStudent.DataSource = ds;
                dgStudent.DataMember = "Students";
                dgStudent.Refresh();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Search By Name

private void button5_Click(object sender, EventArgs e)
        {
            String query = "Select * from Student where Name = @Name";
            SqlCommand cmd = new SqlCommand(query, cn);

            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50);
            cmd.Parameters["@Name"].Value = textBox2.Text; 
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            try
            {
                DataSet ds = new DataSet();
                da.Fill(ds, "Students");
                dgStudent.DataSource = ds;
                dgStudent.DataMember = "Students";
                dgStudent.Refresh();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Search By RollNo

private void button4_Click(object sender, EventArgs e)
        {
            String query = "Select * from Student where RollNo = @RollNo";
            SqlCommand cmd = new SqlCommand(query, cn);

            cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 10);
            cmd.Parameters["@RollNo"].Value = textBox1.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            try
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count == 0)
                    MessageBox.Show("Record not found");
                else
                {
                    textBox2.Text = dt.Rows[0]["Name"].ToString();
                    textBox3.Text = dt.Rows[0]["Marks"].ToString();
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Search By Marks

private void button7_Click(object sender, EventArgs e)
        {
            String query = "Select * from Student where Marks = @Marks";
            SqlCommand cmd = new SqlCommand(query, cn);

            cmd.Parameters.Add("@Marks", SqlDbType.Int);
            cmd.Parameters["@Marks"].Value = Convert.ToInt32(textBox3.Text);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            try
            {
                DataSet ds = new DataSet();
                da.Fill(ds, "Students");
                dgStudent.DataSource = ds;
                dgStudent.DataMember = "Students";
                dgStudent.Refresh();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }