VS2012 is out and with it brings a host of developer improvements when dealing with data. This post aims at highlighting a few key improvements which will affect you the most and hopefully you will find this information useful in getting started with the improvements in Visual Studio tooling to interact with databases. While the Data Tooling itself has undergone a lot of changes, this post is geared towards Web Application Developers.
The quick pitch for LocalDB is the following: “It is very easy to install and it requires no management” LocalDB runs as your account and not as a system wide service(which is how SqlExpress runs). This post from the SQL team goes in detail about the benefits
With all the changes of moving away from SqlExpress to LocalDB, nothing should change in terms of your application development. You should be able to use almost the same connectionStrings as you had with SqlExpress, but instead change the DataSource from “.\SQLExpress” to “(LocalDb)\v11.0” and remove the UserInstance flag since LocalDb always runs as your account
If you look at the connectionstring, the templates for ASP.NET WebForms, MVC use in VS 2012, you will notice that the connectionstring looks like follows. This connection is somewhat different to what you might have seen with web projects in VS2010 where we were only using AttachDbFileName.This change has nothing to do with LocalDb, on the contrary this exposes some of the common patterns that have existed when working with SQL.
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;
Initial Catalog=aspnet-MvcApplication18-20121022222325;Integrated Security=SSPI;
Following is how these two values are used
Initial Catalog: This entry is a key in the SQL master table which holds all of the databases which were created on an instance. In this case the instance is “(LocalDb)\v11.0”
AttachDbFileName: This entry tells SQL the location of the mdf file which holds the database information.
The SQL engine uses Initial Catalog to look up the database entry in its master list and load the database file from the path specified by AttachDbFileName
By surfacing this information to the developer, a developer can run into the following situation. Let’s say that you ran your application and created a database with the above connectionstring. In this case, if you delete the database file(mdf) from disk and do not change the connectionstring, then SQL will look up the database by the Initial Catalog entry which still exists and will try to load the database from disk based on the AttachDbFileName and since the database does not exist, you will get the following error.
“Cannot attach the file 'c:\users\foo\documents\visual studio 2012\Projects\MvcApplication18\MvcApplication18\App_Data\aspnet-MvcApplication18-20121022222325.mdf' as database 'aspnet-MvcApplication18-20121022222325'.”
To workaround this error do either of the following
The reason the AttachDbFileName entry exists so that the database file gets dropped in App_Data folder of your application.
These cheat sheet is useful when you are trying to figure out what instance to use for LocalDB
I hope this would help you understand the changes in the Data Tooling experience for VS2012. This was cross posted at http://blogs.msdn.com/b/webdev/archive/2012/11/12/interacting-with-databases-for-web-developers-in-vs-2012.aspx