ASP.NET ConnectionString AttachDbFilename=|DataDirectory|

This is about ConnectionStrings / ASP.NET MVC with Visual Studio 2012 ultimate & SQL Server Express 2012.

Following up with this tutorial here: http://www.asp.net/mvc/tutorials/mvc-4/getting-started-with-aspnet-mvc4/intro-to-aspnet-mvc-4 I came across an issue with these two connection strings at my web.config:

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDb)v11.0;
                           Initial Catalog=aspnet-MvcMovie-users;
                           Integrated Security=SSPI;
                           AttachDBFilename=|DataDirectory|aspnet-MvcMovie-users.mdf"
         providerName="System.Data.SqlClient" />

    <add name="MovieDBContext" 
         connectionString="Data Source=(LocalDB)v11.0;
                           AttachDbFilename=|DataDirectory|Movies.mdf;
                           Integrated Security=True" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

the website works fine but I couldn’t fingure out why the first db is created in the App_Data folder while the second one is created in “C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESSMSSQLDATA”?! I supposed that both will be created in App_data because both utilize this attribute: AttachDBFilename=|DataDirectory|!

note: the tutorial mentions that it should be in the App_Data & they added a screenshot that shows it there indeed!

I have been looking for an answer and got into the complicity of SQL (I thought User Instances might be the solution) but couldn’t reach an answer for this : |

(this might be useful to read about User Instances http://msdn.microsoft.com/en-us/library/bb264564(v=sql.90).aspx)

Any ideas are greatly appreciated. Thanks in advance.

Regards

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

after research/tests it turned out to be as follows:

VS will look at the class name of the DataContext and will look to see if you have provided a connection string with the same name as the class name; for example:

public class MovieDataContext : DbContext

and

<connectionStrings><add name="MovieDataContext" ...

if it manages to find a matching connection string it will create the DB based on the criteria you specified in the respective data string (to add the DB to the App_Data set the path of the DB to |DataDirectory| as shown in both connection strings mentioned in the question); if the name doesn’t match or you didn’t provide any connection string, VS will fall back to the default settings and will create the DB in the default location/settings (usually C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESSMSSQLDATA).

note neither the “Integrated Security” settings nor the “Initial Catalog” play any role with this (I was able to create the DB in the App_Data with both Integrated Security = True & Integrated Security = SSPI and with/without Initial Catalog).

Hope this helps. Thanks for everyone that participated.

Method 2

I had the same issue. I believe the difference is in the Integrated Security setting. I have SQLExpress installed and found my Movies database in there using MS SQL Server Management Studio.

Check out this response for a better explanation. Difference between Integrated Security = True and Integrated Security = SSPI

Method 3

What AMT has given is exactly right. It was confusing as it is to use connection strings with .mdf and .sdf files.

I have another pointer for you though, you can change the default setting where the application looks for a connection string with the name matching the class name of the context class by overriding the constructor of DBContext and providing the paramter nameOrConnectionString as follows

public BlogsContext()
            : base("name=EFBlogs")
        {
        }

Application then searches for a connection string named EFBlogs, if it cannot find connection string then it creates the database with name EFBlogs, instead of BlogsContext

Method 4

Hi I notice a difference when you add a database and it asks do you want it to be placed in the app_data folder if you click yes then it goes to the app_data folder and the full path name of the mdf is also in the app_data folder whne you use file explorer.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0