Export (0) Print
Expand All

Adding New Objects to Data-tier Applications

Visual Studio 2010
Important note Important

To create and manage databases using the latest version of Data Tier Applications (DACPAC), install the most recent release of SQL Server Data Tools from Get Started with Microsoft SQL Server Data Tools.

After creating a data-tier application (DAC) project, you add the various data-tier objects referenced by your application, such as adding tables, views, and stored procedures. You can add the objects individually, or you can import multiple objects from an existing DAC package, and existing database, or a database script. You can also add data generation plans, and deployment scripts.

While the most commonly used SQL Server database objects are supported in a DAC, others are not. For more information about a list of the objects that can be defined in a DAC, see Features Supported in Data-tier Applications.

You can use either the Solution Explorer or the Schema View to view the existing objects already in the DAC project.

The Schema View displays a hierarchy that includes all of the schema objects in the DAC, such as tables and views. For an individual object, Schema View displays a hierarchy of the elements contained in the object. For example, the node for a table has subnodes for elements such as columns, keys, and indexes.

The schema objects are also displayed in the Solution Explorer hierarchy, but Solution Explorer also includes other types of objects such as project properties, data generation plans, and scripts. Solution Explorer does not display the structure of the objects, it instead lists the .sql file that has the Transact-SQL script for creating the object.

There are two ways to add individual objects to a DAC project, from the Project menu or the Schema View hierarchy.

From the Project menu, select Add New Item… to open the Add New Item dialog box. In the dialog box, select the template for type of object you want to add. In the Name box at the bottom of the dialog box, edit the name of the script file to match the name you want to assign to the object, and then click the Add button.

In the Schema View, right-click the node for the type of object, select Add, then select the type of object. For example, to add a table, right click the Tables node, select Add, and then select Table…. The Add New Item dialog box is displayed with the type of object selected. In the Name box at the bottom of the dialog box, fill in the name for your object, then click the Add button.

In both methods, when you click the Add button, the Add New Item dialog creates a Transact-SQL file that is populated with a template that contains a simple Transact-SQL CREATE statement for that object type. You then edit the CREATE statement to specify the object definition. For example, if you add a table and specify MyTable as the name, the Add New Item dialog box creates a MyTable.table.sql file and opens that in a Transact-SQL Editor window. You then edit the CREATE TABLE statement to add definitions of elements such as columns and constraints.

If you are creating a DAC project to support a new development project for an existing application, you can initialize the DAC by importing the definitions of the existing set of data-tier objects used by the application.

You can import a DAC package; all of the objects defined in the DAC package are added to your DAC project. You typically use this if you have an existing database. You can connect to the database from the DAC project and import any objects supported in a DAC. You can also run the Extract Data-tier Application Wizard in SQL Server Management Studio to extract a DAC package from the database. The extracted DAC package contains definitions of all the objects in the database, which can then be imported to your DAC project. For more information, see How to: Import Data-tier Application Packages.

If you have a script that can be used to create a database, you can import objects from that script. For more information, see How to: Import Database Scripts to Data-tier Applications.

Data generation plans allow you to generate meaningful sets of test data. DAC projecs support the same types of data generation plans as database projects. For more information about data generation plans, see Generating Test Data for Databases by Using Data Generators.

DAC projects support pre-deployment, post-deployment, and management scripts similar to those supported by database projects. A DAC project can have multiple scripts in each category.

Open your solution in Solution Explorer, expand the DAC project node, and then expand the Scripts node.

To add post-deployment actions, right-click the Script.PostDeployment.sql node and select Open. The Script.PostDeployment.sql file is opened in a Transact-SQL editor window where you can add any Transact-SQL statements that should be run after the deployment completes.

To specify pre-deployment actions, perform the same steps on the Script.PostDeployment.sql node.

To add a management script, right click the Scripts node, select Add, and then select New item. In the New item dialog box, select the Script entry, and then select the Add button. This opens a Transact-SQL Editor window with a script template.

For more information about project scripts, see Creating and Modifying Database Scripts.

Community Additions

ADD
Show:
© 2014 Microsoft