DotNetCurry Logo

Database Development with Visual Studio 2010: Overview of SQL Server Data-Tier Application (DACPAC)

Posted by: Gouri Sohoni , on 7/22/2010, in Category Visual Studio, VSTS & TFS
Views: 76153
Abstract: With Visual Studio 2010, a new project type named` SQL server Data-Tier application’ is introduced also called as DAC application. In this article, we will discuss how a DAC application can be created, database related objects can be added to it, how static code analysis can be run against it and finally how the deployment condition can be provided.
With Visual Studio 2010 a new project type named` SQL server Data-Tier application’ is introduced also called as ‘DAC application’. This new project type is for use with SQL Server 2008 R2. It creates special unit of deployment called DACPAC (DAC package). DAC stands for Data tier applications. The ‘dacpac’ can be handed over to DBA by a developer who will later take care of deploying the application. DAC application is targeted towards small applications (departmental applications). It contains all the database and instance objects used by the application.
Note: the ‘dacpac’ file contains schema but no data, so the file size is considerably small.
Important: Data –Tier applications can only be deployed on a computer where SQL Server 2008 R2 is installed and can be created with Visual Studio 2010.
Data-Tier Application Project
With DAC application, we get T-SQL editor, Intellisense available in IDE and Static code analysis features. We have another project type called Database Projects (for various versions of SQL Server like 2005 or 2008). Database projects are mainly used for mission critical, business central applications. DAC application is normally used for departmental applications (where we may not have a special DBA allocated for it). It does not support all object types from SQL Server like FileStream.
DAC can be developed with Visual Studio 2010. All the objects required for the database can be defined. We do not have any wizard for creating DAC project. Following are the steps for creating a DAC application.
Step 1: Start Visual Studio 2010 and select New from File menu.
Step 2: Expand Database, Select SQL Server and then select ‘SQL Server Data-tier Application’ as shown below:
NewProject
Enter name as ‘Demo DAC’, keep default project location and clear the check box for `Add to source control’.
Step 3: From Solution Explorer, right click on project and select Properties. You can see default properties as shown below.
DemoDAC
Step 4: Right click on Solution Explorer and add a table to this project, give a name as ‘UserNames’ and other fields as shown below:
CreateTable
Step 5: Add a stored procedure named GetUsers which displays all the columns from the newly created table.
CreateProcedure

You can observe that we get Intellisense information in the editor in offline mode.
Step 6: We will run the static code analysis for the build and see how we get a warning and later an error. From Solution Explorer, select properties for project and select the tab for Code Analysis. Click the check box for `Enable Code Analysis on Build’ and then Run Code Analysis. You will observe we get a warning message because of the usage of `*’ in the statement. If we double click on the warning, we are taken to the code itself where we get the help when we hover mouse pointer on *.
CreateProcedure_1
Let us change this warning to error by selecting the tab of Code Analysis and click on Treat `Warning as Error’. Run the Code Analysis again and now we get an error instead.
ProjectSettings
Step 7: Change the code to remove this error

SELECT UserName from UserNames
Step 8: Before we deploy this DAC, select properties by right clicking the project in Solution Explorer and select Deploy tab. Click on Edit button for Destination Connection string and create a connection to SQL Server 2008 R2 instance.
Step 9: Let us also add a constraint so that the deployment is successful only when the particular version of SQL Server is available as follows. Open the file `ServerSelection.sqlpolicy’ which is in the properties for the current project and do as shown below:
EditValues
Step 10: We are now ready to build and deploy the DAC. After successful deployment we can see a database created named ‘Demo DAC’ in SQL Server 2008 R2.
Conclusion

In this article we discussed how a DAC application can be created, database related objects can be added to it, how static code analysis can be run against it and finally how the deployment condition can be provided.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Gouri Sohoni is a Trainer and Consultant for over two decades. She specializes in Visual Studio - Application Lifecycle Management (ALM) and Team Foundation Server (TFS). She is a Microsoft MVP in VS ALM, MCSD (VS ALM) and has conducted several corporate trainings and consulting assignments. She has also created various products that extend the capability of Team Foundation Server.


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by george on Tuesday, March 12, 2013 1:17 PM
thank you :) !!
Comment posted by adeel on Friday, September 13, 2013 11:31 PM
plzz mail me full touterial along with snap shot im thank full to you