Database Programming in C#
Step 1:
Open Visual Studio 2019 and select Windows Forms App (.Net Framework) and click "Next".
In the below screen, enter a project name and select location & Framework version,
Step 2:
Create a database named CarModelsDb and Add a table named tblCarDetails with the below columns.
Step 3:
Create a new form and add controls like below, we will add the labels, text boxes, and buttons. Rename the text boxes
like txtCarName, txtModel, and txtYear. Rename the buttons like btnInsert, btnUpdate, and btnDelete.
Step 4:
Add a connection string to connect the SQL Server database as shown below,
SqlConnection con = new SqlConnection("Data Source =localhost; Initial Catalog = CarModelsDb; User Id =sa;
Password=sa;");
Add the PopulateData method to get the data from the database and populate it in the data grid view.
private void PopulateData()
{
con.Open();
DataTable dt = new DataTable();
adapt = new SqlDataAdapter("select * from tblCarDetails", con);
adapt.Fill(dt);
dgvCars.DataSource = dt;
con.Close();
}
The below code is used to read the values from the text box and insert them into the database table tblCarDetails.
// Insert the values to the database
private void btnInsert_Click(object sender, EventArgs e)
{
if (txtCarName.Text != "" && txtModel.Text != "" && txtYear.Text != "")
{
cmd = new SqlCommand("insert into tblCarDetails(Name,Model,Year) values(@name,@model,@year)", con);
con.Open();
cmd.Parameters.AddWithValue("@name", txtCarName.Text);
cmd.Parameters.AddWithValue("@model", txtModel.Text);
cmd.Parameters.AddWithValue("@year", txtYear.Text);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Car Details Inserted Successfully");
PopulateData();
ClearControls();
}
else
{
MessageBox.Show("Please enter mandatory details!");
}
}
The below code is used to read the values from the text box and update them into the database table tblCarDetails.
// Update values to database
private void btnUpdate_Click(object sender, EventArgs e)
{
if (txtCarName.Text != "" && txtModel.Text != "" && txtYear.Text != "")
{
cmd = new SqlCommand("update tblCarDetails set Name=@name,Model=@model,Year=@year where Id=@Id",
con);
con.Open();
cmd.Parameters.AddWithValue("@Id", carId);
cmd.Parameters.AddWithValue("@name", txtCarName.Text);
cmd.Parameters.AddWithValue("@model", txtModel.Text);
cmd.Parameters.AddWithValue("@year", txtYear.Text);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Car Details Updated Successfully");
PopulateData();
ClearControls();
}
else
{
MessageBox.Show("Please enter mandatory details!");
}
}
The below code is used to delete the selected row or record from the database.
// Delete data from database
private void btnDelete_Click(object sender, EventArgs e)
{
if (carId != 0)
{
cmd = new SqlCommand("delete tblCarDetails where Id=@id", con);
con.Open();
cmd.Parameters.AddWithValue("@id", carId);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Car Deleted Successfully!");
PopulateData();
ClearControls();
}
else
{
MessageBox.Show("Please select record to delete");
}
}
The dgvCars_RowHeaderMouseClick event will be triggered when we select the row in the data grid view. Below code is
used to assigned the selected values into the text box to see them in the controls.
// Get selected records from grid view
private void dgvCars_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
carId = Convert.ToInt32(dgvCars.Rows[e.RowIndex].Cells[0].Value.ToString());
txtCarName.Text = dgvCars.Rows[e.RowIndex].Cells[1].Value.ToString();
txtModel.Text = dgvCars.Rows[e.RowIndex].Cells[2].Value.ToString();
txtYear.Text = dgvCars.Rows[e.RowIndex].Cells[3].Value.ToString();
}
Screenshots:
Insert Record into the database:
Update Record into the database:
Delete record from the database:
