Database Development with Visual Studio 2010: Overview of SQL Server Data-Tier Application (DACPAC)
Posted by: Gouri Sohoni
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.
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.