Online Training for Asp.net MVC Web Designing Development, MS SQL, Digital Marketing

Database connection string in Asp.net MVC

How to connect database in Asp.net MVC

Now we learn how to connect database using database 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.


1. Set up connectionString property directly in webconfig 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>

Now read the details in your utility class this way
//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.
But 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

Comment
Name Email Website
Subscribe
 
Database connection in Asp.net MVC

Group Training