Back to: ASP.NET Core Tutorials For Beginners and Professionals
Database Connection String in Entity Framework Core
In this article, I am going to discuss Database Connection String in Entity Framework Core and How to Generate a Database using EF Core Code First Approach. Please read our previous article, where we discussed DbContext Class in Entity Framework Core. We will work with the same 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 what are the different 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 Connection String in 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 Standardes 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:
- Server= specifies the DB Server to use,
- Database= specifies the name of the database to create
- Trusted_Connection=True specifies the Windows authentication mode.
- 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, 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 NuGet Package Manager Console as well as using the dotnet CLI (command line interface) command.
In Visual Studio, open NuGet Package Manager Console. To launch Package Manager Console, select Tools => NuGet Package Manager => Package Manager Console from the menu below.
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.
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.
If you are using .NET Core CLI, then you need to 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.
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 you need to remember 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.
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.
Note: Hardcoding the application code’s connection string is a very 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 not available in Console Application. So, let us proceed and understand the steps required to use the appsettings.json file in Console Application,
How to use AppSettings.json files in .NET Core Console Application?
For .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 Console Application. Let us proceed and see how we can create and use the appsettings.json file in .NET Core Console Application.
To use the appsettings.json file in 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 on the Install button, as shown in the image below.
Once you click on the Install button, it will open the Preview Changes window to tell what packages (including the dependency packages) are going to be installed. Simply review the changes and click the OK button in the image below.
Once you click on the OK button, it will open and install the package, and you can verify the package inside the Dependencies => Packages folder of your project as shown in the below image.
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 then select Add => New Item from the context menu, which will 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.
Once you add the appsettings.json file, then 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 need to 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 then 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 as follows 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 then 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.
Why are we getting the above Exception?
If you read the above exception, then it is clearly saying that the appsettings.json file was not found inside the project bin=>debug=>.net6.0 folder. That means we need to make sure that once we build the project, the appsettings.json should and must 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.
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.
In the next article, I am going to 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.
