Database Project in Visual Studio 2012 (SSDT)

Yesterday I was wondering, what is the best practice to manage database changes in TFS. In the past we have been using CVS to do version controls. The way to manage database changes was just having a folder to store all the scripts from the day the project starts. So when it comes to TFS, the same idea can be applied – create a folder of database scripts in the solution.

However, we can actually go one step further with the help of Database project in Visual Studio. It has been there forever but I never noticed it. The Database project stores all types of database objects that you can think of such as Table, View, Stored Procedures, you name it. The best part of it is that you can directly develop the database from inside of the database project in Visual Studio with the real-time compilation to check errors and have it deployed to your real database server from VS!

Here are the screenshots everyone wants:

Create a Database Project in VS2012. Notice that in 2012 they have get rid of “Server Project”. Part of that is because in VS 2012 you don’t need to have a Server project to create server level objects such as Login object. Server objects can be directly added to Database project in VS 2012 via Add -> New Item.. option. More about this can be found later in this post.

4-19-2013 10-43-04 AM

Now we have the project created.  We can start adding objects to the project.

4-19-2013 11-16-39 AM

If you have a database already. There are 2 ways that scripts of an existing database can be imported to the project:

1. Import.

2. Compare Schema and update your project with only the selected objects. (Source database is the existing database and Target database is the project.)

4-19-2013 10-49-09 AM

Here is how the Schema Compare looks:

4-19-2013 10-52-01 AM

Troubleshooting:

1. Unresolved reference

If you have objects from other database, right click on the project and select “Add Database Reference”, OR

Add the referenced objects to the project. Login object is an example. It would not be imported because it is at server level but you can manually create it.

4-19-2013 10-57-38 AM

2. All I see are errors.

Sometimes after importing a huge database developed outside of the VS, one may experience a lot of errors and warnings. A simple workaround is to not importing the database, and use the project to just manage new scripts. This is a more traditional approach to me.

There is just one thing to keep in mind. When adding new scripts, select Script (Not in Build). Otherwise it will check for errors in the scripts, and because the rest of the database objects are not imported, there will always be errors.

4-19-2013 11-05-12 AM

4-19-2013 10-57-38 AM

Run Dotfuscator CE in Visual Studio 2012 in Building

Unfortunately my approach is not perfect in that it still opens the GUI of Dotfuscator that requires a manual clicking. It is good for me though since I only run it during building in Release mode.

Here is how I run the command during building:

Find the location of Dotfuscator CE which is included in VS2012 by going to Tools -> External Tools.

Copy the path from the Command box.

3-29-2013 2-25-27 PM

Open the property of your project and go to Build Events.

3-29-2013 2-27-17 PM

Click on Edit Post-build and enter the following

if “$(ConfigurationName)” == “Release” (“%The path copied from the Command box%” /in:”$(TargetPath)” /out:”$(TargetDir)\Dotfuscator”
copy /Y “$(TargetDir)\Dotfuscator\$(TargetFileName)” “$(TargetPath)” )

What these lines do is

1. When the project is being built in Release mode, run the Dotfuscator.

2. Output the altered DLL into a folder named Dotfuscator under your target path (Normally the bin/release folder).

3. Copy this altered DLL to the folder where regular DLL resides and overwrite it.