Connect C# to SQL Server

This is a repost from my article published on my blog: Unit Coding go and check it out! ✨

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. Almos 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 are pretty much the same. So create a new web application using the dotnet cli.

dotnet new webapi -o [Name of your project here]

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 insert 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 insted 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 the 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 to 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 on 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 that 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 found 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 data base 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.

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 this 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 soul have two records which are the records we entered into our context class.

Conclusion 🌇

From here the rest is reading that data, manipulating it and doing al 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 special on youtube and wait for any new article or project on youtube. Thanks a lot for your time and attention, see you later! 🙋🏾‍♂️