Database projects provide another method of manipulating databases. With the database project, you can create scripts and queries to execute on an SQL Server or MSDE database. You can create schema change scripts, or SQL queries. You can also use source code control to manage the scripts. The database project provides similar functionality to the Query Analyzer that comes with the full version of SQL Server. The handy part of using a database project is that now you can include it in your solution and track the various stored procedures and queries that are used by the solution.
VB 6—VB 6 also had a database project. However that type of project was very different. The VB 6 database project was a template for creating a project with a DataEnviroment designer and a DataReport designer precreated. Since VB .NET supports neither of these tools, its database project is used to manage SQL queries and scripts.
You create a database project the same way you create any other new VB project. You can also add one to an existing solution. Select New Project from the File menu, or if you are adding it to an existing solution select Add Project/New Project. When the new project dialog opens, find the template called Database Project. You should find it under the Other Projects node of the tree control (Figure 3.22).
Figure 3.22. Solution Explorer view of a database project.
There are two subfolders in the project for storing change scripts and another for queries. It is very important to make the distinction between these scripts and stored procedures. The scripts are saved as disk files and are external to any database. Stored procedures are compiled and saved inside the SQL Server database. When a script is run or debugged, it uses the database connection defined in the project as the default database, but the scripts can be run on any SQL Server database.
The database project gives you a way of organizing and distributing SQL Server scripts. It also allows them to be included in version control. This is actually a real benefit. If you have ever been involved in a project that has multiple distributed databases and must manage changes between them, you will appreciate the ability to organize your scripts.
When creating a script, there are several templates you can choose from. This is a time saver in that you do not have to create the script from scratch. It also provides a nice header block you can (and should) fill in for documentation purposes.
You can run your scripts directly in the IDE. If you run a script that outputs data, it will appear in the Output window. This is a great way of validating your output before you use the script in a program.