Introduction:

In this article, we will explore how to perform Insert, Update, and Delete operations on records within a DataGridView using a C# Windows Forms application. We will utilize a SQL Server database to store and retrieve data by establishing a SQL connection. This comprehensive guide offers step-by-step instructions on creating a solution, database table, and Windows Forms and performing operations to insert, update, and delete records from the SQL Server database.

Let's begin by creating a Windows Forms application using Visual Studio. For this tutorial, Visual Studio 2019 will be used.

Step 1:

Launch Visual Studio 2019 and select "Windows Forms App (.NET Framework)" and click "Next."

In the subsequent screen, enter a project name, choose a location, and select the Framework version.

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:

Conclusion:

This article provided a complete guide on how to perform Insert, Update, and Delete operations on records in a DataGridView using a C# Windows Forms application. The tutorial covered the step-by-step process of creating the application, setting up the SQL Server database, designing the user interface with relevant controls, and implementing the necessary code for database connectivity and record manipulation.

The article highlighted the importance of establishing a connection to the SQL Server database using a connection string and demonstrated how to populate data from the database into the DataGridView using the PopulateData() method. It also covered the implementation of Insert, Update, and Delete functionalities, ensuring that the mandatory fields are filled before performing the operations.

Furthermore, the article showcased the usage of event handlers, such as dgvCars_RowHeaderMouseClick(), to handle user interactions with the DataGridView and update the corresponding text boxes accordingly.

For more details on performing CRUD operations in a C# Windows application, you can also refer to the following article:

Create a Simple Windows Application In C# (CRUD Operation)