Connect C# to SQL Server

Oscar Montenegro

Mar 1, 2023 • 5 min read

Hello again, I’m keeping up with the writing streak hoping to continue consistently writing some interesting articles around the C# programming language. Today in this article I will show you how you can connect any asp.net core web project to a SQL Server database. Almost any web application meets the need to store and manipulate data as everything in software development revolves around the idea of having data and finding creative uses for this one, therefore it’s imperative that you know how to connect to a database to ensure communication with it.

Prerequisites

In order to follow this tutorial you need to have installed any version of SQL server and SQL Server Management Studio which we can say is the GUI to view all the database data and tables.

Also, you need to install the Entity Framework Core tools with our dotnet cli, to install it write the following command on your terminal.

dotnet tool install --global dotnet-ef

After this, write this other command on your terminal dotnet ef and you should see an output like the one below.

                                         _/\\__
               ---==/    \\\\
         ___  ___   |.    \\|\\
        | __|| __|  |  )   \\\\\\
        | _| | _|   \\_/ |  //|\\\\
        |___||_|       /   \\\\\\/\\\\

Entity Framework Core .NET Command-line Tools 2.1.3-rtm-32065

<Usage documentation follows, not shown.>

After this, you are ready and set to go!

Create a new web project

For this example, I will use an asp net core web api project but you can use this code for MVC, Razor or minimal APIs projects as the structure of them is pretty much the same. So create a new web application using the dotnet cli.

dotnet new webapi -o "Name of your project"

Code First approach

I know I’m writing this article for very beginners but in this example, I make use of this approach to ensure that we can see data being inserted into the database so I will try to explain briefly what is the code-first approach.

The code-first approach is one of the three approaches that Entity Framework provides to work with data. What this approach allows us is to work with C# objects instead of working directly with the database or any other library like ADO.NET this is pretty useful for us developers because we can keep working with code when trying to communicate to SQL Server.

Add a Person model

We will create a Person model which in place will be our table in SQL server and we will insert our object’s data into that sql table, therefore the code will end up looking like this.

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
}

This class is pretty simple we only add the classic fields for a Person object being this the Id, name, last name and age of the person. This would be all for the person class.

Install Entity Framework nuget packages

As I mentioned earlier we will use Entity Framework to communicate with the SQL database and to map our person object to the person table in SQL. We will install three EF packages, you can look for them in the nuget package manager on visual studio, use the package manager console or like in our case install the using the dotnet cli.

dotnet add package Microsoft.EntityFrameworkCore.Core

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet add package Microsoft.EntityFrameworkCore.Tools

Now that you have installed the EF core package into your project we will proceed to create the AppDbContext.cs class.

Create the `AppDbContext.cs` class

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Person>().HasData(
            new Person {Id = 1, Name = "Oscar", LastName = "Montenegro", Age = 28},
            new Person {Id = 2, Name = "Yolanda", LastName = "Montenegro", Age = 27}
        );

        base.OnModelCreating(builder);
    }

    public DbSet<Person> People { get; set; }
}

First, we must create the AppDbContext class and make it inherit from the DbContext class and create an empty constructor calling the base constructor from the parent class. Then we are overriding the OnModelCreating function and using the model builder to tell entity framework that our collection should have data at the moment of being created on SQL and we are passing two objects inside the HasData function. Last but not least we create a DbSet property called people which is the plural for Persons as is a good practice when you create a table for a specific entity to use the plural for that entity.

Add the connection string

Go to your appsettings.json file (if you have one if not create it) and add the connection string to your sql server instance as you can see below but without the square brackets:

"ConnectionStrings": {
    "DbConnection": "Server=ServerName;Database=DatabaseName;Trusted_Connection=True;Integrated Security=true;TrustServerCertificate=true"
  }

Server: You can find your server name when you open the SQL Server Management Studio on the connection page your server name is displayed there.

Database: This is the name of your database but as this is a code-first approach there ir no existing database so you can give this database any name you want and it will be created with this name.

Register your AppDbContext in the Program file

Now you can go and register your AppDbContext into the program file and pass the connection string to be able to open the communication with SQL Server

builder.Services.AddDbContext<AppDbContext>(options => {
    options.UseSqlServer(builder.Configuration.GetConnectionString("DbConnection"));
});

Add this line of code below the other services registration and above the app building line.

Execute EF commands

Lastly, we need to execute some ef commands to create the database from our code.

dotnet ef migrations add "Initial Migration"

dotnet ef database update

After executing these two commands you should be able to go into SSMS (SQL Server Management Studio) and see the database created and inside the database, there should be a table called People and in turn inside this table you should have two records which are the records we entered into our context class.

Video version of this Article

Conclusion

From here the rest is reading that data, manipulating it and doing all sort of things that you can imagine with C#. This was only meant to be a starting point but if you found this useful please let me know to create another article on how to create, read, update and delete data (CRUD) so you have a fully functional application communicating with SQL Server, also if you would like to know how to connect to PostgreSQL or MySQL leave a comment down below to create a tutorial for those RDBMS’s too.

Like always if you enjoyed this article please follow me on my social media specially on youtube and wait for any new article or project on youtube. Thanks a lot for your time and attention, see you later and happy coding! 🙋🏾‍♂️