SQLExpress Connection String Features

by Peter A. Bromberg, Ph.D.

Peter Bromberg
"Too bad the only people who know how to run the country are busy driving cabs and cutting hair."--George Burns

In the SqlClient Managed Provider, there has always existed a connection string keyword called "AttachDbFileName". The user can have the location of the database file assigned to this keyword in the connection string and this file gets attached as a database on the server.



This is referred to as a "User Instance".

User Instance Limitations

The unique User Instance architecture introduces some functional limitations as follows:

  • Only local connections are allowed.
  • Replication does not work with user instances.
  • Distributed queries do not work to remote databases.
  • User instances only work in the Express Edition of SQL Server 2005.

You can read a whitepaper on User Instances here.

Here is an example:

SqlConnection cn = new SqlConnection (@“Data Source=.\SQLEXPRESS; Integrated Security=True;"+
@"AttachDbFilename=C:\MyApplication\MyDatabase.mdf;Initial Catalog=MyDatabase");
cn.Open();

The above code attaches file C:\MyApplication\MyDatabase.mdf as DataBase MyDatabase on the server, at runtime.

Default Relative Paths:

You write an application that references the database file. You don't want to have a hard reference to the location of the MDF file in your code. Instead, use relative paths. In ADO.Net 2.0, you can use the substitution string (e.g., |DataDirectory|) to get the absolute path of the DB file to attach at run time. By default, |DataDirectory| is substituted with the base directory that the application(/assembly) is running from.

Example:
SqlConnection c = new SqlConnection (@“Data Source=.\SQLEXPRESS; Integrated Security=True;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Initial Catalog=MyDatabase");
c.Open();


Result: When run in the C:\MyApplication directory, the above code attaches file C:\MyApplication\MyDatabase.mdf as DataBase MyDatabase on the server

Custom Relative Paths:

You can reset the value that is substituted for |DataDirectory| to be anything you want. This can be done as follows:

AppDomain.CurrentDomain.setData(“DataDirectory”,”C:\MyNewPath\”);
SqlConnection c = new SqlConnection (@“Data Source=.\SQLEXPRESS; Integrated Security=True;"+
@"AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Initial Catalog=MyDatabase");
c.Open();

The "DataDirectory" AppDomain Cache property tells SQLExpress (and any other applications that use this) that the specified MDF file in that folder is to be attached. If you aren't familiar with the GetData and SetData Static methods of the AppDomain Class, check here:

SetData: http://msdn2.microsoft.com/en-us/library/system.appdomain.setdata.aspx
GetData: http://msdn2.microsoft.com/en-us/library/system.appdomain.getdata.aspx

Result: The above code attaches file C:\MyNewpath\MyDatabase.mdf as DataBase MyDatabase on the server.

Note:The above strings are hardcoded in example above just to illustrate the concept of Subsititution strings in ADO.Net. In normal practice, these connection strings should be stored into and retrieved from the configuration files. Don't forget that in ASP.NET 2.0, you have the new ConnectionStrings section. Also, developers should be aware that this "dynamic attachment" of databases in the connection string is ONLY SUPPORTED IN SQLEXPRESS, NOT IN SQL SERVER 2005.

Profile and Membership:

In ASP.NET 2.0, the default provider is SQLExpress and the default provider's NAME is "AspNetSqlMembershipProvider". Developers are often confused by this since these settings are in the machine.config file which hardly anyone ever looks at (do you know exactly where it is? Don't mess with it without making a backup first!). So if you do not have a <clear/> or <remove name=.../> directive, you can run into trouble when attempting to use "your own" specified database as the repository for Membership, Role and Profile information. While I am on this subject, did you know that you can perform the equivalent of ASPNET_REGSQL with:

System.Web.Management.SqlServices.Install( database, SqlFeatures.All, connectionString );

There are several overloads on the above, as well as a number of other classes and methods to enable IIS, Session State and much more. If your site is on a hosted environment where you do not have access to command-line utilities, you can build a web page to set up your SQL Database for Membership, Roles, Profiles, and even Session State - programmatically!

More info on provider model:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/ASPNETProvMod_Intro.asp.

Other providers:

You may also want to have relative paths to a UDL file in OleDb or a DSN file in ODBC. These are also supported and the syntax is similar to the AttachDbFileName example above. There are a few restrictions in using the substitution string:

SqlClient Managed Provider: can be used only for the AttachDbFileName keyword
OleDb Managed Provider:  can be used for any keyword other than user id, password and servername.
ODBC Managed Provider:  can be used for any keyword other than user id, password and servername.
Oracle Managed Provider:  not valid for any keywords.

Summary: SQLEXPRESS is an extremely powerful and FREE version of SQL SERVER 2005 that supports most of the advanced features including Full Text, Reporting, and CLR-hosted assemblies. It also comes with a pretty advanced version of Sql Managment Studio. If you are still messing around with MS Access or MSDE, get with the program! Dowload SQLEXPRESS here. You want the "SQL Server 2005 Express Edition with Advanced Services" - 234MB.


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.
Article Discussion: