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();
}
}
