Free Web Development Online Tutorials, Learn to Code
How to setup Entity framework Core Connection String in Asp.net Core, EF Core DbContext in Asp.net core

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

How to add new keys in Appsettings.json?

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

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"
  }
}

appsettings.json in asp.net core

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

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" });
        }
}  

Comment
Name
Email
Website
Subscribe
 
Entity framework Core Connection String
ASP.NET MVC Interview Questions Answers


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