Database Connection String in Entity Framework Core

Database Connection String in Entity Framework Core

In this article, I will discuss Database Connection String in Entity Framework Core and How to Generate a Database using the EF Core Code First Approach. Please read our previous article discussing the DbContext Class in Entity Framework Core. We will work with the example we created in our previous article and discuss How to Create EF Core DbContext Class in our .NET Application.

Database Connection String in Entity Framework Core

Now, we will see the options available in .NET Core to Provide a Database Connection String. The DBContext in EF Core Connects to the database using the Database Providers. The database Providers require a connection string to connect to the database.

We can provide the database connection string to the EF Core application in several ways. We look at some of them in detail. The connection strings were stored in the web.config file in the older version of .NET Framework Applications. The newer .NET Core applications can read the database connection string from various sources like appsettings.json, user secrets, environment variables, and command line arguments; we can even hardcode the connection string. You can store the connection string anywhere you wish to.

Providing Connection String in OnConfiguring Method of DbContext Class:

To provide a Connection String in the OnConfiguring Method, please modify the DbContext Class as follows:

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        //Constructor calling the Base DbContext Class Constructor
        public EFCoreDbContext() 
        {
        }

        //OnConfiguring() method is used to select and configure the data source
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB1;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        //OnModelCreating() method is used to configure the model using ModelBuilder Fluent API
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //use this to configure the model
        }

        //Adding Domain Classes as DbSet Properties
        public DbSet<Student> Students { get; set; }
        public DbSet<Standard> Standards { get; set; }
    }
}

As you can see, the context class includes two DbSet<TEntity> properties for Student and Standard, which will be mapped to the Students and Standards tables in the underlying SQL Server database. In the OnConfiguring() method, an instance of DbContextOptionsBuilder is used to specify which database to use. We have installed MS SQL Server provider, which has added the extension method UseSqlServer on DbContextOptionsBuilder.

The connection string “Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV; Database=EFCoreDB1; Trusted_Connection=True; TrustServerCertificate=True;” in the UseSqlServer method provides database information:

  1. Server= specifies the DB Server to use,
  2. Database= specifies the name of the database to create
  3. Trusted_Connection=True specifies the Windows authentication mode.
  4. TrustServerCertificate=True specifies the transport layer will use SSL to encrypt the channel and bypass walking the certificate chain to validate trust.

EF Core will use this connection string to create a database when we run the migration. After Creating the Context Class and Entity Classes and specifying the Database Connection String, it’s time to add the migration to create a database.

Adding Entity Framework Core Migration:

Entity Framework Core Includes different migration commands to create or update the database based on the model. At this point, there is no EFCoreDB1 database in SQL Server. So, we need to create the database from the model (Entities and Context) by adding a migration.

We can execute the migration command using the NuGet Package Manager Console and the dotnet CLI (command line interface) command.

In Visual Studio, open the NuGet Package Manager Console. To launch Package Manager Console, select Tools => NuGet Package Manager => Package Manager Console from the menu below.

Database Connection String in Entity Framework Core and How to Generate a Database using EF Core Code First Approach

This will open the Package Manager Console. Now, type the add-migration CreateEFCoreDB1 command, select the project where your Context class is, and press the enter button, as shown in the image below.

Database Connection String in Entity Framework Core and How to Generate a Database using EF Core Code First Approach

Once the above code is executed successfully, it will create a new folder named Migrations in the project and create the ModelSnapshot files, as shown below. In our upcoming article, we will discuss Migration files in detail.

Database Connection String in Entity Framework Core and How to Generate a Database using EF Core Code First Approach

If you use .NET Core CLI, use the following command.

dotnet ef migrations add CreateEFCoreDB1

After creating a migration, don’t think that the database is created. We still need to create the database using the update-database –verbose command in the Package Manager Console as below.

Adding Entity Framework Core Migration

If you use dotnet CLI, enter the following command.

dotnet ef database update

Once the command is executed successfully, it will create the database with the name and location specified in the connection string in the UseSqlServer() method. It creates a table for each DbSet property (Students and Standards), as shown in the below image.

Note: This was the first migration to create a database. The most important point is that whenever we add or update domain classes or configurations, we need to sync the database with the model using add-migration and update-database commands.

Reading or Writing Data using DbContext Class:

