Create, Read, Update, and Delete (CRUD) in C# with Basic Info

This code performs CRUD Operation which allows applications to add a new records, retrieve, update, and delete. This includes basic profile information such as first name, last name, and gender. However, you can add more information if you like.

Global variable.

String connectionString;

OleDbConnection conn;

OleDbDataAdapter da;

OleDbCommand cmd;

//DataSet ds;

string sql;

public int x;

I created method dbConnection that point to my database location which located to my documents

public void dbConnection()

        {

 

            connectionString = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\\Users\Juno\Documents\dbInformation.mdb";

            conn = new OleDbConnection(connectionString);

            conn.Open();

            //MessageBox.Show("Connection Open  !");

            //conn.Close();

        }

Note: In a creating your database, if you are using old version .mdb is the right format otherwise .accdb for newer version. For more info, visit this link: https://www.connectionstrings.com/access/

Again, I create another method retrieveALL that will retrieve all records stored in database.

private void retrieveALL(){

             dbConnection();

            sql = "SELECT * FROM tblProfile";

            da = new OleDbDataAdapter(sql, conn);

            DataTable dt = new DataTable();      //ds = new DataSet();

            da.Fill(dt);                         //da.Fill(ds, "dbInformation");

            dataGridView1.DataSource = dt;       //dataGridView1.DataSource = ds.Tables[0];

            conn.Close();

        }

Save button that will save new added records.

private void btnSave_Click(object sender, EventArgs e)

        {

 

            dbConnection();

            sql = "INSERT INTO tblProfile (Firstname,Lastname,Gender) VALUES ('" + txtFname.Text + "','" + txtLname.Text + "','" + cbGender.Text + "')";

            cmd = new OleDbCommand(sql, conn);

            cmd.ExecuteNonQuery();

            MessageBox.Show("Successfully Added to your Database", "New Record Added", MessageBoxButtons.OK, MessageBoxIcon.Information);

            conn.Close();

 

            //btnReadDatabase_Click(sender, e);

            retrieveALL();

        }

DataGridview allows display all stored records in database. To initiate the code, go to datagridview click event and choose cellDoubleClick. So when the users double click the cell, it will display current rows index.

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)

        {

           

            x = dataGridView1.Rows.IndexOf(dataGridView1.CurrentRow);

 

            txtFname.Text = dataGridView1.Rows[x].Cells[1].Value.ToString();

            txtLname.Text = dataGridView1.Rows[x].Cells[2].Value.ToString();

            cbGender.Text = dataGridView1.Rows[x].Cells[3].Value.ToString();

            MessageBox.Show(dataGridView1.Rows[x].Cells[0].Value.ToString());

 

        }

To update new records. The following codes initiate in update button.

private void btnUpdate_Click(object sender, EventArgs e)

        {

            dbConnection();

            int indexID;

            x = dataGridView1.Rows.IndexOf(dataGridView1.CurrentRow);

            indexID =  Convert.ToInt32((dataGridView1.Rows[x].Cells[0].Value.ToString()));

 

            sql = "UPDATE tblProfile SET Firstname= '" + txtFname.Text + "',Lastname='" + txtLname.Text +

                "',Gender='" + cbGender.Text + "' WHERE ID=" + indexID + "";

 

            cmd = new OleDbCommand(sql, conn);

            cmd.ExecuteNonQuery();

            MessageBox.Show("Successfully Updated...", "Update");

            conn.Close();

 

            //btnReadDatabase_Click(sender, e);

            retrieveALL();

        }

To delete records.

private void btnDelete_Click(object sender, EventArgs e)

        {

            dbConnection();

            int indexID;

            x = dataGridView1.Rows.IndexOf(dataGridView1.CurrentRow);

            indexID = Convert.ToInt32((dataGridView1.Rows[x].Cells[0].Value.ToString()));

 

            sql = "DELETE FROM tblProfile WHERE ID=" + indexID + "";

 

            cmd = new OleDbCommand(sql, conn);

            cmd.ExecuteNonQuery();

            MessageBox.Show("Successfully Deleted...", "Delete");

            conn.Close();

            retrieveALL();

        }

To search records.

private void btnSearch_Click(object sender, EventArgs e)

        {

            dbConnection();

            sql = "SELECT * FROM tblProfile WHERE Firstname='" + txtSearch.Text+ "' OR Lastname='" + txtSearch.Text +"'";

            da = new OleDbDataAdapter(sql, conn);

            DataTable dt = new DataTable();      //ds = new DataSet();

            da.Fill(dt);                         //da.Fill(ds, "dbInformation");

            dataGridView1.DataSource = dt;       //dataGridView1.DataSource = ds.Tables[0];

            conn.Close();

        }

 

To load records upon opening your application.

private void Form1_Load(object sender, EventArgs e)

        {

            dataGridView2.Columns.Add("Firstname","fname");

            dataGridView2.Columns.Add("Lastname","lname");

            dataGridView2.Columns.Add("Gender","gender");

            retrieveALL();

        }

Clear all textbox after adding records.

private void btnClear_Click(object sender, EventArgs e)

        {

            txtFname.Clear();

            txtLname.Clear();

            txtSearch.Clear();

            cbGender.Text = null;

        }

 

If you are going to add multiply entry instead adding one at a time in textbox. This work in in datagridview. The following codes shows how to add multiple records in datagridview and save it to database.

private void btnSaveALL_Click(object sender, EventArgs e)

        {

 

           

            for (int i = 0; i < dataGridView2.Rows.Count; i++)

            {

                sql = @"INSERT INTO tblProfile (Firstname,Lastname,Gender) VALUES ('"

                    + dataGridView2.Rows[i].Cells["Firstname"].Value + "',' "

                    + dataGridView2.Rows[i].Cells["Lastname"].Value + "', '"

                    + dataGridView2.Rows[i].Cells["Gender"].Value + "')";

                dbConnection();

                cmd = new OleDbCommand(sql, conn);

                cmd.ExecuteNonQuery();

 

                MessageBox.Show("Successfully Added to your Database", "New Record Added", MessageBoxButtons.OK, MessageBoxIcon.Information);

                conn.Close();

 

                //btnReadDatabase_Click(sender, e);

                retrieveALL();

            }

        }