Using LocalDB in Visual Studio 2010

  • Artigo

SSMS is a Success, But What About the VS?

In my recent post I was using .NET Framework 4.0.2 to enable SQL Server Management Studio to connect to LocalDB. It all worked rather well and I declared success and moved on to another topic. I conveniently kept silent about connecting to LocalDB from Visual Studio 2010. For a good reason -- currently Visual Studio 2010 doesn't really work with LocalDB. There are two reasons for that:

  • The SQL Server database experiences in Visual Studio 2010 are built on top of User Instances of SQL Server Express. Using LocalDB to power them is more complex than just changing a connection string to (localdb)\v11.0. It would require releasing some kind of a wide patch for Visual Studio 2010.
  • LocalDB was introduced in SQL Server 2012 CTP3. Visual Studio 2010 only supports SQL Server 2008 R2 and earlier.

In effect, even though adding the connection to LocalDB to Data Connections node of Server Explorer will succeed, trying to open any database designer from this connection will result in this sad message:

Is There At Least a Hack?

At this point the situation might seem pretty grim. Without Visual Studio 2010 support for LocalDB, developers that want to try it would need to wait for the next Visual Studio release. How can we get your feedback on LocalDB if there's no way to try it for months?

Fortunately, many Visual Studio 2010 features actually work with LocalDB and we should be able to use SQL Server Management Studio for the missing parts. We could also take the opportunity to try the new SQL Server Data Tools -- I will post about working with LocalDB from SSDT soon, as it integrates into Visual Studio, providing developers with a nice alternative for usig SSMS.

I already covered how to connect to LocalDB from SSMS and create databases, tables, input data and so on. Let's look at how we can use them from the Visual Studio 2010.

As I mentioned earlier creating a connection to LocalDB in the Data Connections node of Server Explorer will just work. Let's give it a try! One way to do it is to right-click the Data Connections node and select Add Connection...

We should use the standard LocalDB connection string: (localdb)\v11.0

Clicking OK and done, the connection is created!

Don't worry about the strange format of the connection -- we will soon see that the connection indeed points to our usual LocalDB instance.

The next step is to use the connection. Let's create a DataGridView control by dragging it from the Toolbox panel.

As we can see the DataGridView is not connected to any data source. Let's bind it to our LocalDB connection. Click Add Project Data Source... link on the Tasks quickview of the DataGridView control to bring up the data connection wizard and select Database and Dataset on the next two screens.

When asked about the connection, pick the LocalDB connection from the list. Once it's selected we can confirm that it points to LocalDB by looking at the Connection string property.

The last step is selecting a table to be presented in the DataGridView. After finishing the wizard the DataGridView should have the same columns as the underlying table, like this:

We're done. Hold your breath and hit F5 to run the application. With any luck it should start and the DataGridView should present the data from the table, like on this screenshot:

That was easy, wasn't it? :-)

Try It Now!

This walkthrough covers just one scenario, but the same approach should work in other scenarios as well. Just use SSMS or SSDT to create your database and Visual Studio to connect to it. Please try out your favorite scenario and share your feedback!

You may also want to see other posts and materials about LocalDB:

- Krzysztof Kozielczyk

Share Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!

Comments

  • Anonymous
    November 28, 2011
    Will LocalDB support Service Broker?

  • Anonymous
    December 15, 2011
    @nskerl@gmail.com LocalDB supports Service Broker today, but only for local queues. I cannot comment on the future, but remote Service Broker queues are not among frequently asked-for LocalDB features so far. The best way to request a SQL Server feature is filing the connect item - see the footer of all my posts for a link. -Krzysztof Kozielczyk, Program Manager for SQL Server Express

  • Anonymous
    February 01, 2012
    Does LocalDB connect to Visual Studio 2010 Express?

  • Anonymous
    February 02, 2012
    @Junior Cirqueira Yes, the procedure described in this post should apply to all editions of Visual Studio 2010, including Visual Studio 2010 Express. Correction: SSDT can install side-by-side with Visual Studio 2010 Express, but SQL Server Explorer will no appear inside it. You will have to start SSDT separately from Start menu. The connection to LocalDB can still be added to Data Connections node, but you will not be able to edit any database object from within Visual Studio 2010 Express window.

  • Anonymous
    July 08, 2012
    The comment has been removed

  • Anonymous
    July 08, 2012
    Thanks Steve. We are aware of the LocalDB issues with SSMS. We have resolved most of them in SQL Server 2012 Service Pack 1, which will be available later this year. As you have discovered already, there are workarounds for most of the problems. Ward.

  • Anonymous
    July 10, 2012
    QUESTION FOR BEGINNERS. I Have a SQL SERVER 2008 R2 On my system ( Win 7). Must I uninstall this version and then install SQL SERVER 2012 express  or do an upgrade ? I Cannot find this request ? Thank you    Anthride

  • Anonymous
    July 10, 2012
    Hi Anthride, For Express, you can either upgrade to 2012 or install it "side-by-side". In the latter case, you will need to choose a different instance name (for example, SQLEXPRESS2012). You can install LocalDB only side-by-side (it did not exist for 2008 R2). If you want SSMS and LocalDB together, you can install one of the "SQLManagementStudio" packages from www.microsoft.com/.../details.aspx. Make sure you install the x64 package if your system is 64-bit. Ward.

  • Anonymous
    October 17, 2012
    didn't work for me please tell me what i do please mail me at desaimandar2@gmail.com if possible

  • Anonymous
    November 04, 2012
    Are there any articles or references that discuss building an install (InstallShield or WiX) for apps using SQL LocalDb?  

  • Anonymous
    October 23, 2013
    I've done a small test program in Visual Studio 2008 (C#) that uses a LocalDB database. I defined and put some data into it all from inside Visual Studio, since I'm really unfamilar with SQL Server. Is there any way to also manipulate this database from MS Access? I've tried to do an External Data ODBC link from Access to the .mdf file, but with no luck.

  • Anonymous
    August 23, 2014
    Hello, doesn't work for me, i use visual web developer studio 2010 express, i can add a connection but not create table, whenever the issue is "this server version is not supported. Only servers up to Microsoft SQL Server 2008 are supported... I installed the SSMS with SQL Server 2012 and LocalDB. Could you help me please ?

  • Anonymous
    July 10, 2015
    can i connect visual studio 2010 with sql server 2014? when i am trying to connect through database its shows error 40( a network related or instance specific error occurred while establishing a connection to sql server.The server was not found or not accessible. verify that instance name is correct or sql server is configured to remote access. (provider:name pipes provider error:40 could not a connection to sql server).

  • Anonymous
    July 22, 2015
    I want to do this but when i create a DB through : ADD -> NEW ITEM -> Based-service database its not created and not show any error !!!! why?