« Previous Next »

Local Declaration Access

Design



Understanding using System.Data.OleDb;

1. Meaning:

The System.Data.OleDb namespace in C# is used to enable database connectivity and interaction using OLE DB technology. This allows applications to communicate with various data sources like Microsoft Access, Excel, and SQL Server.

2. Full Form of OleDb:

Object Linking and Embedding Database

3. What is OLE DB?

OLE DB is a Microsoft technology designed to provide a uniform way of accessing different types of data sources. It enables applications to connect with databases such as MS Access, Excel, and SQL Server, facilitating seamless data retrieval and management.

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 System.Data.OleDb; // Database Connectivity using OleDb

namespace Insert_Update_Delete_Refrsh
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent(); //Initializes UI components
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //  Loads data from Student.accdb into DataGridView when form opens
            this.batch1TableAdapter.Fill(this.studentDataSet.Batch1);
        }

        private void btnInsert_Click(object sender, EventArgs e) //  Insert Data (button1)
        {
            OleDbConnection con = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();

            cmd.Connection = con;
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AIT_PC_5\Documents\Student.accdb";
            con.Open();

            // 📝 Insert Query: Adds new student record into the Batch1 table
            cmd.CommandText = "Insert Into [Table_Name] Values (" + textBox1.Text + ",'" + textBox2.Text + "','" + textBox3.Text + "'," + textBox4.Text + ")";
            cmd.ExecuteNonQuery();
            MessageBox.Show("✅ Record Inserted Successfully!");
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
        
            cmd.Connection = con;
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AIT_PC_5\Documents\Student.accdb";
            con.Open();
        
            // 📝 Update Query: Modifies student record in the Batch1 table
            cmd.CommandText = "Update [Table_Name] Set Student_Course = '" + textBox3.Text + "' Where Student_ID = " + textBox1.Text + " " ;
            cmd.ExecuteNonQuery();
            MessageBox.Show("✅ Record Updated Successfully!");
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
        
            cmd.Connection = con;
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AIT_PC_5\Documents\Student.accdb";
            con.Open();
        
            // 🗑️ Delete Query: Removes student record based on ID
            cmd.CommandText = "Delete From [Table_Name] Where Student_ID =" + textBox1.Text + " " ; 
            cmd.ExecuteNonQuery();
            MessageBox.Show(" Record Deleted Successfully!");
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
        
            cmd.Connection = con;
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AIT_PC_5\Documents\Student.accdb";
            con.Open();
        
            // 🔍 Search Query: Retrieves student record based on ID
            cmd.CommandText = "Select * From [Table_Name] Where Student_ID =" + textBox1.Text + " " ;
            OleDbDataReader dr = cmd.ExecuteReader();
        
            if (dr.Read())
            {
                textBox1.Text = dr[0].ToString();
                textBox2.Text = dr[1].ToString();
                textBox3.Text = dr[2].ToString();
                textBox4.Text = dr[3].ToString();
            }
            MessageBox.Show("🔍 Record Found!");
        }

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
        
            cmd.Connection = con;
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AIT_PC_5\Documents\Student.accdb";
            con.Open();
        
            // 🔍 Dynamic Search: Filters results as the user types
            cmd.CommandText = "Select * From [Table_Name] Where Student_Name Like '%" + textBox5.Text + "%'";
            OleDbDataReader dr = cmd.ExecuteReader();
        
            DataTable dt = new DataTable();
            dt.Load(dr);
        
            dataGridView1.DataSource = dt;
            dataGridView1.Refresh();
        }

        private void btnShowAll_Click(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
        
            cmd.Connection = con;
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AIT_PC_5\Documents\Student.accdb";
            con.Open();
        
            // 🔄 ShowAll Query: Loads all records into DataGridView
            cmd.CommandText = "Select * From [Table_Name]";
            OleDbDataReader dr = cmd.ExecuteReader();
        
            DataTable dt = new DataTable();
            dt.Load(dr);
        
            dataGridView1.DataSource = dt;
            dataGridView1.Refresh();
        }
    }
}

Output

« Previous Next »