How to get an ASP.NET MVC 3.0 site to use a db other than the default SQLEXPRESS

Question

essentially what I'm trying to do is get my head around Microsoft's ASP.NET MVC 3.0. I'm using Visual Studio 2010 Ultimate. I followed the video tutorial at http://www.asp.net/mvc/videos/5-minute-introduction-to-aspnet-mvc which got me up and running and as a result have created a website which accesses a local database.

Question 1:

Does anyone know where that local database is? I believe it's using a default SQLEXPRESS db somewhere. It doesn't show up in Server Explorer, it's not in the App_Data folder. I cant find it in any folder anywhere. It was - as I understand it - generated automatically using a model from the project and nowhere does it tell me where it's put it.

Question 2:

What I'd really like to do is put the project online to play about with, and I've done that already by way of AppHarbor.com. They let you create a MS SQL Server database and give you a replacement connection string to bung in Web.config, but having replaced the connection string and rebuilt the solution - even after adding the AppHarbor database (I'm pretty sure pointlessly) to Server Explorer, my site still uses the elusive SQLEXPRESS database which I cant find.

The change I made in Web.config was from:

  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

To the format

<connectionStrings>
    <add name="TestDB"
         connectionString="Server=SERVER;Database=DBNAME;User ID=USERID;Password=DBPASSWORD;" />         
</connectionStrings>

Question one would be nice to know, but it's question 2 that's really bothering me. What reference to the database have I missed that is causing it to use a database that I don't want it to and know nothing about? I figure that there must be one, not least because when I remove the connection string completely from Web.config the thing still runs along fine.

Any help would be greatly appreciated.

Answer 1

So I finally worked out what the problem was - aided in no small part by everyone's help here. So thank you. There were two main things I had wrong.

Firstly, turns out that the name of the connection string is important. I needed to name my connection string after the context I'd set up in MVC. So my "TestDB" was wrong, and instead for the video tutorial I linked to in my question the connection string should be called "SportsContext".

So in the end, my connection string entry for Web.Config looked like this:

<connectionStrings>
<add name="SportsContext" connectionString="Server=SERVER;Database=DBNAME; User ID=USERID;Password=PASSWORD" providerName="System.Data.SqlClient" />
</connectionStrings>

Which interestingly enough is not what they tell you that you should use on appharbor. Secondly, and another key point I was missing, while MVC will happily build your databases for you in SQLEXPRESS, it won't do the same for SQL Server. And when building them yourself in SQL Server you have to be sure to get everything in correctly, including auto-incrementing ID columns and foreign keys etc.

Those two changes were all it took get my site up and working.

Answer 2

Question 1:

Type services.msc in your Windows->Start->Run box and ensure that the SQL EXPRESS service is started.

In your Server Explorer in VS: Connect to Database, choose Sql Server, enter this as your server: .\SQLEXPRESS You should then be able to test your connection.

Question 2: HMMM i can reproduce your same issue by just attempting to change from SQLEXPRESS to my default SQL instance via web.config . EF keeps creating the DB in SQLEXPRESS. Interesting but i don't have a clue.

Answer 3

You need to first run the C:\WINDOWS\Microsoft.NET\Framework\\aspnet_regsql.exe

program and point it to your remote (or local) database

Here is a link outlining this - http://msdn.microsoft.com/en-us/library/x28wfk74.aspx

then just change the web config entry to point to the new database.

You need to use

<add name="ApplicationServices" connectionString="DataSource=YourRemoteServerName;Initial
Catalog=YourDBName; Persist Security Info=True; UserID=YourUName;Password=YourPassword providerName="System.Data.SqlClient"
/>

Answer 4

I had this same problem when I tried to deploy with appharbor. Look in your project directory - you probably actually have THREE web.config files - a web.config, web.config.debug and web.config.release. Visual Studio 2010 uses transformations based on your project build state to decide what transformations to apply. Check out the transformation tester from appharbor to get this figured out.

Also be careful because if you have the wrong settings in an Entity Framework project, you may be deleting the database on appharbor every time you make changes to your model. And the account that you have been given the credentials to does not have permission to re-create that database.

If you are using the built-in aspnet user models, make sure you set them up with the appharbor database instead of your local sqlexpress. Here is a How-To on that.

Answer 5

A couple things to look for...

1. In the <ConnectionStrings> element, you may try clearing any existing connection strings, by way of <clear />, before defining any other connection strings. This will clear any inherited connection strings that may be causing the problem.
2. Since you are using MVC3, I am going to assume that you are using Entity Framework? Ensure that either your DbContext (ef 4.1) or any other Context object is being properly configured to use "TestDB".

As for where the SQLExpress db is located, from the original connection string, it appears it is looking at your local SQLExpress server.