Telerik OpenAccess ORM - Using SQL Server 2012 LocalDB

Microsoft SQL Server 2012 is certainly an interesting upgrade for the SQL Server, offering lots of improvements and new features. There are many things that might tempt you to migrate to 2012 or at least to start your new projects based on it, but to name a few:

• Enhancements for higher availability
• New and improved version of the SQL Server Management Studio
• Easier migration – the new Side-by-Side Migration option makes the migration from older versions of SQL Server safer and the rollback easier, which can be crucial. In the same time, you can do even an In Place migration (only if you are running 2005 or 2008 edition) or a full-blown data migration by configuring a new machine.

You will most probably want to take advantage of the new file-based version of SQL Express called LocalDB. It will be useful during the development phase of your application as you will not spend any time configuring the server - everything is configured out of the box. In the same time, as it is compatible with the SQL Server 2012 commercial editions, it will be easier to deploy your application to production servers. That means the ORM product you choose has to fully support both versions of the database, and to allow you to easily connect to and use LocalDB.

Let’s get our hands dirty and try all this with OpenAccess ORM - it fully supports LocalDB and SQL Server 2012 commercial editions. We can use the SQL Server Data Tools (SSDT) for creating and defining a LocalDB database, and then generate an OpenAccess Domain Model out of it using the simple Update from Database wizard. This component is available out of the box for Visual Studio 11 Beta, so we will use VS 11 to evaluate what is being offered. Those of you who would like to stick to VS 2010 can download and install .NET Framework 4.0.2 and optionally - SSDT (you can also use the old Server Explorer, it's totally fine).

Note that the Database First approach is the choice in this case since we would like to really familiarize with the database – for the scenarios where you don’t care about it so much, you can just create an empty Domain Model, add your entities and concentrate on the business logic rather than the database specifics.

1. Create a LocalDB database

First, you have to create the .mdf file which will represent your database. In order to add one to your project, choose a Service-based Database in the Add New Item dialog (under Data category):

After you click Add, a wizard will automatically offer you to create some model or a dataset matching the empty database. Cancel the wizard and we will get to creating the model later. 

2. Access the database and define the schema

For database schema manipulations we will use the SQL Server Object Explorer - a shiny new addition to the Visual Studio:

Initially it will not have any servers presented, but if you click on Add New Server a familiar window pops up asking for connection details. Our problem now is how to specify the LocalDB here - we only know the file name and that's it!? Not so obvious, but after a short search it appeared that for getting started you can try writing "(localdb)\v11.0" as a server name and use Windows Authentication. As it turns out, while you can create Named Instances of LocalDB dedicated to one application only, an Automatic (public) one is available and it's named after the LocalDB version, in this case - v11.0.

After we add the Automatic instance, our file is shown as a database under it in the SQL Server Object Explorer. It's full path is used as a database name by default, so we have renamed it to something more readable:

The UI will somehow remind us for the SQL Server Management Studio, but it certainly has some new things to get used to. Adding a table is easy:

Just click Update, and after some additional dialogs showing you a preview of the database changes and asking you to confirm them (nice!), the table is added.

3. Auto-Generate the OpenAccess Domain Model

Now we can start the OpenAccess Add Domain Model wizard:

If you are not familiar how to work with the wizard, you can check out the dedicated Getting Started with Database First video available now in Telerik TV and you should be up to speed! Just select Microsoft SQL Server as a backend type and the connection to the LocalDB file we have added earlier should be available automatically. Just in case you need a connection string anyway, it should be something like:

Data Source=(LocalDB)\v11.0;AttachDbFilename="[path]\LocalDBTest.mdf";Integrated Security=True

Now we are ready - you have your Domain Model in the OpenAccess Visual Designer:

I hope this quick walkthrough has shown how easy is it is to enjoy the latest technologies with OpenAccess ORM. Stay tuned as Q2 2012 is approaching and we are implementing some long awaited features for it!