Asp.net MVC SQL Connection String

Let's learn how to work with database connection in asp.net mvc application, here in example I show you how to create connection string for SQL and connection string for Entity Framework

Create database connection in Asp.net MVC

Now we learn how to connect SQL database using SQL connection string in Asp.net MVC application

Open your web.config file in root of your web application, Now you can setup your database connection details in two ways.

Database connection string in Web.Config

1. Setup asp.net mvc web.config connection string sql server property directly in web.config with right providerName.

<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=189.187.147.17;Initial Catalog=WTRAppDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
</connectionStrings>

2. In web.config file appSettings section adding each property value then read the value and form the connection string in your code.

<appSettings>
<add key="ServerName" value="189.187.147.17"/>
<add key="DatabaseName" value="WTRAppDatabase"/>
<add key="SchemaName" value="dbo"/>
<add key="UserId" value="dbuser100"/>
<add key="Password" value="xxpassword123"/>
</appSettings>

Read Database connection string in C#

Now read the database connection details in your C# code, create a utility class like below. Look how to create SQL server connection string in Asp.net MVC

//namespace required
using System.Configuration;
using System.Text;
using System.Data.Entity;
using System.Data.EntityClient;
using System.Data.SqlClient;         

public static string DBConnectionString
{
    get
    {
    SqlConnectionStringBuilder providerCs = new SqlConnectionStringBuilder();
    providerCs.InitialCatalog = ConfigurationManager.AppSettings["DatabaseName"];
    providerCs.UserID = ConfigurationManager.AppSettings["UserId"];
    providerCs.Password = ConfigurationManager.AppSettings["Password"];
    providerCs.DataSource = ConfigurationManager.AppSettings["ServerName"];               

    providerCs.MultipleActiveResultSets = true;
    providerCs.TrustServerCertificate = false;
    providerCs.IntegratedSecurity = true;            

    return providerCs.ToString();
    }
}

Note: in real life, you may need to keep encrypted username and password in web.config, in that case you simply decrypt the property value by using your encryption utility this way

providerCs.InitialCatalog = UtilityService.Decrypt(ConfigurationManager.AppSettings["DatabaseName"]);

Your connectionString is ready to be used in your Ado.net class.

Asp.net MVC entityframework connection string

Now if you are using entityframework, then adding below code will make the job easier.

EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder();
ecsb.Provider = "System.Data.SqlClient";
ecsb.ProviderConnectionString = providerCs.ToString();
ecsb.Metadata = string.Format("res://{0}/entityfilename.csdl|res://{0}/entityfilename.ssdl|
res://{0}/entityfilename.msl", typeof(Entities).Assembly.FullName);

Another way could be instead of keeping the connectionstring details in web.config file, keep them in a separate xml file and read the file using .net configuration framework

Note: If you are using Asp.net Coe MVC then you should look at Asp.Net core database connection string example

.Net MVC Tutorial | Asp.net MVC Course | MVC interview questions

Database connection string in Asp.net MVC example

Aspnet MVC Training

Asp.net MVC tutorials, learn model view controllers with c#, develop database driven web application using Asp.net MVC framework.

Hire .Net Developer

Free Tutorials

Asp.net Core Tutorial

Learn .Net C# Programming

SQL Programming Tutorial

Asp.net Core interview question

Asp.Net MVC C# Examples | Join Asp.Net MVC Course | Asp.net Core Tutorial