Data Tier Applications in Visual Studio 2010 – Pre/Post Deployment scripts

Data Tier Applications in VS 2010 seem to have taken on some nice features as you use it with SQL Server 2008 R2.  One thing that I like is the ability to do quite a bit inside of Visual Studio rather than in SQL Server Management Studio (SSMS).  You can do all of your development inside of VS 2010 – even easier than in VS 2008 since there was no Data-Tier Application project as there is now.  So, what you can do is create all of your objects inside of VS 2010 as a part of an application, and then bundle-up all of the objects and deploy to a database server from that wacked-out .dacpac extension that they’ve created.  Very nice!  Has me thinking…I wonder if someday all dev work, whether it be c, c#, vb.net, tsql, etc. will be based out of one tool?  Seems they’re going that direction, and have finally gotten close.  Let’s take a look:

VS 2008

VS 2010

I read somewhere that you could add data for, say, lookup values, but I forgot to bookmark it, so I wasn’t sure what the correct method for doing this.  I checked Books Online and didn’t find it readily; what I did find, however, was the Pre-Deployment and Post-Deployment folders in the project.  This is super-handy for adding just what I need – a place to automatically add domain, or lookup, or static, values to my database tables where necessary.  Check out these screenshots. Note that I’ve added a script to my Post-Deployment folder – it looks by default in the project folder in the Script.PostDeployment.sql file, in our case, since we want to do a post deploy operation by inserting my values, so we add our script file (StaticValues.sql) in the same location as well:

My script for this demo has something super simple. 

/*
Scipt: StaticValues.sql
Reason: Lookup Values using Post-Deployment
Database: DAC
Date: 12/3/2010
 
*/
USE DAC
GO
INSERT INTO [DAC].[dbo].[Table1]
           ([column_1]
           ,[column_2])
     VALUES
           (1
           ,100)
GO

INSERT INTO [DAC].[dbo].[Table1]
           ([column_1]
           ,[column_2])
     VALUES
           (2
           ,200)
GO

INSERT INTO [DAC].[dbo].[Table1]
           ([column_1]
           ,[column_2])
     VALUES
           (3
           ,300)
GO

When I build the project, I get the following:

------ Build started: Project: DAC, Configuration: Debug Any CPU ------
  DAC -> C:\Documents and Settings\Lee\my documents\visual studio 2010\Projects\DAC\DAC\sql\debug\DAC.dacpac
------ Deploy started: Project: DAC, Configuration: Debug Any CPU ------
Pre-Deploy: Starting script execution…
    :
(1 row(s) affected)

    :
(1 row(s) affected)

    :
(1 row(s) affected)

Pre-Deploy: Finished script execution.
Validating upgrade
Preparing DAC metadata in the SQL Server instance 'S03777\SQL2K8'
Preparing deployment script
Creating database 'DAC_1_0_0_0__129358633652284695'
Creating schema objects in database 'DAC_1_0_0_0__129358633652284695'
Registering the DAC in DAC metadata.
Setting database 'DAC' to read-only mode
Disconnecting users from database 'DAC'
Preparing scripts to copy data from database 'DAC'  to database 'DAC_1_0_0_0__129358633652284695'
Disabling constraints on database 'DAC_1_0_0_0__129358633652284695' before populating data
Inserting data from database 'DAC' to database 'DAC_1_0_0_0__129358633652284695'
Enabling constraints on database 'DAC_1_0_0_0__129358633652284695'
Setting database 'DAC' to read-write
Renaming database 'DAC' to 'DAC_1_0_0_0__129358633654000310'
Renaming database 'DAC_1_0_0_0__129358633652284695' to 'DAC'
Upgrading DAC metadata to reflect the new DAC version
Previous copy of database DAC is available as DAC_1_0_0_0__129358633654000310.
Post-Deploy: Starting script execution…
Post-Deploy: Finished script execution.
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Thanks for reading,

Lee

--------------------------------

“Deck the harrs with boughs of horry, fa ra ra ra ra, ra ra ra ra” 

http://msdn.microsoft.com/en-us/library/ee210546.aspx