Interacting with Databases for Web Developers in VS 2012

  • [アーティクル]

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.

Changes in VS2012
  • VS2012 ships with SQL2012 engine.
  • VS2012 ships with an enchanced Sql tools(SSDT) which brings in functionality from SSMS(SqlServerManagementStudio) to VS
    • As part of this integration, you will see a new window called “SQL Server Object Browser”. In this window you can do advanced Database management stuff, such as running sprocs, managing Initial Catalogs etc. which were not possible in “Database Explorer”
    • Some videos to learn more about SSDT.
  • "Sql Server Express" is no longer installed with VS. Instead VS ships with "Sql Server Express LocalDB".
    • LocalDB development is supported in VS2012 for .NET v4.0/v4.5 on Windows 8/ Windows 7 – Client and Server SKUs
    • LocalDB development is supported in VS2012 for .NET v3.5 on Windows 8 only(Client and Server SKUs)
  • Enhanced User experience while upgrading projects from VS2010 to VS2012
    • This user experience has some guidance which help you upgrade databases which were using SQLExpress to LocalDB
  • Auto loading of ConnectionStrings in ServerExplorer
    • if we detect that your ConnectionString points to a database which exists, then we load the connectionstring in the ServerExplorer so its there when you want to use it. This is an improvement from Dev10 where you had to explicitly add the ConnectionString
Why LocalDB?

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

Initial Catalog vs AttachDbFileName

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;

AttachDBFilename=|DataDirectory|\aspnet-MvcApplication18-20121022222325.mdf"

providerName="System.Data.SqlClient" />

  

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

One small caveat

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

  • Change the Initial Catalog and AttachDbFileName value to be something unique
  • Using SSDT, connect to “(LocalDb)\v11.0” instance and delete the Initial Catalog entry specified in the database

The reason the AttachDbFileName entry exists so that the database file gets dropped in App_Data folder of your application.

 
How to Videos for basic operations in VS tooling

 

Working with Entity Framework Code First

Cheat sheet of things to remember/do for Data in VS2012

These cheat sheet is useful when you are trying to figure out what instance to use for LocalDB

  • Default Instance of Localdb in VS2012 is "(LocalDb)\v11.0"
    • This means whenever you are connecting to a localdb instance you need to type in the above to connect to using Windows Authentication to operate on your database
  • Tools-Options is "(LocalDb)\v11.0"
    • This means that any local database that you create will be created using "(LocalDb)\v11.0" instance
    • If you have SqlExpress installed and you want to use SqlExpress as the default local database server you should change this option in VS. This is a global setting for VS
  • LocalDB does not run with UserInstance=true setting
    • You will get an error when you run your application
Helpful links/scripts 

I hope this would help you understand the changes in the Data Tooling experience for VS2012. This was cross posted at https://blogs.msdn.com/b/webdev/archive/2012/11/12/interacting-with-databases-for-web-developers-in-vs-2012.aspx