{"id":2044,"date":"2012-11-12T08:43:20","date_gmt":"2012-11-12T08:43:20","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/webdev\/2012\/11\/12\/interacting-with-databases-for-web-developers-in-vs-2012\/"},"modified":"2012-11-12T08:43:20","modified_gmt":"2012-11-12T08:43:20","slug":"interacting-with-databases-for-web-developers-in-vs-2012","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/interacting-with-databases-for-web-developers-in-vs-2012\/","title":{"rendered":"Interacting with Databases for Web Developers in VS 2012"},"content":{"rendered":"<p><font size=\"2\">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.<\/font><\/p>\n<p> <font size=\"2\"><\/font>  <\/p>\n<h5><font size=\"2\">Changes in VS2012<\/font><\/h5>\n<p> <font size=\"2\"><\/font>  <\/p>\n<ul><font size=\"2\"><\/font>    <\/p>\n<li><font size=\"2\">VS2012 ships with <\/font><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc645993.aspx\"><font size=\"2\">SQL2012<\/font><\/a><font size=\"2\"> engine.<\/font><\/li>\n<p>   <font size=\"2\"><\/font>    <\/p>\n<li><font size=\"2\">VS2012 ships with an enchanced Sql tools(SSDT) which brings in functionality from SSMS(SqlServerManagementStudio) to VS <\/font>      \n<ul><font size=\"2\"><\/font>        <\/p>\n<li><font size=\"2\">As part of this integration, you will see a new window called \u201cSQL Server Object Browser\u201d. In this window you can do advanced Database management stuff, such as running sprocs, managing Initial Catalogs etc. which were not possible in \u201cDatabase Explorer\u201d<\/font><\/li>\n<p>       <font size=\"2\"><\/font>        <\/p>\n<li><font size=\"2\">Some <\/font><a href=\"http:\/\/msdn.microsoft.com\/en-us\/data\/hh297028\"><font size=\"2\">videos<\/font><\/a><font size=\"2\"> to learn more about SSDT.<\/font><\/li>\n<p>       <font size=\"2\"><\/font><\/ul>\n<p>     <font size=\"2\"><\/font><\/li>\n<p>   <font size=\"2\"><\/font>    <\/p>\n<li><font size=\"2\">&quot;Sql Server Express&quot; is no longer installed with VS. Instead VS ships with &quot;Sql Server Express LocalDB&quot;. <\/font>      \n<ul><font size=\"2\"><\/font>        <\/p>\n<li><font size=\"2\">LocalDB development is supported in VS2012 for .NET v4.0\/v4.5 on Windows 8\/ Windows 7 \u2013 Client and Server SKUs<\/font><\/li>\n<p>       <font size=\"2\"><\/font>        <\/p>\n<li><font size=\"2\">LocalDB development is supported in VS2012 for .NET v3.5 on Windows 8 only(Client and Server SKUs)<\/font><\/li>\n<p>       <font size=\"2\"><\/font><\/ul>\n<p>     <font size=\"2\"><\/font><\/li>\n<p>   <font size=\"2\"><\/font>    <\/p>\n<li><font size=\"2\">Enhanced User experience while upgrading projects from VS2010 to VS2012 <\/font>      \n<ul><font size=\"2\"><\/font>        <\/p>\n<li><font size=\"2\">This user experience has some guidance which help you upgrade databases which were using SQLExpress to LocalDB<\/font><\/li>\n<p>       <font size=\"2\"><\/font><\/ul>\n<p>     <font size=\"2\"><\/font><\/li>\n<p>   <font size=\"2\"><\/font>    <\/p>\n<li><font size=\"2\">Auto loading of ConnectionStrings in ServerExplorer <\/font>      \n<ul><font size=\"2\"><\/font>        <\/p>\n<li><font size=\"2\">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<\/font><\/li>\n<p>       <font size=\"2\"><\/font><\/ul>\n<p>     <font size=\"2\"><\/font><\/li>\n<p>   <font size=\"2\"><\/font><\/ul>\n<p> <font size=\"2\"><\/font>  <\/p>\n<h5><font size=\"2\">Why LocalDB?<\/font><\/h5>\n<p> <font size=\"2\"><\/font>  <\/p>\n<p><font size=\"2\">The quick pitch for LocalDB is the following: \u201cIt is very easy to install and it requires no management\u201d LocalDB runs as your account and not as a system wide service(which is how SqlExpress runs). This <\/font><a href=\"http:\/\/blogs.msdn.com\/b\/sqlexpress\/archive\/2011\/07\/12\/introducing-localdb-a-better-sql-express.aspx\"><font size=\"2\">post<\/font><\/a><font size=\"2\"> from the SQL team goes in detail about the benefits<\/font><\/p>\n<p> <font size=\"2\"><\/font>  <\/p>\n<p><font size=\"2\">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&#160; almost the same connectionStrings as you had with SqlExpress, but instead change the DataSource from \u201c.\\SQLExpress\u201d to \u201c(LocalDb)\\v11.0\u201d and remove the UserInstance flag since LocalDb always runs as your account<\/font><\/p>\n<p> <font size=\"2\"><\/font>  <\/p>\n<h5><font size=\"2\">Initial Catalog vs AttachDbFileName<\/font><\/h5>\n<p> <font size=\"2\"><\/font>  <\/p>\n<p><font size=\"2\">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.<\/font><\/p>\n<p> <font size=\"2\"><\/font>  <\/p>\n<div class=\"csharpcode\">\n<pre class=\"alt\" style=\"width: 100%;height: 103px\"><p><span class=\"kwrd\">&lt;<\/span><span class=\"html\">add<\/span> <span class=\"attr\">name<\/span><span class=\"kwrd\">=&quot;DefaultConnection&quot;<\/span> <span class=\"attr\">connectionString<\/span><span class=\"kwrd\">=&quot;Data Source=(LocalDb)\\v11.0;<\/span><\/p><p><span class=\"kwrd\">Initial Catalog=aspnet-MvcApplication18-20121022222325;Integrated Security=SSPI;<\/span><\/p><p><span class=\"kwrd\">AttachDBFilename=|DataDirectory|\\aspnet-MvcApplication18-20121022222325.mdf&quot;<\/span> <\/p><p><span class=\"attr\">providerName<\/span><span class=\"kwrd\">=&quot;System.Data.SqlClient&quot;<\/span> <span class=\"kwrd\">\/&gt;<\/span><\/p><\/pre>\n<\/div>\n<pre><font size=\"2\"><\/font><\/pre>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\">Following is how these two values are used<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\"><strong>Initial Catalog<\/strong>: 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 \u201c(LocalDb)\\v11.0\u201d<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\"><strong>AttachDbFileName<\/strong>: This entry tells SQL the location of the mdf file which holds the database information.<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\">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<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<h5><font size=\"2\">One small caveat<\/font><\/h5>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\">By surfacing this information to the developer, a developer can run into the following situation. Let\u2019s 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.<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\"><em>\u201cCannot attach the file &#8216;c:\\users\\foo\\documents\\visual studio 2012\\Projects\\MvcApplication18\\MvcApplication18\\App_Data\\aspnet-MvcApplication18-20121022222325.mdf&#8217; as database &#8216;aspnet-MvcApplication18-20121022222325&#8217;.<\/em>\u201d<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\">To workaround this error do either of the following<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<ul><font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">Change the Initial Catalog and AttachDbFileName value to be something unique<\/font><\/li>\n<p>  <font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">Using SSDT, connect to \u201c(LocalDb)\\v11.0\u201d instance and delete the Initial Catalog entry specified in the database<\/font><\/li>\n<p>  <font size=\"2\"><\/font><\/ul>\n<p>The reason the AttachDbFileName entry exists so that the database file gets dropped in App_Data folder of your application.<\/p>\n<h5><\/h5>\n<p><font size=\"2\"><\/font><\/p>\n<h5><font size=\"2\">How to Videos for basic operations in VS tooling<\/font><\/h5>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\">&#160; <\/font><\/p>\n<div id=\"scid:5737277B-5D6D-4f48-ABFC-DD9C333F4C5D:37a993cf-e3ff-4b44-9dc5-c88f31ca91b9\" class=\"wlWriterEditableSmartContent\" style=\"margin: 0px;padding: 0px;float: none\">\n<div><\/div>\n<div style=\"width:448px;clear:both;font-size:.8em\">Working with Entity Framework Code First<\/div>\n<\/div>\n<p><font size=\"2\"><\/font><font size=\"2\"><\/font><\/p>\n<h5><font size=\"2\">Cheat sheet of things to remember\/do for Data in VS2012<\/font><\/h5>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\">These cheat sheet is useful when you are trying to figure out what instance to use for LocalDB<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n<ul><font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">Default Instance of Localdb in VS2012 is &quot;(LocalDb)\\v11.0&quot; <\/font>\n<ul><font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">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<\/font><\/li>\n<p>      <font size=\"2\"><\/font><\/ul>\n<p>    <font size=\"2\"><\/font><\/li>\n<p>  <font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">Tools-Options is &quot;(LocalDb)\\v11.0&quot; <\/font>\n<ul><font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">This means that any local database that you create will be created using &quot;(LocalDb)\\v11.0&quot; instance<\/font><\/li>\n<p>      <font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">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<\/font><\/li>\n<p>      <font size=\"2\"><\/font><\/ul>\n<p>    <font size=\"2\"><\/font><\/li>\n<p>  <font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">LocalDB does not run with UserInstance=true setting <\/font>\n<ul><font size=\"2\"><\/font><\/p>\n<li><font size=\"2\">You will get an error when you run your application<\/font><\/li>\n<p>      <font size=\"2\"><\/font><\/ul>\n<p>    <font size=\"2\"><\/font><\/li>\n<p>  <font size=\"2\"><\/font><\/ul>\n<p><font size=\"2\"><\/font><\/p>\n<h5><font size=\"2\">Helpful links\/scripts<\/font>&#160; <\/h5>\n<ul>\n<li><a href=\"http:\/\/blogs.msdn.com\/b\/pranav_rastogi\/archive\/2012\/02\/20\/mapping-sql-mdf-file-versions-to-sql-product-versions.aspx\"><font size=\"2\">Figuring out the version of your database file<\/font><\/a><\/li>\n<p>  <font size=\"2\"><\/font><\/p>\n<li><a href=\"http:\/\/blogs.msdn.com\/b\/pranav_rastogi\/archive\/2012\/10\/23\/common-commands-to-manage-a-sql-instance.aspx\"><font size=\"2\">Common commands to manage a SQL instance<\/font><\/a><\/li>\n<p>  <font size=\"2\"><\/font><\/ul>\n<p><font size=\"2\"><\/font><\/p>\n<p><font size=\"2\">I hope this would help you understand the changes in the Data Tooling experience for VS2012.<\/font><\/p>\n<p><font size=\"2\"><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":408,"featured_media":58792,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[],"class_list":["post-2044","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-aspnet"],"acf":[],"blog_post_summary":"<p>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 [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/2044","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/users\/408"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/comments?post=2044"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/2044\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media\/58792"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media?parent=2044"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/categories?post=2044"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/tags?post=2044"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}