Insert update delete navigate using textbox and display result in listview

In this blog we will know how to Insert update delete navigate using textbox and display result in listview.

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
            <add key="dsn" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\EMP.mdb" />
      </appSettings>
</configuration>

Form1.cs

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.OleDb;
namespace Insert_update_delete_navigate
{
    public partial class Form1 : Form
    {
        string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["dsn"];
        OleDbCommand com;
        OleDbDataAdapter oledbda;
        string str;
        DataSet ds;
        byte flag;
        BindingManagerBase bm;
        DataTable dt;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            listView1.Columns.Add("ID", 70, HorizontalAlignment.Center);
            listView1.Columns.Add("NAME", 70, HorizontalAlignment.Center);
            listView1.View = View.Details;
            listView1.GridLines = true;
            listView1.BackColor = Color.Aqua;
            listView1.ForeColor = Color.Blue;

            OleDbConnection con = new OleDbConnection(ConnectionString);
            con.Open();
            str = "select * from student";
            com = new OleDbCommand(str, con);
            oledbda = new OleDbDataAdapter(com);
            ds = new DataSet();
            oledbda.Fill(ds, "student");
            textBox1.DataBindings.Add("Text", ds, "student.id");
            textBox2.DataBindings.Add("Text", ds, "student.name");
            bm = this.BindingContext[ds, "student"];
            con.Close();
            listviewdisplay();
        }

        private void listviewdisplay()
        {
            listView1.Items.Clear();
            OleDbConnection con = new OleDbConnection(ConnectionString);
            con.Open();
            str = "select * from student";
            com = new OleDbCommand(str, con);
            oledbda = new OleDbDataAdapter(com);
            ds = new DataSet();
            oledbda.Fill(ds, "student");
            con.Close();
            dt = ds.Tables["student"];
            int i;
            for (i = 0; i <= dt.Rows.Count - 1; i++)
            {
                listView1.Items.Add(dt.Rows[i].ItemArray[0].ToString());
                listView1.Items[i].SubItems.Add(dt.Rows[i].ItemArray[1].ToString());
            }
        }

        private void btn_first_Click(object sender, EventArgs e)
        {
            bm.Position = 0;
        }
 
        private void btn_next_Click(object sender, EventArgs e)
        {
            bm.Position += 1;
        }

        private void btn_previous_Click(object sender, EventArgs e)
        {
            bm.Position -= 1;
        }

        private void btn_last_Click(object sender, EventArgs e)
        {
            bm.Position = bm.Count - 1;
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox1.Focus();
            flag = 1;
        }

        private void btn_modify_Click(object sender, EventArgs e)
        {
            flag = 2;
        }

        private void btn_save_Click(object sender, EventArgs e)
        {
            if (flag == 1)
            {
                OleDbConnection con = new OleDbConnection(ConnectionString);
                con.Open();
                str = "insert into student(id,name) values (@id,@name)";
                com = new OleDbCommand(str, con);
                com.Parameters.AddWithValue("@sid", textBox1.Text);
                com.Parameters.AddWithValue("@sname", textBox2.Text);
                com.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Records Successfuly Inserted");
                listviewdisplay();
            }

            if (flag == 2)
            {
                OleDbConnection con = new OleDbConnection(ConnectionString);
                con.Open();
                str = "update student set name='" + textBox2.Text.Trim() + "' where id='" + textBox1.Text.Trim() + "'";
                com = new OleDbCommand(str, con);
                com.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Records Successfuly Updated");
                listviewdisplay();
            }
        }

        private void btn_delete_Click(object sender, EventArgs e)
        {

            DialogResult result;
            result = MessageBox.Show("Are You sure want to Delete?", "Conformation", MessageBoxButtons.YesNo);
            if (result == System.Windows.Forms.DialogResult.Yes)
            {
                OleDbConnection con = new OleDbConnection(ConnectionString);
                str = "delete from student where id=@id";
                com = new OleDbCommand(str, con);
                com.Parameters.AddWithValue("@id", textBox1.Text);
                con.Open();
                com.ExecuteNonQuery();
                MessageBox.Show("Records Successfuly Deleted");
                listviewdisplay();
                con.Close();
            }
            else
            {
                MessageBox.Show("No action");
            }
           
        }
    }
}

 

Thanks for reading