ASP.NET Core Connection String

Introduction to ASP.NET Core Connection String

ASP.NET Core Connection String is mainly used to connect to the database, which can be stored in the appsetting.json. The majority of database providers need some type of connection string to establish a database connection. The connection string contains the perceptive information that requires being safest. To change the connection string, we need to move the application between production, development, and testing environments.

ASP.NET Core Connection String Overviews

The ASP.NET Core configuration system is especially stretchy in which the connection string is stored in the appsettigs.json, the user stores secret, an environment variable, and the configuration source.

How to manage ASP.NET Core Connection String?

In ASP.NET Core, we need not use the Web. config file we need to use the other approaches they are as follows,

Hard-Code Connection String

Initially, to hard code the connection string in the startup.cs file as shown below,

var _conString = @"Data Source=ATIQ;Initial Catalog=UserDB;Integrated Security=False;Persist Security Info=False;
User ID=sa;Password=******";
services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connection));
Connection String from the appSetting.json
services.AddDbContext<MyDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Here the default-connection is defined at the root directory of the application in appSetting.json

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DefaultConnection":
"DATA SOURCE=xxx\\SQLEXPRESS;DATABASE=xxx;UID=sa;PWD=xxx"
}
}

Connection String from dataSetting.json – it’s the custom JSON file

The next method is that to get the connection string from the JSON custom method and to de-serialize for getting the values of the connection string as follows,

var _settings = new DataSettingsManager().LoadSettings();
var _connection = _settings.DataConnectionString.ToString(); services.AddDbContext<MyDbContext>(options => options.UseSqlServer(_connection));

Here the MyDbContext is the data context, and DataSettingsManager is the one more class to store the serialized values of the connection string in the object DataSettings. Let’s see the following code as follows,

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();
//here we getting the data setting from the JSON File
DataSettings settings = JsonConvert.DeserializeObject<DataSettings>(text);
return settings;
}
}
public class DataSettings
{
public string DataConnectionString { get; set; }
}

To create the folder at the root directory naming with the “App_Data” and build the JSON file with “dataSetting.json,” the code below is as follows,

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

ASP.NET Core Connection String Configuring

In ASP.NET Core Web Application appSettings.json is the configuration file that is used here. The main purpose of this is used to store the connection string to the database. However, it is also used to store a variety of other settings which we can use in our application.
There are several configuration providers in ASP.NET Core; the configuration providers read the configuration data from several sources of configurations like key-value pairs; let’s see the following methods as follows,

• Environment Variables
• appSetting.json, setting files
• Comman-line arguments
• Azure Key Vault
• Azure App Configuration
• Directory Files
• Custom providers, created/ installed
• In-Memory objects of .NET

The above topics give the information on the configuration in ASP.NET Core.

ASP.NET Core Connection String web application

Let’s see one example of a web application in ASP.NET Core, ensure that to install the required packages in NuGet, we need to use
System.Data.SqlClient

Our appSetting.json string will be as follows,

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"ConnectionString":
"DATA SOURCE=xxx\\SQLEXPRESS;DATABASE=xxx;UID=sa;PWD=xxx"
}
}

We required adding a few to the Startup.cs file as follows,

public void ConfigureServices(IServiceCollection services)
{
services.AddRazorPages();
services.AddSingleton<IConfiguration>(Configuration);
}

We also added the,

services.AddSingleton<IConfiguration>(Configuration);

Let’s create the database class, initially create the Models classes in the solution explorer, and then create the class called Measurement. cs finally code for retrieving the data from the database. The code below is as follows,

using System.Data.SqlClient;
namespace MeasurementApp.Model
{
public class Measurement
{
public int Msmt_Id { get; set; }
public string Msmt_Name { get; set; }
public string Msmt_Unit { get; set; }
public List<Measurement> Get_MeasurmentParameters(string connectionString)
{
List<Measurement> msmt_List = new List<Measurement>();
SqlConnection con = new SqlConnection(connectionString);
string sqlQuery = "select Msmt_Id, Msmt_Name, Msmt_Unit from MEASUREMENT";
con.Open();
SqlCommand cmd = new SqlCommand(sqlQuery, con);
SqlDataReader dr = cmd.ExecuteReader();
if (dr != null)
{
while (dr.Read())
{
Measurement _msmtParameter = new Measurement();
_msmtParameter.Msmt_Id = Convert.ToInt32(dr["Msmt_Id"]);
_msmtParameter.Msmt_Name = dr["Msmt_Name"].ToString();
_msmtParameter.Msmt_Unit = dr["Msmt_Unit"].ToString();
msmt_List.Add(_msmtParameter);
}
}
return msmt_List;
}
}
}

This ASP.NET Core Web Page contains the two codes: the “Database.cshtml” and the “Database.cshtml.cs”. Let’s see the above two codes below,

Database.cshtml.cs

using Microsoft.Extensions.Configuration;
using AppSettingsApp.Models;
namespace AppSettingsApp.Pages
{
public class DatabaseModel : PageModel
{
readonly IConfiguration _configuration;
public List<Measurement> measurementParameterList = new List<Measurement>();
public string connectionString;
public DatabaseModel(IConfiguration configuration)
{
_configuration = configuration;
}
public void OnGet()
{
GetData();
}
void GetData()
{
Measurement _msmt = new Measurement();
connectionString = _configuration.GetConnectionString("ConnectionString");
measurementParameterList = _msmt.Get_MeasurmentParameters(connectionString);
}
}
}

Database.cshtml

<div>
<h1>Measurement Parameters</h1>
Below you see all the Measurement Names registered in the Database:
<table class="table">
<thead>
<tr>
<th>MeasurementId</th>
<th>Measurement Name</th>
<th>Unit</th>
</tr>
</thead>
<tbody>
@foreach (var measurement in Model.msmt_List)
{
<tr>
<td> @measurement.Msmt_Id</td>
<td> @measurement.Msmt_Name</td>
<td> @measurement.Msmt_Unit</td>
</tr>
}
</tbody>
</table>
</div>

ASP.NET Core Connection String example

We will use SQL Server in this example as our database.

CREATE TABLE [MEASUREMENT]
(
[Msmt_Id] int NOT NULL IDENTITY ( 1,1 ) Primary Key,
[Msmt_Name] varchar(100) NOT NULL UNIQUE,
[Msmt_Unit] varchar(50) NULL
)
Go

Once creating the table, in order to retrieve the data, manually add/ enter the data as per the attributes in Measurement Table.
The connection string in the startup.cs file as shown below,

var _conString = @"Data Source=ATIQ;Initial Catalog=UserDB;Integrated Security=False;Persist Security Info=False;
User ID=sa;Password=******";

Conclusion

In this article, we have seen the ASP.NET Core Connection String, the main purpose is to store the connection string to the database, and it’s also used to store a variety of other settings that we can use in our application. I hope the article helps you understand the usages and variety of methods of connection string used.

Recommended Articles

This is a guide to ASP.NET Core Connection String. Here we discuss the Introduction, overviews,  How to manage ASP.NET Core Connection String examples with code implementation. You may also have a look at the following articles to learn more –

1. Dataset in ASP.NET
2. Validation in ASP.NET
3. ASP.NET life cycle
4. ASP.NET security