Few simple questions on managing different types of databases in Visual Studio SQL manager

Question

Recently started with ASP.NET and MVC and have a few questions on working with databases.

• I have the following connection string:

     <add name="EFDbContext" connectionString="data source=.\SQLEXPRESS;InitialCatalog=EFDbContext;MultipleActiveResultSets=True;IntegratedSecurity=SSPI;" providerName="System.Data.SqlClient" />
  

  And a class (EFDbContextInitializer) that derives from another class that implements the IDatbaseInitializer interface which creates the database if database does not exist. However when it's created it works and all and the database is obviously created somewhere. However I cannot see it in my SQL manager in Visual Studio. The database is probably mounted with the built in ./SQLEXPESS so I figure I should be able to access it somehow when application is not running ? Where is the thing?

• Also I have another existing database an *.mdf type that I want to transfer a bunch of tables to from the above database that (EFDbContextInitializer) created. The tables I want to export are for the ASPNET membership provider implementation . I actually used the *.mdf database at first with my application, but then decided to exclude it from the project so that a new database could be created with the proper tables for the membership provider as I don't think it's possible or practical to try and add those tables to an existing database using the the (EFDbContextInitializer) way and now I don't want to recreate all the data in my *.mdf database that I excluded from the project or manually put anything into seed() method. Just want to use my *.mdf database again and add the membership provider tables to it that were created by the (EFDbContextInitializer) in that illusive database.

  What is the best tool or method to go about transferring those tables to the *.mdf database? If I gotta write lots of T-SQL queries please point to some examples. I probably don't know how to use it but I don't think there's anyway this can be done in the SQL manager in Visual Studio? I know there's compare database option where you can get the target to match the source, or just get certain tables but once again I don't know where to find that database that was created automatically. Also should I look into installing some SQL database management tool? I am using Windows 7 Pro so I take it I can't just install a full blown SQL server 2008 R2 suite and use the management studio there so what other tools are available to me for proper db management.

Answer 1

AD1. Sure you can connect to these DB's - just connect to .\SQLEXPRESS instance of you local MS SQL Express server using Windows Authentication. Optionally you can check if the database was created for you on the file system by going to :

C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

AD2. To add Application Services structures (tables,stored procedures, views) you can use aspnet_regsql.exe GUI tool if you are using asp.net 4.0 it's in

c:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe

This will recreate the structure for you. Othwerwise if you would like to keep using your .mdf database for Application Services puproses you only need to add connection string entry in your Web.config. Something like the below should work for you

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

Answer 2

1. You should be able to point SQL management studio to .\sqlexpress and see your database
2. To transfer data you can use the database Publishing wizard. Right click on your database in the server explorer view in visual studio and you should be able to publish to a provider which will generate your scripts etc for you.