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
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:
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.
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:
Step 5: Add a stored procedure named GetUsers which displays all the columns from the newly created table.
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 *.
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.
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:
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.
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Gouri is a Trainer and Consultant on Azure DevOps and Azure Development. She has an experience of three decades in software training and consulting. She is a graduate from Pune University and PGDCA from Pune University. Gouri is a Microsoft Most Valuable Professional (MVP) - Developer Technologies (Azure DevOps), Microsoft Certified Trainer (MCT) and a Microsoft Certified Azure DevOps Engineer Expert. She has conducted over 150 corporate trainings on various Microsoft technologies. She is a speaker with Pune User Group and has conducted sessions on Azure DevOps, SQL Server Business Intelligence and Mobile Application Development. Gouri has written more than 75 articles on Azure DevOps, TFS, SQL Server Business Intelligence and SQL Azure which are published on
www.sqlservercurry.com and
www.dotnetcurry.com. You can connect with her on
LinkedIn.