How to set a connection string in Asp.net Core?

What is the connection string?

A connection string is a string that is used to represent information about database connection. It contains information that different database providers need to know to establish a connection between different databases and applications.

Sometimes the connection string contains sensitive information. The connection string may change as the application moves between environment like development, testing, and production.

Why do we need a connection string?

There are different database provider and each database provider have multiple ways to connect. And every database provider required some form of the connection string to connect with the database and there are different ways to write a connection string.

The DbContext Connects to the different databases using a database provider. These database providers require a connection string to connect to the database.

Where are these connection strings located?

The Asp.net Core is an open-source cross-platform use to create server applications including cloud-based development. In the Asp.Net application web.config file is used to manage all configurations. But in Asp.Net core application doesn’t have a web.config file to manage all configuration like database connection.

Asp.Net have JSON (JavaScript object notation) file to manage all configuration because Asp.Net core application is platform-dependent and Json file is readable to all platform. The connection string is just a string inside the Asp.net Core application. The Asp.Net core has an “appsetting.Json” file to write a database connection string.

Basic connection string format

The basic format of the connection string is based on ODBC (open database connectivity) connection string. The connection contains key/values pairs separated by a semicolon (;). The equal (=) sign connects the key to its value.

Keyword1=”value”; keyword2=”value”;

Formatting Rules for the connection string

There is no built-in constraints or rules for the formatting connection string. This is a general rule set adopted by every database provider.

Note: These rules are not accurate for every situation or problem. But using these basic rules we can avoid common problems.

The Rules:

All blank characters, except those placed within a value or quotation marks, are ignored.

• If the value in the connection string contains a semicolon (;)then it must be delimited by quotation marks (“)

Ex. ”key=value; key1=value1;”

• Use a single-quote (‘) if the value in the connection string begins with a double-quote (“) and vice versa, use the double quote (“) if the value in the connection string begins with a single quote (‘)

 “key=’value’; key1=’value1’;”
 ‘key=”value”; key1=”value1”;’

• No escape sequences are supported
• Names are case insensitive
• If the connection string contains more than one KEYWORD=VALUE pair then, the value associated with the last occurrence is used.
• However, if the provider keyword occurs multiple times in the string, the first occurrence is used.
• If a keyword in a string contains an equal sign (=) then it must be preceded by an additional equal sign to indicate that it is part of the keyword.

Connection string in Asp.net core

The “appsetting.json” file is used to define the connection string in Asp.net core.

By Default “appsetting.json” file contains the following code:

{
“Logging”: {
“IncludeScopes”: false,
“LogLevel”: {
“Default”: “Warning”
}
}
}
After adding the connection string:
{
“ConnectionStrings”: {
“DefaultConnection”: “Data Source=192.168.0.12\\SQLEXPRESS; Initial Catalog=Demo;Integrated Security=false;uid=sa;password=demo@1234;”
},
“Logging”: {
“IncludeScopes”: false,
“LogLevel”: {
“Default”: “Warning”
}
}
}

• Data Source: The name or network address of the SQL Server instance for connecting.

server=(localdb)\\myInstance (used to connect with local Db)

• Initial Catalog/Database: The name of the database. The name of the database can be 128 characters or less.
• Integrated Security/Trusted Connection: Default Value of Integrated Security is false, when false, User ID and Password are specified in the connection. When the value of Integrated Security is true, the current Windows account credentials are used for authentication. Values for integrated security can be true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.
• User ID/UID: The SQL Server login account. Not recommended. The user ID must be 128 characters or less than 128 characters.
• Password/PWD: The password for the SQL Server account is used for login. Not recommended and must be 128 characters or less.

Method for writing connection string:

1) Hardcoded connection string:

var connection = @”Data Source=192.168.0.12\\SQLEXPRES;Initial Catalog=blog;Integrated Security=False;Persist Security Info=False;
User ID=sa;Password=demo@1234";
//Get the connection string
services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connection));

2) Get a connection string from appsetting. Json

{
“ConnectionStrings”: {
“DefaultConnection”: “Data Source=192.168.0.12\\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=false;uid=sa;password=demo@1234;”
},
“Logging”: {
“IncludeScopes”: false,
“LogLevel”: {
“Default”: “Warning”
}
}
}
//Get the connection string
services.AddDbContext<MyDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString(“DefaultConnection”)));

How to store and Read connection string from Appsetting.Json?

• Store connection string in Appsetting.Json

{
“ConnectionStrings”: {
“DefaultConnection”: “Data Source=192.168.0.12\\SQLEXPRESS;Initial Catalog=Demo;Integrated Security=false;uid=sa;password=demo@1234;”
},
“Logging”: {
“IncludeScopes”: false,
“LogLevel”: {
“Default”: “Warning”
}
}
}

• Read connection string

public class HomeController : Controller
{
private IConfiguration Configuration;
public HomeController(IConfiguration _configuration)
{
Configuration = _configuration;
}
public IActionResult Index()
{
string connString = this.Configuration.GetConnectionString(“MyConn”);
return View();
}
}

The Connection string for different types of providers and its detail.

• Microsoft SQL Server (Standard connection)

ConnectionString = “Data Source=servername;Initial Catalog=databasename;User id=uasername;Password=password;”;

• MySQL (Standard connection)

ConnectionString=“Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword”;

• Oracle (Standard connection)

ConnectionString =” Data Source=MyOracleDB;
User Id=myUsername;Password=myPassword;Integrated Security=no”;

• Microsoft Access (Standard connection)

ConnectionString =”Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\myFolder\myAccessFile.accdb;Jet OLEDB:
Database Password=MyDbPassword”;

Example:

Step-1: Create an “Asp.net core web application”

• Open visual studio
• Create new Asp.net web application with model-View-controller template
• Visual studio opens your new project

Step-2: Set up the connection string

• Open Microsoft SQL Server Management studio
• Connect your database using Add connection

Right-click on the database and select properties

Copy connection string and paste in “appsetting.json”

{
“ConnectionStrings”: {
“DefaultConnection”: “Data Source=192.168.0.14\\SQLEXPRESS;Initial Catalog=blog;Integrated Security=false;uid=sa;password=blog@1234;”
},
“Logging”: {
“IncludeScopes”: false,
“LogLevel”: {
“Default”: “Warning”
}
}
}

Add Connection string In ConfigureServices method in Startup.cs class

services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString(“DefaultConnection”)));

Read string from JSON file

• Add Microsoft.Extension.Configuration Library
• Add constructor and initialize configuration interface
• Use GetConnectionString method

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
namespace blog.Controllers
{
public class AdminController : Controller
{
private IConfiguration Configuration;
public AdminController(IConfiguration _configuration)
{
Configuration = _configuration;
}
public IActionResult Index()
{
ViewBag.msg = this.Configuration.GetConnectionString(“DefaultConnection”);
return View();
}
}
}

Conclusion

Most database contributors need some form of the connection string to connect to the database. Many times this connection string holds sensitive information that requires to be shielded. We hope this article will help you to understand the connection string in the Asp.net core web application.