Visual Studio 11 Beta Code-Named "Juneau" SQL Server Database Project - Quick Overview

Quick Overview of Code-Named "Juneau" SQL Server Database Project which is one of the most awesome features of Visual Studio 11 beta
7 April 2012
4 minutes read
MS SQL
6
 Visual Studio
11

Related Posts

3 minutes read
First Hours with Visual Studio Code on Mac and Windows
29 April 2015
.NET
75
 ASP.Net
101
 ASP.NET 5
16
 C#
36
 JavaScript
6
 Visual Studio
11
2 minutes read
Using "Ctrl+Enter, Enter" Instead of "Ctrl+., Enter" for C# Quick Actions on Visual Studio 2015 Preview
13 November 2014
Visual Studio
11
2 minutes read
Debugging ASP.NET vNext Beta Builds with Visual Studio 14 CTP 3
5 October 2014
.NET
75
 ASP.Net
101
 ASP.NET vNext
8
 Tips
14
 Visual Studio
11
4 minutes read
Replace the Default Server of OwinHost.exe with Nowin in Visual Studio 2013
27 September 2013
Katana
7
 OWIN
8
 Visual Studio
11
5 minutes read
Good Old F5 Experience With OwinHost.exe on Visual Studio 2013
9 September 2013
ASP.Net
101
 Katana
7
 OWIN
8
 Visual Studio
11
6 minutes read
Clean, Better, and Sexier Generic Repository Implementation for Entity Framework
10 January 2013
.NET
75
 ASP.NET MVC
42
 ASP.NET Web API
49
 DbContext
5
 Entity Framework
5
 MS SQL
6
4 minutes read
Working with IIS Express Self-signed Certificate, ASP.NET Web API and HttpClient
23 October 2012
ASP.Net
101
 ASP.NET Web API
49
 PowerShell
6
 Visual Studio
11
6 minutes read
Asynchronous Database Calls With Task-based Asynchronous Programming Model (TAP) in ASP.NET MVC 4
9 April 2012
ASP.NET MVC
42
 async
11
 C#
36
 MS SQL
6
 TPL
9
3 minutes read
Make The Visual Studio Extensions Compatible With Visual Studio 11 Developer Preview
7 December 2011
Tips
14
 Visual Studio
11
3 minutes read
Unit Testing With xUnit.net for ASP.NET MVC Web Applications - Part 1
16 October 2011
ASP.Net
101
 ASP.NET MVC
42
 Unit Testing
5
 Visual Studio
11
 xUnit
1
3 minutes read
How to Detect Errors of Our ASP.NET MVC Views on Compile Time - Blow up In My Face Theory
29 September 2011
ASP.Net
101
 ASP.NET MVC
42
 Tips
14
 Visual Studio
11
3 minutes read
Parent / Child View In a Single Table With GridView Control On ASP.NET Web Forms
28 April 2011
.NET
75
 ASP.Net
101
 C#
36
 MS SQL
6
2 minutes read
File By File Deployment Process with MSDeploy Inside Visual Studio
5 March 2011
.NET
75
 Deployment
6
 Tips
14
 Visual Studio
11
4 minutes read
SQL Injection vs. Lethal Injection / Protection Against SQL Injection
29 January 2011
.NET
75
 ASP.Net
101
 MS SQL
6
 Security
3
 SQL Injection
1
3 minutes read
Sending E-mail to All List of Membership Users with ASP.Net Using Built-in Membership API
26 November 2010
.NET
75
 ASP.Net
101
 C#
36
 eCommerce
1
 MS SQL
6

I think one of the most awesome features of Visual Studio 11 beta is SQL Server Database Project (code-named "Juneau"). It is simple, robust and makes it really easy for us to check the database code into our source control system.

I hear you yelling at me "Dude, ever heard about NoSQL? MongoDB? RavenDB?". Yeah, I am aware of those technologies and I think you can achieve lots of things that you can do with a relational database system with a NoSQL database system. On the other hand, there are quite a lot of other things that you cannot do with a NoSQL database system. At least, with the comfort of SQL. What? Did you say "Comfort? Who favors comfort over performance?"? I would like to remind you that all Stack Exchange family sites run on SQL Server.

To sum up, SQL Server will not go anywhere at all. It will always be around and we will keep using it. So, I‘d pay attention to this post Smile

This project template carries lots of features inside it and despite the fact that it is so easy to get started with it, you might drown inside its options very quickly. Honest to say, I am not so experienced with this feature but I try to use this on some personal projects for now as much as I can.

Let’s first have a look at Visual Studio 2010 because if you play with SQL Server project templates with VS 2010, you will be in panic because lots of them are now gone.

image

Ok, then. What do we do now? Well, it is simple: they are now all in one project template. From database projects to SQL Server CLR assembly projects, SQL Server Database Project template can do all of them (I have never tired creating a SQL Server CLR project with VS 11 Database Project template so far. So, I might not be entirely correct here).

Let’s come back to VS 11 beta. Creating a SQL Server Database Project is very straight forward. File > New > Project and navigate to SQL Server language tab or search against "SQL Server". SQL Server Database Project option will be available.

image

When we create the project, we get not many things out of the box which is nice.

image

From this point, you can either start a brand new project or import your existing database.

When you double-click on the Properties, we will get properties window which we can set lots of configuration about the database, build process and so on.

image

When you are working on a project, make sure you set the target properly as shown below.

image

While we are working on our database project, we will be creating files for Tables, User Defined Functions, Stored Procedures, etc. Don’t worry about where you put them. VS is smart about your files and it orders them properly on your deployment. This is the experience I have had so far.

As usual, we can add new items via Add New Item dialog box and Database Project ships with several item templates.

imageimageimage

User experience is also incredibly high. For example, you can create your tables through design surface or directly with T-SQL code. No matter which one you pick, VS will always keep those in sync.

As you can see below, we have a file named Products.sql for Products table.

image

Let’s create a foreign key constraint for CategoryId column by referencing a table which does not exist yet.

image

Save the file and build the solution. Now, we are getting errors since the Categories table does not exist.

image

When we add the necessary Categories table and its columns, the build process should run smoothly without any errors.

Let’s now open up the Products.sql table file again. Did you notice the right side of the design surface? Yes, it really knows what is going on outside of this file and it keeps the information in sync. As soon as you make a chance anywhere inside your project, proper places get updated.

image

You can also create script files which will be executed at particular point of your deployment. When you go Add New Item dialog box and navigate to User Scripts tab, you will see some options there. Let’s add Post-Deployment Script file to our project.

image

This file is nothing but a regular file except for the fact that its build action has been set to PostDeploy.

image

What about IntelliSense? Well, it is quite nice as you can see from the below screenshots.

I don’t know why but at some points, I was unable to kick off the intellisense, though.

imageimage

One more thing to mention in this post would be the deployment process but I thought it would be too much for a blog post. So, I will (hopefully) try to blog about that separately by demonstrating different scenarios.

I am very excited about this feature and I am sure you have something to benefit from it as well.

Useful References