Visual Studio 2012 MVC Local DB Add Table menu opt

Question

Usually connect to an external database but I wanted to fiddle with creating a project with a local database. I am using SQLServer Express Local Database, not CE.

I can add tables with EF but if I right click on Tables in Server Explorer the only options I have are Refresh and Properties, i.e. no Add Table menu option. Similarly, if I right click on one of the tables EF has created I also get only the Refresh and Properties menu options so I can't add rows, etc. Same problem with stored procedures; I can't add any (should I want to do some testing with EF and stored procedures) because I only have the Refresh and Properties menu options.

FOLLOW UP: I have isolated this problem to VS2012 Professional. When I use VS2010 it works exactly as expected: I can add tables and stored procedures. Following the same steps with VS2012 results in the situation where there is no menu option to add either tables or stored procedures. I produced these test results with SQLServer 2012 LocalDB.

Answer 1

If you only see Refresh and Properties when you right click on Tables in Server Explorer, you probably need to install SQL Server Data Tools from http://msdn.microsoft.com/en-us/data/hh297027 .

Answer 2

I had a similar problem only with Visual Studio 2012 Express for Web following along with the "Getting started with ASP.Net MVC3" at www.asp.net. The tutorial used a Sql Server Compact edition but I have SQL Express 2012 so I changed the connection string to have the data source point at my local instance, .\SQLExpress2012. The app worked fine and was able to read and write to the database. However I could not edit the database in the Visual Studio Express Database Explorer (all the context menu options were missing except "refresh" and "properties).

I found that changing the data source in the connection string to "Data Source=(LocalDB)\v11.0;" fixed the problem. I read somewhere that if you use LocalDb instead of the SQL Server instance name then it will be accessed under your user account - must have been some kind of permissions issue.

The whole (working) connnection string is: "Data Source=(LocalDB)\v11.0;AttachDBFilename=|DataDirectory|Movies.mdf;Integrated Security=True;"

Hope this saves someone a bunch of hours pulling their hair out, I'm almost bald now :)

Answer 3

I found that when installing Visual Studio 2013, I had not requested the 'SQL Server Data Tools' feature.

By re-running the installer from 'Control Panel' > 'Programs and Features', using the 'Modify' option, I could add the missing feature. On completion, the 'Add New Table' menu item was available.