Connection strings in ASP.NET MVC 5

Connection strings in ASP.NET MVC 5

When you start a new ASP.NET MVC 5 Web Project you’ll see a connection string in your Web.config xml like so:

 

This default connection string connects your code base with a local database (LocalDB) file with a name that, in the above case, looks like this: aspnet-WebApplication-20150706073844.mdf.

Lets look at the components in this string one by one.

name

from msdn:  

“(name) Defines the key to use to access the connection string value. This attribute overrides any inherited key with the same name.”

“name” is a required field in your connection string and is used to associate your data with your code base. This association brings your data into context with your code. In ASP.NET MVC 5, you’ll find this association in your Models folder in a class called IdentityModels.cs, here’s the function that makes the association.

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }
 
    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
}

providerName

from msdn:

“The name of the ADO.NET provider to use to access the underlying data store… the default is “System.Data.SqlClient.”

In most cases, you’re going to leave this with its default value.

providerName="System.Data.SqlClient"

If you’re using entity framework Database First or Model First, you’ll use:

providerName="System.Data.EntityClient"

And if you’re using SQLServer Compact you’ll use:

providerName="System.Data.SqlServerCe.4.0"

connectionString

from msdn:

“Defines the connection string value. the value can be an empty string (“”). No checking is done to verify its validity… the default is an empty string.”

LocalDb Connection String

To get your application up and running quickly, Microsoft starts you out with a localDb file and connection, which looks like this:

connectionString="Data Source=(LocalDB)\v11.0;
                  AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;
                  InitialCatalog=DatabaseName;
                  Integrated Security=True;
                  MultipleActiveResultSets=True" />

Connection String Attributes:

  • Data Source
  • AttachDBFileName
  • InitialCatalog
  • Integrated Security
  • MultipleActiveResultsets

SQL Server Express Connection String

When I begin a new application my first step is to set up a non-local SQL Server Express connection string for use with entity framework (code first), which looks like this.

connectionString="Data Source=.\SQLEXPRESS;
                  Initial Catalog=DatabaseName;
                  Integrated Security=True;
                  MultipleActiveResultSets=True”/>

Connection String Attributes:

  • Data Source
  • Initial Catalog
  • Integrated Security
  • MultipleActiveResultSets

SQL Server Full Edition Connection String

When you’re ready to publish your web application to a server you’ll need to change that Integrated Security to false. When it’s set to true you’re using your Windows Authentication login and password to establish a connection. For obvious reasons you’ll want a more secure connection in a live setting. Here’s an example connection string.

connectionString="Data Source=ServerName;
                  Initial Catalog=DatabaseName;
                  Integrated Security=False;
                  User Id=userid;
                  Password=password;
                  MultipleActiveResultSets=True" />

Connection String Attributes:

  • Data Source
  • Initial Catalog
  • Integrated Security
  • User Id
  • Password
  • MultipleActiveResultSets

For Windows Azure, Entity Framework (data first) and SQL Server Compact connection strings, see the msdn documentation on connection strings.

Reference

Leave a Reply

Your email address will not be published. Required fields are marked *