Using LocalDB in Visual Studio 2010
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:
- Introducing LocalDB and LocalDB Q&A
- Upgrading .NET Framework 4 to support LocalDB connections and using SQL Server Management Studio to work with LocalDB
- Using LocalDB in Visual Studio 2010
- LocalDB RC0 and the New LocalDB Installer
- Where are LocalDB database files located?
- SQL Server 2012 Express LocalDB documentation [preview]
- Krzysztof Kozielczyk