SQL Advisor

Easier Database Development with Visual Studio 11 Beta

LocalDB is making life easier for database developers working in the Visual Studio 11 IDE.

• By David Ramel
• 05/08/2012

Having recently set up an old desktop as a dev machine in order to avoid trashing my main computer with evaluation software (don't get me started), I decided it was time to try out Visual Studio 11 Beta and its new features that promise to ease database development.

SQL Server Data Tools, released with SQL Server 2012 and coming with Visual Studio 11 Beta, are interesting and certainly worth a look soon, but I decided to first check out something a little simpler that also purportedly makes life easier for database developers in the IDE: LocalDB.

It's a new version of SQL Server Express with easy installation and use with no onerous configuration or management required by developers who just want to write code and create applications. While meant for development/testing, Microsoft said it can be used as a production embedded database, also.

You get it as an option when you download the free SQL Server 2012 Express.

Download and installation went fairly smoothly, so I started to tinker. Using LocalDB in SQL Server Management Studio (SSMS) was as simple as typing "(localdb)\v11.0" into the Connect to Server dialog box. After connecting, you can see you're using LocalDB in the Object Explorer, as shown in Fig 1.

Figure 1. Connecting to LocalDB in SQL Server Management Studio. (Click image to view larger version.)

You can do the same thing in Visual Studio 11 Beta from the new SQL Server Object Explorer -- described by Microsoft as designed to reflect the look, feel and functionality of SSMS -- by right-clicking the SQL Server node and choosing Add SQL Server, which brings up the same connection dialog box.

I decided to check out some MSDN Library instructional pages to see how to use LocalDB, keeping in mind that it's preview-only, subject-to-change documentation. The MSDN Library "Creating a SQL Server Express LocalDB Database" walkthrough seemed like a good place to start.

I completed the walkthrough successfully without major problems. It involves creating a Windows Forms project, adding a database and then creating tables for it. Be aware that in discussion of the Table Designer, the walkthrough has a note explaining that a new Table Designer is used in the SQL Server 2012 Release Candidate 0, while the walkthrough uses the "old version of Table Designer." The note contains a link to directions about how to use the new Table Designer, in the MSDN Library SQL Server Data Tools section. So, because the steps are similar but the table names and fields and other details aren't the same as in the walkthrough, I just had to go back and forth between pages to complete the tutorial.

The Table Designer is pretty cool, letting you create tables either in a graphical Columns Grid where you enter row information via the GUI fields for name, data type, nulls allowed and so on, or in a Script Pane where you write out the actual code, such as "CREATE TABLE [dbo].[Customers]." When using the latter, you can just click an Update button at the top of the designer to generate a script to update the database to reflect the changes in the graphical Columns Grid.

After creating the tables, constraints and foreign keys and clicking Update one last time, I populated the sample tables with data from the venerable Northwind database. This involved creating a connection to the Northwind database, showing table data for Customers and Products and simply copying and pasting the table data into the appropriate SampleDatabase.mdf tables I had created. Note, however, to get some older versions of the Northwind (or any) database to work with LocalDB, you need to upgrade it to work with SQL Server 2012. This is as simple as attaching to the database in SQL Server 2008 Management Studio and then detaching it.

I ended the walkthrough with a SampleDatabase.mdf database file that I used in a companion walkthrough, "Connecting to Data in a SQL Server Express LocalDB Database (Windows Forms)."

This walkthrough was easy. It involved connecting to the SampleDatabase.mdf database I just created via a Data Source Configuration Wizard. The wizard produced a SampleDatabaseDataSet that was added to my project, appearing in the Data Sources window. Then it was simply a matter of adding a control to the Form1 form by dragging a table from the dataset onto the form. That produced a DataGridView on the form that lets you see and edit table data upon running the application. Fig. 2 shows the app running.

Figure 2. The venerable Northwind database displayed in a Visual Studio Windows Forms app using LocalDB. (Click image to view larger version.)

I didn't follow the Next Step of adding validation functionality. I did, however, investigate some more MSDN Library instructional material in pages linked to from the walkthroughs.

I found in the MSDN Library | Visual Studio 11 Beta | Visual Database Tools section a lot of pages about the Database Diagram Designer. It's supposedly opened up via a Database Diagrams node under the database connection in Server Explorer. I couldn't get this to work.

Figs. 3 through 5 illustrate my experience with Database Diagrams in a few different configurations of Visual Studio and SQL Server. From left to right: Visual Studio 10 running with SQL Server 2008 on my regular work machine, where the Database Diagrams node shows up under a database in Server Explorer and they work; Visual Studio 10 running with SQL Server 2012 on my dev machine, where Database Diagrams show up, but don't work (error message); and Visual Studio 11 Beta on my dev machine, running with SQL Server 2012, where the Database Diagrams don't even show up, but every other node is the same. The aforementioned error message in VS10 said: "This server version is not supported. Only servers up to Microsoft SQL Server 2008 are supported."

Figure 3. Contrary to some MSDN Library preview documentation, there's no "Database Diagrams" node under Data Connections in Visual Studio 11 Beta ...

Figure 4. ...while it shows up and works in VS10 with SQL Server 2008 ...

Figure 5. ... and shows up but doesn't work in VS10 with SQL Server 2012.

I looked around and found a "bug" titled "Missing Database Diagram Node in Server Explorer" filed by a user who encountered the same issue. Microsoft replied, "This is a by design change in the product. Diagrams are no longer supported in the new version of the SQL database, so the node is removed when you work with a new SQL server." Well, that settled that question. The user who filed the "bug" reported Microsoft's response in a forum and said, "It appears that they intend for us to use SSMS to design databases from now on." I'm kind of curious about that, considering that one of the stated goals to improve database development in Visual Studio was to incorporate more functionality in the IDE so users don't have to keep switching back and forth between it and SSMS.

Anyway, just beware of the "Database Diagrams" information. I'm not complaining, because as noted earlier, this is clearly marked as preview documentation, subject to change. I'm just hoping this might clear things up for someone who follows my same path and looks for help on the Web.

There's much more to explore, but I've spent enough time on LocalDB and need to move on to further explore Data Tools and other things that enhance database development in the latest Visual Studio. Stay tuned.

What are your database development experiences in Visual Studio 11 Beta? We'd love for you to share. Please comment here or drop me a line.