Insert, Update, and Delete (CRUD) Records in C# DataGridView

In this article, we will use an SQL Server database to store and retrieve data by establishing an SQL connection. We will also create a solution, set up a database table, and build Windows Forms, along with performing operations to insert, update, and delete records from the SQL Server database.

Step 1: 

Open  Visual Studio 2022 select "Windows Forms App (.NET Framework)" and click "Next." enter your project name, choose a location, select the Framework version and click "Create"

Step 2:

Create a database named "CarModelsDb" and add a table called "tblCarDetails" with the following columns:

Step 3:

Create a new form and add controls as shown below. We will include labels, text boxes, and buttons. Rename the text boxes as "txtCarName," "txtModel," and "txtYear." Rename the buttons as "btnInsert," "btnUpdate," and "btnDelete."

Step 4:

Add a connection string to establish a connection with the SQL Server database. Use the following code:

SqlConnection con = new SqlConnection("Data Source =localhost; Initial Catalog = CarModelsDb; User Id =sa; Password=sa;");

Now, let's add the "PopulateData" method to retrieve data from the database and populate it within the DataGridView.

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 following code snippet reads the values from the text boxes and inserts them into the "tblCarDetails" database table.

// 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 following code snippet reads the values from the text boxes and updates them in the "tblCarDetails" database table.

// 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 following code snippet 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 a row is selected in the DataGridView. The following code snippet assigns the selected values to the text boxes, allowing them to be viewed in the corresponding 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: