VS2010 and local databases

tags:

visual-studio-2010

views:

256

answers:

VS2010 and local databases

Is it possible to connect to a local database (in the app_data folder) using the Data -> Transact SQL Editor in Visual Studio 2010?

When I launch the Transact SQL Editor from VS2010 I get the "Microsoft SQL Server 2008 RC" connect to server dialog. The options I have for Server type are "Database Engine" and "SQL Server Compact"

What you can do is:

CTRL + ALT + S (show up Server Explorer)
Add Connection.
Click "Change" right from Data Source and select Microsoft SQL Server Data File

Now youshould be able to select any SQL Server file now for your connection.

Click on Data -> Transact SQL Editor > New Connection. SQL Server Connection Dialog will appear.
In the list, choose SQL Server Compact and browse for SDF-file.
Click Connect and you should be connected now.

Sander Pham 2010-06-18 00:37:36

Thank you for your reply. However I already have a connection to the database using that method in the Server Explorer.With the connection in the Server Explorer opened, when I click on the "Data -> Transact SQL Editor", Visual Studio does not use the active connection in the Server Explorer - it prompts you to connect to a server using the "Connect to Server" dialog. At this point what should I do to connect to my local database?

Darren 2010-06-18 01:00:20

Check my edited post. It should work fine in that way

Sander Pham 2010-06-18 01:16:23

I still can't get it to work - where is the SDL file supposed to be?Does creating a MSQL Data file connection create an SDF file somewhere? If so, where does it put it?The database I am using is a SQL2008\Express database - not a Compact database.If on the last step you described above I choose SQL Server Compact and on the "Select SQL Server Compact Database File" dialog I switch from "SQL Server Compact Database" to "All Files" and browse to the .mdf file it does not work...

Darren 2010-06-18 02:01:00

You can have the SDF wherever you want in your project. You create it by clicking right-mouse on your project and click on Add > New Item, select "Local Database" and click Add.

Sander Pham 2010-06-18 02:06:50

You were asking for a "local database". When you add a "local database" to your project, it will create a SDF-database.

Sander Pham 2010-06-18 02:10:13

Sorry - I didn't realize the term "Local Database" was normally reserved for talking about SQL Server Compact databases.I don't have the "Local Database" option when I try to add a new item in VS2010.I just want to be able to run long Transact SQL statments against an SQL Express database that is located in the app_data directory without leaving VS2010. I can work easily with an sql express on the server but I just want to put it into my app_data directory so it is more portable.

Darren 2010-06-18 03:44:16

I was able to connect to a local database by using the AttachDbFilename connection string option (as it is done e.g. in Web.config).

In "Connect to Database Engine" dialog, click "Options >>", go to "Additional Connection Parameters" tab and enter something like the following:

Data Source=.\SQLEXPRESS;AttachDbFilename=<your_path_to_app>\App_Data\YourDatabase.mdf;Integrated Security=True;User Instance=true;Initial Catalog=YourDatabase

The only problem with this method is that the options are not saved in the dialog, and the connection string has to be entered with every connection.

Sergii Volchkov 2010-07-30 09:37:35

ansaurus

tags:

views:

answers:

VS2010 and local databases

related questions