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

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 can download it for free in Microsoft website.

You also need a Visual Studio. Currently the latest version of Visual Studio is 2019. You can download Visual Studio 2019 Community Edition for free in Visual Studio Microsoft website.

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.

Server Name

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.

employees table

Then add some data in your employees table.

table data

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.

    public class Employee
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string FullName
                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.

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

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

system config

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;

    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;
                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.

edit columns

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

output readOnly

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

Source Code:

Leave a Reply

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

You are commenting using your 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