Creating a Database Project
Database projects use the same project template system and “new project” process as all other Visual Studio project types. This means that we launch the creation process by selecting File, New and then selecting one of the templates located in the Other Languages/SQL Server category on the New Project dialog box (see Figure 13.21).
Note
If you are coming to Visual Studio 2015 as a prior user of Visual Studio 2010 or earlier, note the fundamental changes that have taken place with regard to the database tools. There is no longer a wizard that is launched when you create a new database project, and the number of project templates has shrunk to just the single SQL Server Database Project that comes in the currently shipping version of the SQL Server Data Tools.
Note
To be able to parse and validate the objects within a database project, Visual Studio needs to communicate with a local instance of SQL Server: this can be the Express Edition, Developer Edition, or Enterprise Edition of the particular database version you are targeting. If you do not have a local instance of SQL Server running, you see a dialog box at the start of the new project process prompting you to supply the path to a valid SQL Server local instance.
Importing a Database
After the project has been created, we are left with a rather sparse and empty solution tree (see Figure 13.22). We have two basic options at this point. We can create items within the project and configure the properties of the database by right-clicking the project name and running the Properties dialog, or we can import an existing database, thus pulling all of its attendant information into the project. Most database developers will want to build their initial project from an existing database. This preserves the concept of a production database being the “one version of the truth.” This is a recognition of the fact that we really want our test and development database environments to mirror the production environment in terms of structure. By reverse engineering a database into its component objects, Visual Studio enables us to create copies of a database, and that, in turn, enables developers to work in their own private sandboxes without worrying about affecting the production data store.
Let’s import a database. Right-click the project, select Import, and then select Database. This launches the Import window.
The Import tool (see Figure 13.23) captures the database connection to use (effectively answering the question, “Which database should be imported?”) and the various items to be imported.
At the bottom of the screen is a drop-down labeled Folder structure; the value set here dictates exactly how Visual Studio structures the project around the imported items. There are two approaches: organizing by object type and organizing by schema. For the object type approach, Visual Studio creates a schema objects folder with subfolders for your database objects such as tables and stored procedures. This is similar to the way that the Server Explorer represents a database in its tree view. The schema approach will group your project objects by the schema type that they belong to. There is also a hybrid approach, Schema/ObjectType, which will first organize by schema and then, within each schema folder, by object type. For most database implementations, the object type setting is the most useful. The exception is those cases where the database itself has multiple schemas. In those scenarios, the hybrid Schema/ObjectType option will likely be best. Because our sample AdventureWorks database contains multiple schemas, this is the option we will select.
Click the Start button to start the import. A Status dialog will detail the progress of the import (see Figure 13.24).
At its conclusion, project items (which correspond to all the database schema items) will now exist in the project (see Figure 13.25). Note that each object in the database (be it a table, a stored procedure, an index, a key, or a constraint) is represented by a single .sql file. In addition to the schema files, we have folders for holding data generation plans (more on these in a bit) and pre- and post-deployment scripts. Scripts placed into the pre- and post-folders are executed just before or immediately after deployment.
