Category: C#

In ASP.NET Core the configuration system is very flexible, and the connection string could be stored in appsettings.json. In this blog, this blog shows asp.net core how to use the connection string stored in appsettings.json.

Config appsettings.json file

To define the connection strings in appsettings.json it is important to specify it in the right section of the JSON structure. Below snippet shows how to store connection string in appsettings.json file

{
“Logging”: {
“LogLevel”: {
“Default”: “Warning”
}
},
“AllowedHosts”: “*”,
“ConnectionStrings”: {
“Dev”: “Server=myServer;Database=dev;Trusted_Connection=True;”,
“Live”: “Server=myServer;Database=live;Trusted_Connection=True;”
}
}

Read connection string from appsettings.json file

For Entity Framework Core, we can use the Configuration API to pass the connection string to the DbContext in the Startup class:

public void ConfigureServices(IServiceCollection services) {
if (CurrentEnvironment.IsDevelopment())
{
services.AddDbContext(options => { options.UseSqlServer(Configuration.GetConnectionString(“Dev”)); });
}
else {
services.AddDbContext(options => { options.UseSqlServer(Configuration.GetConnectionString(“Live”)); });
}
}

Not Entity Framework

If you are not an Entity Framework Core fan, then you can create a interface with property to store the connection string.

public interface IDBInfo
{
string DbConnectionString { get; }
}

In the Startup class we can use AddScoped method to read data from json file to interface:

public void ConfigureServices(IServiceCollection services) {
if (CurrentEnvironment.IsDevelopment())
{
services.AddScoped(x => new DBInfo(Configuration.GetConnectionString(“Dev”)));
}
else {
services.AddScoped(x => new DBInfo(Configuration.GetConnectionString(“live”)));
}
}

When you searching .NET Core database via Google, all results in the first five page are EF Core. But I am a Dapper fans, I thought I should introduce about .NET Core and Dapper.

Add Connection String to appsettings.json

.Net Core can use appsettings.json to store database connection string. Open the file and add a ConnectionString section to the file. You can maintain multiple connection strings in this section if you have more than one connection string.

“ConnectionStrings”: {
“Dev”: “Server=WIN-TAUP5KNN93J\SQLEXPRESS;Database=test;User Id=sa;Password=sa;”,
“Test”: “Server=WIN-TAUP5KNN93J\SQLEXPRESS;Database=live;User Id=sa;Password=sa;”
}

Create generic interface and class to pass the connection string between service

Because the heart of .NET Core is DI. we are going to use DI to pass the connection strings between different classes and services.

public interface IDBInfo
{
string GetConnectionString();
}
public class DBInfo : IDBInfo
{
private string _dbString;
public DBInfo(string connString)
{
_dbString = connString;
}
public string GetConnectionString()
{
return _dbString;
}
}

DI passing connection string to DBinfo class

Using below code to get connection string from setting file and pass to DBInfo class.

services.AddTransient(x=> new DBInfo(Configuration.GetConnectionString(“Dev”)));

Add Nuget Package

Open you database access project and add below two Nuget packages:

Install System.Data.SqlClient for using SqlConnection

Install Dapper

Using Dapper

To make things simple, I am only show Dapper related code at below. Dapper has number of extension methods. For detail Dapper API, you can get from Dapper Gehub project page. In below code shows how to pass connection string from contractor to service and fetch the data from table.

public class UserService : IUserService
{
#region Fields
private readonly IDBInfo _db;
#endregion
public UserService(IDBInfo dbInfo) {
_db = dbInfo;
}
public List GetUserList()
{
using (var con = new SqlConnection(_db.GetConnectionString()))
{
var users = con.Query(
“SELECT [User_Id],[PC_Name],[PC_IP],[First_Name],[Last_Name],[Password],[Is_Manager] FROM [tb_users]”
)
.ToList();
return users;
}
}
}