#12: Vitamin C# for the Newbies Part 2 {CRUD}

Vitamin C# part 2 is a continuation of my previous blog in which we discuss about how to retrieve data from our database. This time we will talk about how add, edit or delete data from the database.

 

CRUD

Create, Read, Update, Delete or simply CRUD are the four basic functions for database applications. Once you know how to create such functions, you have a way to go in Software development. Let's do this in a simple way, our C sharp way.

Let's add first the action buttons in our form1.

Then add new form. In the solution explorer, right click on the project name and select Add>Windows Form as shown;

 

In the new form, call it Form2 and follow the layout below. Let's name the LastName textbox as txbLName, FirstName as txbFName, Birthdate as dtpBirth, Address as txbAddress and ContactNo as txbContact.

  

We will prepare Form2 to accept ID and AddRecord variable. In code behind, add the following line;
      public string ID { get; set; }
        public bool AddRecord { get; set; }

Let's go back to Form1 and add events on the add, edit, delete buttons.

CREATE
In Form1, double click on the Add button and write the following code

         Form2 frm = new Form2();
            frm.AddRecord = true;
            frm.ShowDialog();

Then in Form2, we will create the InsertRecord function below;

 public void InsertRecord(string LName, string FName, DateTime BirthDate, string Address, string ContactNo)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strSql;

            strSql = "INSERT INTO tblCustomer (" +
                        "LastName, " +
                        "FirstName, " +
                        "Birthdate, " +
                        "Address, " +
                        "ContactNo " +
                        ") ";
            strSql = strSql + "values ('" +
                    LName + "', '" +
                    FName + "', '" +
                    BirthDate + "', '" +
                    Address + "', '" +
                    ContactNo + "' )";

            DataSet DtSet = new DataSet();

            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();

            cmd.Connection = mycon;
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();
            mycon.Close();         
        }

READ
In Form1, double click on the edit button and add the following code;

            Form2 frm = new Form2();
            frm.AddRecord = false;
            frm.ID = dgvCustomer.CurrentRow.Cells[0].Value.ToString();
            frm.ShowDialog();

In Form2, write the LoadCustomerData function;

DataSet DtSet = new DataSet();
string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();
            string strSql;

            strSql = "SELECT * FROM tblCustomer WHERE id = " + ID + " ";

            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(strSql, mycon);
            myDataAdapter.Fill(DtSet);

            if (DtSet.Tables[0].Rows.Count > 0)
            {
                txbLName.Text = DtSet.Tables[0].Rows[0][1].ToString();
                txbFName.Text = DtSet.Tables[0].Rows[0][2].ToString();
                dtpBirth.Value = Convert.ToDateTime(DtSet.Tables[0].Rows[0][3]);
                txbAddress.Text = DtSet.Tables[0].Rows[0][4].ToString();
                txbContact.Text = DtSet.Tables[0].Rows[0][5].ToString();
            }

            mycon.Close();

Then call this in form load;

 private void Form2_Load(object sender, EventArgs e)
        {
            if (AddRecord == false)
            {
                LoadCustomerData();
            }
        }

UPDATE
In Form2, let's create the UpdateRecord Function;

public void UpdateRecord(string ID, string LName, string FName, DateTime BirthDate, string Address, string ContactNo)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strSql;

            strSql = "UPDATE tblCustomer SET " +
                "LastName = '" + LName + "', " +
                "FirstName = '" + FName + "', " +
                "Birthdate = '" + BirthDate + "', " +
                "Address = '" + Address + "', " +
                "ContactNo = '" + ContactNo + "' " +
                " WHERE id = " + ID + " ";

            DataSet DtSet = new DataSet();

            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();

            cmd.Connection = mycon;
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();

            mycon.Close();
        }

Now double click on the OK button and add the following line;

 if (AddRecord == true)
            {
                InsertRecord(txbLName.Text, txbFName.Text, dtpBirth.Value, txbAddress.Text, txbContact.Text);

            }
            else { UpdateRecord(ID, txbLName.Text, txbFName.Text, dtpBirth.Value, txbAddress.Text, txbContact.Text); }
            this.Close();

Our IF condition tell us that if the ADD button in Form1 is clicked, then AddRecord is true in Form2 thus it executes the InsertRecord function which will add new record in the database once the user clicks the OK button. IF it is false, then it executes the UpdateRecord function

DELETE
In Form1, add the DeleteRecord Function;

public void DeleteRecord(string ID)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strSql;

            strSql = "DELETE  FROM tblCustomer " +
                    " WHERE id = " + ID + " ";

            DataSet DtSet = new DataSet();
            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();

            cmd.Connection = mycon;
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();

            mycon.Close();
        }

Then call it in the Delete button event

DeleteRecord(dgvCustomer.CurrentRow.Cells[0].Value.ToString());

To refresh your data in Form1, you just have to call the LoadAllCustomerData function;

private void LoadAllCustomerData()
        {
            DataSet DtSet = new DataSet();
            string connection_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=appdata/Database1.mdb;Persist Security Info=False;";
            OleDbConnection mycon = new OleDbConnection(connection_str);
            mycon.Open();
            string strSql;

            strSql = "SELECT * FROM tblCustomer ";

           OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(strSql, mycon);
           myDataAdapter.Fill(DtSet);

           dgvCustomer.DataSource = DtSet.Tables[0];
           mycon.Close();
        }

Now we have successfully created the basic functions in database applications. Good luck to your future project folks. Happy coding!