How to access SQL Database Connection string in .Net Core?

A database connection string is a very basic task for a developer who is working on ASP.Net. But defining a database connection string in .Net Core and reading back into C# code is a little different than the traditional ASP.Net framework.

In this article, we will define a database connection string and then get its value in our C# code. To demonstrate this we will use .NET Core 5 and above.
The ASP.Net Core is a new open-source framework introduced by Microsoft.
ASP.Net Core is a cross-platform open-source framework for building modern applications including cloud-based development.

Web config in traditional ASP.Net

In the ASP.Net framework, we had web.config file to manage all the configurable things including the database connection string.

ASP.NET Core DB Connection String Example

In .Net core, we don’t have web.config. ASP.Net Core uses JSON-based files to manage database connection strings. Because applications developed in ASP.Net Core are platform-dependent and JSON is readable to all platforms.

To know how to create a web application in ASP.Net Core, follow this blog – How to create a web application in ASP.Net Core?

In the .Net Core web application, we have a appsettings.json file to write a database connection string. By default, the appsettings.json file contains the below code –

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts”: "*"
}

Add the below line in appsettings.json file

"ConnectionStrings": {
    "DefaultConnection": "Data Source=127.0.0.1;Initial Catalog=MySQLDB;Persist Security Info=True;User ID=sa;Password=12345"
  }

After adding the Connection string, the complete appsettings.json file looks like the below –

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=127.0.0.1;Initial Catalog=MySQLDB;Persist Security Info=True;User ID=sa;Password=12345"
  },
  "AllowedHosts": "*"
}

Once we have done with a connection string, now we will try to read this connection string in C# code.

How to read Database Connection string in ASP.Net Core application?

In ASP.NET Core, database connection and other configurations are stored in a JSON file which is appsettings.json, there could be multiple ways to read the database connection string in the .Net Core application. Let’s look at them in detail.

First Way for reading Database Connection string in .Net Core

Create a class file and call it MyDBContext.cs. Inherit this class with the DbContext class which is a part of Microsoft.EntityFrameworkCore.

Declare a field of type IConfiguration

private IConfiguration Configuration;

Create a constructor of MyDbContext.cs

public MyDbContext(DbContextOptions<MyDbContext> options, IConfiguration localconfiguration)
            : base(options)
        {
            Configuration = localconfiguration;
        }

Next, override the function OnConfiguring() in the MyDbContext class and add the below code.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
              
                optionsBuilder.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),
sqlServerOptions => sqlServerOptions.CommandTimeout(
Convert.ToInt32(Configuration.GetConnectionString("ConnectionTimeOut"))
));

            }
        }

Complete MyDbContext.cs file will look like this –

public partial class MyDbContext : DbContext
{
    private IConfiguration Configuration;

    public MyDbContext(DbContextOptions<MyDbContext> options, IConfiguration localconfiguration)
        : base(options)
    {
        Configuration = localconfiguration;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {

            optionsBuilder.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),
        sqlServerOptions => sqlServerOptions.CommandTimeout(
        Convert.ToInt32(Configuration.GetConnectionString("ConnectionTimeOut"))
        ));

        }
    }

}

Another Way for reading Database Connection string in .Net Core

Write the below code in the C# class to read the apsettings.json file –

var dbconfig = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json").Build();

And, then read the ConnectionString from the variable dbconfig.

if (!string.IsNullOrEmpty(dbconfig.ToString()))
{
    string dbconnectionStr = dbconfig["ConnectionStrings:DefaultConnection"];
}

I hope you like this blog. Please share this blog on social media and write your comments below.

Leave a Comment

RSS
YouTube
YouTube
Instagram