SQL Server 2012 Denali LocalDB and Data Abstract for .NET

With all this the-last-week-before-the-release fuss I’ve completely missed the RC0 release of the MS SQL Server 2012 “Denali”.
Among many other features (probably Microsoft itself is better to explain about them), there is new SQL Express version called LocalDB or, as they call it, SQL Express for developers.

So let’s try to connect to a LocalDB-hosted database from a Data Abstract for .NET application.

Setting up the server

Installing LocalDB is quite straightforward – just download the installer and run it.

But there is a small problem – SQL Server Management Studio is not included in the standalone LocalDB installer, probably to minimize the installer size.
So I had to download the entire Denali RC0 installation DVD and install SSMS from there.

[![Installing Denali SSMS](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-Install-SSMS.png "Installing Denali SSMS")](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-Install-SSMS.png) It is possible to manage LocalDB using Visual Studio, but for me SSMS gives much more flexibility (and I am used to its interface).

Creating a new database

We’ll create a PCTrade database because we already have the database creation script for it and there are server and client applications that use the full MS SQL Server-based PCTrade database.

The database creation process is very similar to the ‘real’ MS SQL Server:

Connect to the SQL Server using (localdb)\v11.0 as server name

[![Connecting to LocalDB instance](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-Connect.png "Connecting to LocalDB instance")](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-Connect.png) 2. Create a new database named PCTrade

It seems that Management Studio in RC0 still has a bug. If you just enter a database name in the ‘New Database’ dialog and press enter the process will fail:

[![Creating new database](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-New-Database.png "Creating new database")](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-New-Database.png) You have to explicitly provide file names for the .mdf and .ldf files in the ‘Database files’ list.

Create the database structure and populate the tables with data

The database creation script is shipped with the Sample Server (on Windows7 it can be found in the folder c:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\Server\Scripts ).

There are no errors when it is executed against the LocalDB instance.
The only change that is needed is to remove the first SQL lines where the database itself is created, because we have already created the PCTrade database in the previous step.

Changes in the Data Abstract for .NET application

In the following chapter, the Sample Server application shipped with Data Abstract for .NET will be modified.

Now we need to adjust the Sample Server connection definitions file.

Open the c:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\Server\DASampleServer.Wpf.2010.sln solution.

Double-click the daSchema file in the Solution Explorer.
Once the Schema Modeler is be started, you’ll need to change the PCTrade.MSSQL connection definition. Set its ConnectionString to

MSSQL.NET?Server=(localdb)\v11.0;Database=PCTrade;Integrated Security=SSPI;

[![Editing connection in SchemaModeler](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-SchemaModeler.png "Editing connection in SchemaModeler")](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-SchemaModeler.png) And this is the place where things stop to go smoothly. The problem is that Schema Modeler internally uses the .NET Framework 2.0-based assembly to access ADO.NET while LocalDB support was introduced only in .NET Framework 4.0.2 (LocalDB support is introduced by this .NET Framework update: [http://support.microsoft.com/kb/2544514](http://support.microsoft.com/kb/2544514)).

This means that so far Schema Modeler cannot access LocalDB data in any way (sure we will fix this by the time Denali is out ;).

But this doesn’t mean that the Sample Server application will also fail. All you need to do is set both Sample Server’s projects to use .NET Framework 4.0 instead of 3.5.

Now compile and run the server.

Et voilà! The server is fully accessible and provides data served by the LocalDB instance.

[![Sample Server accessing LocalDB database](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-SampleServer.png "Sample Server")](http://blogs.remobjects.com/wp-content/uploads/2011/12/LocalDB-SampleServer.png) ## Conclusion

As you see it is very easy to add support of this cutting-edge database server to a Data Abstract-based application without writing a single line of code.