How To Connect SQL Server Database Using C# and Perform CRUD Operation | Cheap ASP.NET Hosting Review

Read Time:6 Minute, 39 Second

In this tutorial I will teach you how to connect to SQL Server using C# and perform a basic CRUD operation in object oriented manner.

You will also learn how to use DataGridView to display data from the database and how to get the data from the selected row of the data grid.

For this project, you need to have a SQL Management Studio to create a database.

You also need a Visual Studio. Currently the latest version of Visual Studio is 2019.

After installing SQL Management Studio and Visual Studio. We can now start with our project.

Open your SQL Management Studio to connect to you Database server.

If you install Visual Studio 2019 in your machine then you can enter (LocalDb)\MSSQLLocalDB for your server name.

Then Create new database and name it EmployeeDb.

In EmployeeDb create a new table called Employees.

Add Id, FirstName, LastName columns in your table.

Then add some data in your employees table.

Now we are done with our database.

Open your Visual Studio and create a new Windows Forms App and name it SQLCSHARPCRUD.

To make cleaner and reusable code instead of directly accessing the database in our form we will create a class that will be responsible for connecting to database and performing our CRUD operation.

Add new class in our project and name it Employee.cs. This class is a representation of our Employees table in our code. Add properties Id, FirstName and LastName in our Employee class.

namespace SQLCSHARPCRUD
{
    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string FullName
        {
            get
            {
                return $"{FirstName} {LastName}";
            }
        }
    }
}

I added a new property called FullName. It is a read only property and will have a value of a combination of FirstName and LastName.

Then open app.config then add app settings for provider and connectionString inside the configuration tag.

<appSettings>
    <add key="provider" value="System.Data.SqlClient"/>
    <add key="connectionString" value="Data Source=(LocalDb)\MSSQLLOCALDB;Initial Catalog=EmployeeDb;Integrated Security=True"/>
  </appSettings>

To access the data in app.config we need to add a reference for System.Configuration.

Now add a new class and name it EmployeeRepository.cs. Copy and paste the code below to your EmployeeRepository class.

using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
namespace SQLCSHARPCRUD
{
    public class EmployeeRepository
    {
        DbProviderFactory factory;
        string provider;
        string connectionString;
        public EmployeeRepository()
        {
            provider = ConfigurationManager.AppSettings["provider"];
            connectionString = ConfigurationManager.AppSettings["connectionString"];
            factory = DbProviderFactories.GetFactory(provider);
        }
        public List<Employee> GetAll()
        {
            var employees = new List<Employee>();
            using(var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                var command = factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "Select * From Employees;";
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        employees.Add(new Employee
                        {
                            Id = (int)reader["Id"],
                            FirstName = (string)reader["FirstName"],
                            LastName = (string)reader["LastName"]
                        });
                    }
                }
            }
            return employees;
        }
    }
}

In this code we used DbProviderFactory to create connection and execute command in our database.

In our GetAll Method. We executed the Select Query and store the data in employee list then return it, so other part of the program that will call GetAll method will get all the data from our Employees table.

Now go to Form1 and add a datagrid view.

Click the arrow in upper right corner of datagridView then click Edit Columns. Then add columns Id, FirstName, LastName and Name.

To bind the columns in a property of our Employee class. Change the value of DataPropertyName to match the name of the property in our Employee class.

Set DataPropertyName of Id to Id. Then for the FirstName set to FirstName. LastName to LastName and for Name set the DataPropetyName to FullName.

Then I don’t want the FirstName and LastName to be visible in our gridView, so set FirstName and LastName visible property to False.

Run your program and if everything works well you should the same output as mine.

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

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.

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

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