Now, we can use the context class to save and retrieve data from the database using Entity Framework Core. For a better understanding, please modify the Main method of the Program class as follows. The following example code is self-explained, so please go through the comment lines for a better understanding.

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using var context = new EFCoreDbContext();
            try
            {
                //Create a New Student Object
                var student = new Student()
                {
                    FirstName = "Pranaya",
                    LastName = "Rout",
                    Height = 5.10M,
                    Weight = 50
                };

                //Add the Student into context object using DbSet Property and Add method
                context.Students.Add(student);

                //Call the SaveChanges method to make the changes Permanent into the Database
                context.SaveChanges();

                Console.WriteLine("Student Saved Successfully...");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Once you run the above code, you should get the following output.

Reading or Writing Data using DbContext Class

Now, if you go and verify the database, you should see the newly added student inside the Students database table, as shown in the image below.

Reading or Writing Data using DbContext Class

Note: Hardcoding the application code’s connection string is a bad programming practice. In real-time applications, we need to store the connection string in the appsettings.json file, and from the configuration file, we need to read the connection string. But by default, the appsettings.json file is unavailable in the Console Application. So, let us proceed and understand the steps required to use the appsettings.json file in the Console Application,

How to use AppSettings.json files in .NET Core Console Application?

For the .NET Framework Console Application, we always use the app.config file to store our configuration values for the application, such as Connection Strings, Application Level Global Variables, etc. In .NET Core, instead of App.Config file: we need to use the appsettings.json file. But, by default, this appsettings.json file is available in ASP.NET Core Framework Applications. But, by default, not available for the Console Application. Let us proceed and see how we can create and use the appsettings.json file in the .NET Core Console Application.

To use the appsettings.json file in the Console Application, we need to install the following package from NuGet either by using NuGet Package Manager UI or Package Manager Console.

Microsoft.Extensions.Configuration.Json

So, open NuGet Package Manager UI by selecting Tools => NuGet Package Manager => Manage NuGet Packages for Solution from the Visual Studio Menu, which will open the following window. Select the Search tab from this window, then search for the Microsoft.Extensions.Configuration.Json package, select the package, and click the Install button, as shown in the image below.

How to use AppSettings.json files in .NET Core Console Application?

Once you click the Install button, it will open the Preview Changes window to tell what packages (including the dependency packages) will be installed. Review the changes and click the OK button in the image below.

How to use AppSettings.json files in .NET Core Console Application?

Once you click on the OK button, it will install the package, and you can verify the package inside the Dependencies => Packages folder of your project, as shown in the image below.

How to use AppSettings.json files in .NET Core Console Application?

Adding appsettings.json File:

Once we install the package, the next step is to add a JSON file with the name appsettings.json to your project. While the name does not need to always be appsettings, this is a naming convention we generally follow in .NET Core Applications. So, right-click on your project and select Add => New Item from the context menu to open the following Add New Item window. Here, search for JSON and then select JavaScript JSON Configuration File, provide the file name as appsettings.json, and click on the add button, which will add appsettings.json to the root directory of your project.

Adding appsettings.json File

Once you add the appsettings.json file, please open it and copy and paste the following code. Here, we are adding the database connection string.

{
  "ConnectionStrings": {
    "SQLServerConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreDB1;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}
How to Fetch the Connection String from the appsettings.json file?

We must follow the steps to get the Connection String from the appsettings.json file.

Step 1: Import the following namespace.
using Microsoft.Extensions.Configuration;

Step 2: Load the Configuration File.
var configBuilder = new ConfigurationBuilder().AddJsonFile(“appsettings.json”).Build();

Step 3: Get the Section to Read from the Configuration File
var configSection = configBuilder.GetSection(“ConnectionStrings”);

Step 4: Get the Configuration Values based on the Config key.
var connectionString = configSection[“SQLServerConnection”] ?? null;

So, modify the EFCoreDbContext class to read the connection string from the appsettings.json file. The following code is self-explained, so please go through the comment lines for a better understanding.

using Microsoft.EntityFrameworkCore;
//Step1: Import the following Namespace
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;

namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        //Constructor calling the Base DbContext Class Constructor
        public EFCoreDbContext() : base()
        {
        }

        //OnConfiguring() method is used to select and configure the data source
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //Get the Connection String from appsettings.json file

            //Step2: Load the Configuration File.
            var configBuilder = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();

            // Step3: Get the Section to Read from the Configuration File
            var configSection = configBuilder.GetSection("ConnectionStrings");

            // Step4: Get the Configuration Values based on the Config key.
            var connectionString = configSection["SQLServerConnection"] ?? null;
            
            //Configuring the Connection String
            optionsBuilder.UseSqlServer(connectionString);
        }

        //OnModelCreating() method is used to configure the model using ModelBuilder Fluent API
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //use this to configure the model
        }

        //Adding Domain Classes as DbSet Properties
        public DbSet<Student> Students { get; set; }
        public DbSet<Standard> Standards { get; set; }
    }
}

Next, modify the Main method of the Program class as shown below.

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using var context = new EFCoreDbContext();
            try
            {
                //Create a New Student Object
                var student = new Student()
                {
                    FirstName = "Pranaya",
                    LastName = "Rout",
                    Height = 5.10M,
                    Weight = 50
                };

                //Add the Student into context object using DbSet Property and Add method
                context.Students.Add(student);

                //Call the SaveChanges method to make the changes Permanent into the Database
                context.SaveChanges();

                Console.WriteLine("Student Saved Successfully...");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

With the above changes in place, now run the application, and you should get the following Runtime Exception.

Database Connection String in Entity Framework Core

Why are we getting the above Exception?

The above exception clearly says that the appsettings.json file was not found inside the project bin=>debug=>.net6.0 folder. That means we need to ensure that once we build the project, the appsettings.json should be stored inside the above location. To do so, Right-click the appsettings.json file to show its Properties window. Then, set its “Copy to Output Directory” property to “Copy always.” This ensures the appsettings.json file is copied to the Debug or Release folder every time the project is built.

Why are we getting the above Exception?

So, with the above changes in place, build the project and run the application, and you should get the output as expected, as shown in the below image.

Database Connection String in Entity Framework Core

In the next article, I will discuss CRUD Operations in Entity Framework Core (EF Core). In this article, I try to explain Database Connection String in Entity Framework Core. I hope you enjoy this Database Connection String in Entity Framework Core article.

2 thoughts on “Database Connection String in Entity Framework Core”

  1. The migration doesn’t work for me: “Unable to create a ‘DbContext’ of type ”. The exception ‘Method not found: ‘Void CoreTypeMappingParameters..ctor(System.Type, Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter, Microsoft.EntityFrameworkCore.ChangeTracking.ValueComparer, Microsoft.EntityFrameworkCore.ChangeTracking.ValueComparer, Microsoft.EntityFrameworkCore.ChangeTracking.ValueComparer, System.Func`3)’.’ was thrown while attempting to create an instance. For the different patterns supported at design time”

Leave a Reply

Your email address will not be published. Required fields are marked *