Database Connections In ASP.NET Core 2.0

Introduction

In this blog post, we will discuss three ways to define database connection string, get its values, and retrieve data in ASP.NET MVC core.

Details

We will just create a simple ASP.NET MVC Core web application.

For this, follow these steps in Visual Studio 2017.

File > New > Project.
Select ASP.NET Core Web Application.
Set name and location
In next screen, select Web Application (Model –>View –> Controller) Template, select "No Authentication", and click OK.

Now, add some necessary NuGet packages.

Tools - NuGet Package Manager - Package Manager Console
Run Install-Package Microsoft.EntityFrameworkCore.SqlServer

For Entity Framework Core Tools to create a database from your EF Core model:

Run Install-Package Microsoft.EntityFrameworkCore.Tools

And for ASP.NET Core Scaffolding tools to create Controllers and Views:

Run Install-Package Microsoft.VisualStudio.Web.CodeGeneration.Design

If you are not already familiar with ASP.NET MVC Core web application development and have not developed even a single application, you should get a basic understanding of its development or read my blog by clicking here.

In previous versions of ASP.NET, we often used web.config for connection strings but in ASP.NET Core, we cannot use web.config for this purpose; rather, we can use other techniques or methods for it. Some of the simple methods are given below.

Method 1: Hard code connection string

The first and most basic method is to hard code the connection string into Startup.cs file as in the code below.

//Method 1 (Hard coded connection string)
var connection = @"Data Source=ATIQ;Initial Catalog=UserDB;Integrated Security=False;Persist Security Info=False;
User ID=sa;Password=******";
services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connection));

Method 2: Get connection string from appsettings.json

//Method 2 (Get connection string from appsettings.json)
services.AddDbContext<MyDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

where "DefaultConnection" is defined in appsetting.json at root directory of the application.

{
"Logging": {
"IncludeScopes": false,
"LogLevel": {
"Default": "Warning"
}
},
"ConnectionStrings": {
"DefaultConnection": "Data Source=ATIQ;Initial Catalog=UserDB;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=www@123"
}
}

Method 3: Get connection string from datasetting.json -- our custom JSON file

The third method is to get the string from custom JSON file and deserialize it for getting the values of connection string.

//Method 3 (Get connection string from datasetting.json - our custom json file)
var settings = new DataSettingsManager().LoadSettings();
var connection = settings.DataConnectionString.ToString();
services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connection));

Where "MyDbContext" is our data context class and "DataSettingsManager" is another class that saves serialized values of connection string in object "DataSettings." It looks like the following.

public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options)
: base(options)
{ }
public DbSet<MyUser> MyUser { get; set; }
}
public class DataSettingsManager
{
private const string _dataSettingsFilePath = "App_Data/dataSettings.json";
public virtual DataSettings LoadSettings()
{
var text = File.ReadAllText(_dataSettingsFilePath);
if (string.IsNullOrEmpty(text))
return new DataSettings();
//get data settings from the JSON file
DataSettings settings = JsonConvert.DeserializeObject<DataSettings>(text);
return settings;
}
}
public class DataSettings
{
public string DataConnectionString { get; set; }
}

You have to create a folder at the root directory named "App_Data" and create a JSON file named "datasettings.json" that has code like this:

{
"DataConnectionString": "Data Source=ATIQ;Initial Catalog=UserDB;Integrated Security=False;
Persist Security Info=False;User ID=sa;Password=******"
}

Last Words

We can get any type of our custom settings or key values by using these methods in Asp.Net MVC Core.

All necessary code related files are attached to this blog post; just copy and paste into your project at the root. Please change connection string with your SQL Server in Startup.cs file. Also, modify the namespaces as your project name.