Database Connection Strings in Entity Framework Core

In this article, you will learn how to setup connection string for entity framework core in Asp.net core application

Configuring Connection String in Appsettings.json

Now you may not find any web.config file in Asp.net Core application, but to keep all standard configuration information like database connection string, we will use the file Appsettings.json, We will setup all database related information like server name, database name, user-id password etc under a new section in appsettings.json

Appsettings.json always contain values with key value pair separated with a colon like “Key”: “Value”

How to add new keys in Appsettings.json?

Now for each key you can have a single value or multiple values with a set of keys. you can write this way “section-key” :{ “key1”:”value1”, “key2”:”value2”}

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      //"Default": "Warning"
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "DbConnectionConfig": {
    "DatabaseName": "MarketingDB",
    "UserName": "masa",
    "Password": "mapass",
    "ServerName": "USER-PC"
  }
}

How to read database info from Appsettings.json file?

There are multiple ways to read value from Appsettings.json file First we learn simple way, like how we used to read value from web.config appsettings section.
In Asp.net Core we need to refer a namespace Microsoft.Extensions.Configuration; and then use IConfiguration to read the value from appsettings.json file.

In controller constructor we need to set the local IConfiguration property this way.

private readonly IConfiguration config;
public studentController(IConfiguration configuration)
{
    config = configuration;
}

See this code Illustration
read database configuration from appsettings.json in asp.net core

Here is the code demonstrate how to read database configuration information in asp.net core controller, notice, how dependency injection pattern used in constructor

using Microsoft.Extensions.Configuration;            
public class studentController : Controller            
{
    private readonly IConfiguration config;
    public studentController(IConfiguration configuration)
    {
    config = configuration;
    }
    public IActionResult index()
    {
    
    // Method 1 to read value            
    string _dbCon1 = config.GetSection("DbConnectionConfig").GetSection("DatabaseName").Value;
    
    // Method 2 to read value
    string _dbCon2 = config.GetValue<string>("DbConnectionConfig:DatabaseName");
    
    return View();
    }
}

Configuring As A Service (Method 2)

There is another we can read values using custom class from appsettings.json, like how we used to read using custom configuration in earlier .net version

See this code Illustration

reading value from appsettings.json in asp.net core

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
            
public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();
    services.Configure<DbConnection>(Configuration.GetSection("DbConnectionConfig"));
}

Make sure you have registered the new section in ConfigureService method of Stratup.Cs

services.Configure<DbConnection>(Configuration.GetSection("DbConnectionConfig"));

ConfigureService Method of Startup.Cs

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
            
public class HomeController : Controller
{
    private readonly DbConnection  dbconfig;
    public HomeController(IOptions<DbConnection> dbcon)
    {
        dbconfig = dbcon.Value;
    }

    public Task GetNewOrders()
    {
           
        using (var connection = new SqlConnection(dbconfig.ConnectionString))
        {
            connection.Open();
            // do whatever
            connection.Close();
        }
        
        throw new NotImplementedException();
    }


}

Now you may wonder how dbconfig.ConnectionString property will look like, so here I will share the DbConnection class code for you better understanding.

public class DbConnection
{
public string DatabaseName { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string ServerName { get; set; }

    public string ConnectionString
    {
        get
        {
            return string.Format("Server={0};Database={1};User ID={2};Password={3};" +
            "Trusted_Connection=False;MultipleActiveResultSets=true;", ServerName, DatabaseName, UserName, Password);
        }
    }
}

Entity Framework Core Connection String for DbContext

So far, we have seen how to read database configuration information from appseeting.json file and use that with SQL server, now we see how to use same connection string information for Entity Framework.

You need to add following codes in your ConfigureServices method of startup.cs file.

Note: you may not see the UseSqlServer method if you are using asp.net core 3.1, then you need to install Microsoft.EntityFrameworkCore.SqlServer from your Nuget package manager

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using Microsoft.Extensions.DependencyInjection;

public void ConfigureServices(IServiceCollection services)
{         
    services.Configure<DbConnection>(Configuration.GetSection("DbConnectionConfig"));
            
    services.AddDbContext<EFContext>(option=>
    option.UseSqlServer(Configuration.GetConnectionString("DbConnectionConfig")));
}

Here is a quick demonstration of how database connection information is being retrieved from appsetting.json file then database object created and passed from controller to DbContext object

Controller => DTO Class => DbContext

Entity Framework Core Connection String

Here is how your DbContext class for Entity framework core will look like

using Microsoft.EntityFrameworkCore;

public class EFContext : DbContext
{
    private readonly string conString;
       
    public EFContext(string connectionString):base()
    {
        conString = connectionString;
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)            {
                
            optionsBuilder.UseSqlServer(conString);
        }
        base.OnConfiguring(optionsBuilder);
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>().HasKey(s=>s.StuId);
        base.OnModelCreating(modelBuilder);
    }
              
    public DbSet<Student> tbStudent { get; set; }
       
}

DTO Class : This class is responsible for passing data from controller to DBContext class

public class StudentDTO : IDisposable
{
    private readonly DbConnection dbcon;
    public StudentDTO(DbConnection dbconnection)
    {
        dbcon = dbconnection;
    }
    public Student AddStudent(Student s)
    {
        using (var context = new EFContext(dbcon.ConnectionString))
        {
            context.tbStudent.Add(s);
            context.SaveChanges();
        }
        return s;
    }
}

Now look at the controller class, how DbConnection object is created through constructor dependency injection

    public class studentController : Controller
    {
        private readonly IConfiguration config;
        private readonly DbConnection dbcon;
        public studentController(IConfiguration configuration, 
            IOptions<DbConnection> dbconnection)
        {
            config = configuration;
            dbcon = dbconnection.Value;
        }
		
		[HttpPost]
        public IActionResult index(StudentModel model)
        {
            Student _student = new Student();
            _student.Firstname = model.FirstName;
            _student.Lastname = model.LastName;
            _student.ContactNumber = model.Mobile;
            _student.Email = model.Email;
            _student.StuId = model.StudentId;
            
            using (StudentDTO dto = new StudentDTO(dbcon))
            {
                if (_student.StuId == 0)
                {
                    dto.AddStudent(_student);
                }
                else
                {
                    dto.UpdateStudent(_student);
                }
                
            }
            return RedirectToAction("index", new { msg = "New Student Added" });
        }
}

Notice, how we created the new instance of IConfiguration object through constructor dependency injection.

You may be interested to read following posts

Asp.net Core Tutorial | Asp.net Core Interview Questions

Asp.net Core Course

Entity framework Core Connection String

Asp.net Core Tutorial for Beginners

Learn .Net C# Programming

Learn .Net LINQ using C#

SQL Database Tutorials

MySQL Tutorials

Entity Framework Tutorials

Ado.Net Tutorials

LINQ Tutorials

Hire .Net Developer

Asp.Net Core C# Examples | Join Asp.Net MVC Course