[Note: This post is a preliminary version of a document that has been published on MSDN. The published version with changes resulting from the comments received is now available here. Thanks to everyone who sent comments about this version.]
This is one of a series of posts that present preliminary versions of pages that will eventually be published on MSDN. These pages are a work in progress and suggestions for improvements or corrections are welcome. The intended audience is newcomers to ASP.NET and the purpose is to provide basic guidance for making data access choices for ASP.NET application development. Please forgive the less than ideal formatting -- the HTML for this post was generated by tools that weren't designed for blog publishing. I corrected some of the deficiencies but did not fix all of them.
The series includes:
-- Tom Dykstra ASP.NET User Education
This topic provides examples of SQL Server connection strings for typical ASP.NET web application scenarios. It also includes instructions for converting connection strings created by Visual Studio project templates between SQL Server Express and LocalDB. The remaining sections of the topic explain some of the more common connection string settings.
This topic contains the following sections:
The examples show the connection strings as they appear in the ConnectionStrings element of the Web.config file, where the connectionString attribute must be accompanied by a providerName attribute.
In all of these examples, if you are using Entity Framework Code First, ConnectionStringName is typically the name of the context class.
You can also construct connection strings in code by using the SqlConnectionStringBuilder API for SQL Server Express, LocalDB, SQL Server, or SQL Database. There is no corresponding API for SQL Server Compact.
Connection strings are not wrapped to multiple lines in the Web.config file but are done so here due to limitations of this blog.
Integrated Security=True;User Instance=True;
Initial Catalog=DatabaseName;Integrated Security=False;
Initial Catalog=DatabaseName;Integrated Security=True;
Initial Catalog=DatabaseName;Integrated Security=False;
"Data Source=|DataDirectory|\DatabaseFileName.sdf" />
provider connection string="
Data Source=ServerName;Integrated Security=False;
Project templates for Visual Studio 2010 and Visual Web Developer 2010 Express create connection strings that specify SQL Server Express databases. To convert one of these connection strings to LocalDB, make the following changes:
Project templates for Visual Studio 2012 and Visual Studio Express 2012 for Web create connection strings that specify LocalDB databases. To convert one of these connection strings to SQL Server Express, make the following changes:
For SQL Server Express, LocalDB, SQL Server, SQL Database, this setting specifies the name of the server and the SQL Server instance on the server. For example, you can specify ServerName\Instancename. You can use ".", "(local)", or "localhost" in place of the server name to specify the local computer, and you can use an IP address instead of the server name. If you omit the instance name, the default instance is assumed. By default, SQL Server Express is installed without a default instance and with a named instance that is named SQLEXPRESS.
For LocalDB, use "(LocalDB)\V11.0" to refer to the automatic instance of LocalDB for SQL Server 2012. For named instances of LocalDB, replace "V11.0" with the name of the instance. For information about named instances of LocalDB, see SQL Server 2012 Express LocalDB.
For SQL Database the value of this setting typically has a "tcp:" prefix to indicate the protocol used and a ",1433" suffix to indicate the port number.
For SQL Server Compact, this setting specifies the path and name of the database file. You can use the |DataDirectory| variable in place of an absolute path; for information about this option, see the following section about the AttachDBFileName setting.
This setting specifies the path and name of the database file for SQL Server Express or LocalDB databases that are not defined in the local SQL Server Express instance until the first time you access them by using the connection string. It is typically used for database files that you keep in the App_Data folder.
The App_Data folder a relatively secure place to store database files that you want to keep in the web application folder structure. ASP.NET will not serve contents of the App_Data directory in response to Web requests, which helps to maintain the security of the data in database files kept in this folder. In place of the physical path to a database file that is stored in the App_Data folder, you can specify the |DataDirectory| variable in the AttachDbFileName setting. ASP.NET automatically substitutes the file path to the App_Data directory for the |DataDirectory| connection-string variable when it opens a connection to the database. This ensures that the path to your database remains current if the application is moved to a different directory. If you don't use the |DataDirectory| connection string variable, you have to provide the full physical path to the database file.
For information about the database name that is used when the file is attached by the SQL Server Express or LocalDB instance, see the following section about the Initial Catalog setting.
Whichever method you use for specifying the location of the .mdf file, the .ldf file (log file) should be in the same folder as the .mdf file.
We recommend that if you want to use this option, you use it with LocalDB rather than SQL Server Express. If you use this option with SQL Server Express, you also have to specify the User Instance option, which is deprecated.
If you use this option with SQL Server Express, you won't find the database listed in the local SQL Server Express instance when you use SQL Server Management Studio or SQL Server Data Tools. For more information, see User Instance later in this topic.
The first time you connect to a database by using this option in the connection string, the SQL Server Express or LocalDB instance attaches the database, and it stays attached. When you want to connect to the same database in the future you could use Initial Catalog without AttachDbFileName if you prefer.
This setting specifies the name of the database in the SQL Server instance catalog.
If you omit this setting, ADO.NET connects to the default database for the SQL Server instance specified in the Data Source setting.
If you omit this setting and include the AttachDbFileName setting for SQL Server Express or LocalDB, the full path to the database file is used as the database name. If the full path is longer than 128 characters, a default database name is constructed from the path by using a hash.
In LocalDB connection strings, the Visual Studio web project templates add a unique number as a suffix to both the file name and the Initial Catalog setting, as shown in the following example:
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0; Initial Catalog=aspnet-ProjectName-20120702083251; Integrated Security=SSPI; AttachDBFilename=|DataDirectory|\aspnet-ProjectName-20120702083251.mdf" providerName="System.Data.SqlClient" />
The reason for this is to avoid database name collisions in the SQL Server Express instance for LocalDB. Suppose the project specified the following values:
When you create a project with these settings and run it, LocalDB attaches the aspnet.mdf file and names the database "aspnet" Then when you create another project and run that one, LocalDB tries to attach that project's aspnet.mdf file to the same database name "aspnet". The operation fails because that name is already attached to the first project's aspnet.mdf file.
For the same reason, the suffixes added by the Visual Studio templates won't prevent name collisions if you create a copy of a project by copying the files instead of by creating a new project in Visual Studio, which generates a new unique number. If you want to create a new project by copying an existing project's files, change the suffix number manually to make it unique.
If you prefer simpler file names, omit the Initial Catalog setting, as shown in this example:
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0; Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet.mdf" providerName="System.Data.SqlClient" />
With this connection string, the first project attaches its aspnet.mdf file to a database name that includes the full path to the file. A second project attaches its aspnet.mdf file to a different name because it is in a different folder. A disadvantage of this approach is that database names are long in the SQL Server Object Explorer window. If you have many such database names and the window is not especially wide, it might be hard to differentiate them, as you see in the following illustration:
This setting specifies whether the connection specifies the user ID and password, or the current Windows account credentials should be used for authentication:
You have to use integrated security when you use the AttachDBFileName option.
The MultipleActiveResultSets (MARS) option makes it possible to execute multiple queries simultaneously. This is a common scenario when you use the Entity Framework, especially if you leave lazy loading enabled. For example, the second line of code in the following example will raise an exception if you don't enable MARS because the query that returns instructors is still active when the ToList method executes a new query that retrieves related courses:
It is often more efficient to load related data by using eager loading, which retrieves all of the data by using a single join query, but sometimes join queries are inefficient. In most real-world applications, there are times when the best choice for loading related data is lazy loading or explicit loading. Therefore, connection strings for databases that you access by using the Entity Framework typically specify this option. This option carries a slight performance penalty, but in most scenarios its benefits outweigh any performance loss even if you aren't using the Entity Framework. For more information, see the following resources:
This option is only used with SQL Server Express, not with LocalDB, a full edition of SQL Server, or SQL Server Compact.
You use the AttachDbFileName setting to connect to a database in an .mdf file, typically located in the web application's App_Data folder. In order to connect to this database file, the SQL Server Express instance on your computer has to attach the file. But only SQL Server Express administrators can attach databases, so this operation will fail if you are not an administrator in the SQL Server Express instance. Also, even if you are an administrator and are able to attach the .mdf file, the default service account used by SQL Express might not have permissions to read and write the .mdf file. The service account typically does not have permissions for folders in your user profile, such as your My Documents folder, which is where Visual Studio projects are created by default. You use the User Instance option to solve these problems.
When you have enabled the User Instance option in the SQL Server Express instance and connect using a connection string that has User Instance set to True, a special instance of SQL Server Express is created for your user account. You are a SQL Server Express administrator in this user instance, so the instance is able to attach the database file. And when your web application runs under the Visual Studio Development Server (Cassini) or IIS Express, the user instance runs under your account, so it has access to folders in your user profile. For information about what identity is used for the user instance when the application runs in IIS, see ASP.NET Impersonation.
Databases that you have attached to a user instance are difficult to manage by using tools such as SQL Server Management Studio (SSMS). You won't see them in SSMS when you attach to the SQL Server Express instance on your computer.
For more information about SQL Server Express user instances, see the following resources:
For more information about connection strings, see also the following resources: