How To Connect SQL Server Database Using C# and Perform CRUD Operation – Part 2

This is part two of my first post How To Connect SQL Server Database Using C# and Perform CRUD Operation – Part 1.

In this post you will learn how to insert, update and delete data in the database using C#.

Advertisements

Let’s start by adding form controls to enter data.

Add two textboxes in the form and name it txtAddFN and txtAddLN. Then add a button and name it btnSave and change the text to Save.

add form

Then add controls for details. txtId, txtFN, txtLN, btnUpdate, btnDelete. Change the value of ReadOnly property of txtId to true.

detailsForm

Now open your EmployeeRepository class. We will Add, Update and Delete methods here to support CRUD operation of our application.

Copy and paste the code below inside your  EmployeeRepository class.

public void Add(Employee employee)
        {
            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                var command = factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = $"Insert Into Employees (FirstName, LastName) Values ('{employee.FirstName}', '{employee.LastName}');";
                command.ExecuteNonQuery();
            }
        }
        public void Update(Employee employee)
        {
            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                var command = factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = $"Update Employees Set FirstName = '{employee.FirstName}', LastName = '{employee.LastName}' Where Id = {employee.Id};";
                command.ExecuteNonQuery();
            }
        }

        public void Delete(int id)
        {
            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                var command = factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = $"Delete From Employees Where Id = {id};";
                command.ExecuteNonQuery();
            }
        }

Now select your datagridView then click events. Double click in the event name CellClick. Write this code inside your CellClick event.

Here we check if there is a selected row in our datagridView. If there is a selected row we will get the first row the convert it to object type Employee. Then display the employee details in the detail textboxes.

private void DataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if(dataGridView1.SelectedRows.Count > 0)
            {
                var row = dataGridView1.SelectedRows[0];
                var employee = (Employee)row.DataBoundItem;
                txtId.Text = employee.Id.ToString();
                txtFN.Text = employee.FirstName;
                txtLN.Text = employee.LastName;
            }
        }

Then in the form, double click the btnSave and change the code to look like this code.

private void BtnSave_Click(object sender, EventArgs e)
        {
            if(!string.IsNullOrEmpty(txtAddFN.Text) && !string.IsNullOrEmpty(txtAddLN.Text))
            {
                employeeRepository.Add(new Employee
                {
                    FirstName = txtAddFN.Text,
                    LastName = txtAddLN.Text,
                });
                txtAddFN.Text = string.Empty;
                txtAddLN.Text = string.Empty;
                dataGridView1.DataSource = employeeRepository.GetAll();
            }
        }

What we did here was check if txtAddFN and txtAddLN has a value then call the Add method of EmployeeRepository.

Now double click your btnUpdate and put this code.

private void BtnUpdate_Click(object sender, EventArgs e)
        {
            if(!string.IsNullOrEmpty(txtId.Text) && !string.IsNullOrEmpty(txtFN.Text) && !string.IsNullOrEmpty(txtLN.Text))
            {
                employeeRepository.Update(new Employee
                {
                    Id = int.Parse(txtId.Text),
                    FirstName = txtFN.Text,
                    LastName = txtLN.Text
                });
                dataGridView1.DataSource = employeeRepository.GetAll();
            }
        }

Then double click your btnDelete and copy this code.

private void BtnDelete_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtId.Text) && !string.IsNullOrEmpty(txtFN.Text) && !string.IsNullOrEmpty(txtLN.Text))
            {
                employeeRepository.Delete(int.Parse(txtId.Text));
                txtId.Text = string.Empty;
                txtFN.Text = string.Empty;
                txtLN.Text = string.Empty;
                dataGridView1.DataSource = employeeRepository.GetAll();
            }
        }

Now run your application and if everything went well. You should be able to Create, Update and Delete data using C#.

How To Connect SQL Server Database Using C# and Perform CRUD Operation – Part 1

Source Code: https://github.com/alexcalingasan/SQLCSHARPCRUD

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s