Mastering Visual Studio .NET 1002003 [Electronic resources] نسخه متنی

5.7 Database Projects

There is a fundamental difference between working with databases and
working with other software development artifacts in Visual Studio
.NET. With programs and components, source code is of central
importance. Although we must create DLL or EXE files in order for our
programs to run, these are usually never checked into revision
control systemsthey are essentially disposable because they
can always be re-created from the source code.

With databases, on the other hand, the model is different. The
closest thing we might have to source code is some SQL script that
creates a database with a particular schema. However, these are not
really at the center of the development modelthe database is
typically the authoritative source of information. SQL creation
scripts are often generated from the contents of the database, so
they cannot necessarily be described accurately as containing
"source" code. You can use a
Database project to hold scripts that contain the master definition
for the current database schema, but these scripts would not be run
as part of the normal build processyou don't
want to re-create your database from scratch every time you build
your project.

The role of a Visual Studio .NET Database project is therefore
somewhat different from that of most projectsit does not
contain the authoritative information required to build the target.
In fact, Database projects don't build any kind out
output at allthey just act as a container for scripts.
Moreover, you do not even need a project in order to use the visual
database design toolswhen you save any changes you have made
in these designers, Visual Studio .NET always writes changes out to
the database itself, so there is no need for a project file or source
files.

Database projects are useful when you employ the common development
practice of having separate database servers for development and
production. (And there may also be separate staging and test
servers.) Visual Studio .NET can create script files that capture any
changes made to a development server. These scripts can then be
applied to other servers later on in the development process to apply
the same modifications that you applied to the development server.

5.7.1 Creating a Database Project

Database
projects are created just like any other projects, using the New
Project dialog. (Open this with File New
Project (Ctrl-Shift-N).) The Database Projects
category can be found underneath the Other Projects category. The
Database Projects category contains only one kind of project:
Database Project.

When you create a new Database project, Visual Studio will ask you to
choose a database connection for the project, presenting the dialog
shown in Figure 5-26. This lets you select the
database for which you will be creating scripts. When you select a
connection, this creates a new database reference in the project.

Figure 5-26. Add Database Reference dialog

5.7.2 Connections and References

The list of named database
connections displayed in the Server Explorer provides a convenient
way of looking at particular data sourcesthey save you having
to navigate through the tree control the long way round. (They also
allow non-SQL Server data sources to be used.) However, these
connections are stored on a per-user basis, so although they are
convenient for interactive use, they are not much use for
representing connections in Database projectsproject files may
be opened by other users who do not have the same connections
configured on their system. Visual Studio .NET Database projects
therefore have a slightly different mechanism of their own called
database references.

Database projects usually contain at least one database reference.
(You can see one at the bottom of Figure 5-30.) A
database reference is very similar to a database connection, except
that all of the information is stored in the database project file
instead of in the user's Visual Studio .NET
settings. This means that anyone who opens the project file will be
able to connect to the database even if he didn't
have the relevant connection in his local configuration. (This is a
good reason to use integrated security with database connections. If
you use explicit credentials, they can get stored in the project as
part of the connection information, visible to anyone with access to
the project file. You might not want database credentials stored in
the clear like this. You can prevent passwords from being
storedthe Data Link Properties dialog shown in Figure 5-2 has a checkbox labeled Allow Saving Password.
If you remember to make sure this is unchecked, the password will not
be stored in the settings, and users will be prompted to type
credentials in when they try to use the connection. However, if you
use integrated security, anyone who opens the project will simply
connect with his own credentials automatically.)

When you open a Database project with database references in it,
Visual Studio .NET checks to see if the references match any of your
database connections. If the project contains a database reference
that is different from all of the connections listed in your Server
Explorer, it will automatically add a new connection with the same
settings as the reference.

When you create a new database project, Visual Studio .NET will ask
you which database you wish to connect to. If your system already has
an appropriate database connection configured, you can use that. A
reference will then be created in your project that has the same
settings as the chosen connection. Otherwise, choose the Add New
Reference... button to connect to some other data source. This will
open the Data Link Properties dialog, allowing you to configure the
connection. This is the same window that is used to add data
connections to the Server Explorer, as shown in Figure 5-2. This will add a new connection to your Server
Explorer and a new database reference to your project, both with the
same settings.

5.7.3 Scripts and Databases

Two kinds
of scriptscreate scripts and
change scriptscan be stored in a Database
project. Create scripts contain all of the information required to
create a new database from scratch, copying everything except the
table contents. Change scripts contain just modificationsthey
assume that the target database will have the same schema that the
development database did before the change was made.

The scripts generated by Visual Studio .NET 2002 and 2003 are designed to be applied to SQL Server databases. Although the visual database design tools work with other databases, database projects and the scripts they contain support only SQL Server.

5.7.3.1 Create scripts

Create scripts are not normally used on production servers. The only
time you would use a create script on a production server would be
the very first time the system goes live. Once a system is up and
running, you will never want to recreate the database from scratch,
because all of the data would be lost.

By default, create scripts check for the existence of items they are about to create and will DROP any existing items they find. You should therefore never run a create script on a live server. (It is possible to instruct Visual Studio .NET to omit the DROP statements, but it will still not be productive to run such a script against a live server.)

Create scripts are most likely to be useful in staging and test
environmentsthese systems don't have any real
live data. The ability to create a new, empty database instance with
exactly the right schema, views, and stored procedures can be very
useful in these environments. You can add a create script to a
Database project by using the Server Explorerthe context menu
will have a Generate Create Script... item on the appropriate nodes.
You can either generate scripts for individual objects, such as
tables and stored procedures, or Visual Studio .NET can make a set of
create scripts for the entire database.

You must install the SQL Server Client Tools in order for the Generate Create Script... option to work.

When you ask Visual Studio .NET to generate a create script, you will
see the dialog shown in Figure 5-27. This allows you
to control exactly which database items the script will create. The
available database objects are shown in the list on the left of the
dialog. Figure 5-27 shows the dialog as it appears
for the pubs sample database when generating a
create script for the entire database.

Figure 5-27. Generating a create script

If you open this dialog from the context menu of a specific database item instead of for the entire database, only the selected item will be available, and the checkboxes will all be disabled. However, you can click the Show All button to bring all the other objects back into the list.

By default, no objects will be added to the scriptthe list on
the right shows the items that will be added, and it is initially
empty. You can use the Add >> button to add individual items to
the list. However, you may find it less work to use the checkboxes
toward the top of the dialog. These let you include entire categories
of database objects in your create script. The Script All Objects
checkbox will cause every database object to be represented, but you
can also select just certain categories, such as tables or stored
procedures.

The Formatting tab of the Generate Create Scripts dialog, shown in
Figure 5-28, allows you to control certain aspects
of the SQL script that Visual Studio .NET will generate. You can
disable the creation of DROP statements here,
which makes the scripts potentially less destructive.

Figure 5-28. Create script code generation options

The "Generate scripts for all dependent objects" checkbox will cause Visual Studio .NET to
determine which other database objects your selected objects depend
upon and generate scripts to create those too. For example, if you
generate a create script for a view, selecting this checkbox will
generate create scripts for all of the tables the view uses.

The remaining checkboxes allow you to control whether comments
describing the file's purpose will be added to the
start of the scripts, whether SQL Server 2000 extended properties
will be copied across, and whether the script will be limited to
using only SQL Server 7 features.

Further options can be set with the Options tab shown in Figure 5-29. Despite being under the Security Scripting
Options category, the first option simply determines whether a
CREATE DATABASE statement will be created, along
with some associated configuration options. The next three determine
whether user role, login, and permission settings will be
transferred.

Figure 5-29. Create script options

The settings under the Table Scripting Options category control how
much information will be stored in the script for each table. By
default, all indexes, triggers, and keys will be created by the
generated script.

The File Options category allows you to choose the text encoding of
the script files, the default being Unicode. It also allows you to
choose between generating a single script file that creates
everything and splitting the scripts up so that each object has its
own script. Figure 5-30 shows a Database project for
a simple database for which the "Create one file per object" option was selected. (This is the default
option.) This particular database was fairly smallit contained
two tables, ContentText and
Transforms, and two stored procedures,
GetNewTransformAndContent and
GetNewTransformAndNewContent.

Figure 5-30. Create scripts in a Database project

Create scripts for stored procedures are fairly
straightforwardeach procedure is represented by a single file
with a .prc extension, containing a SQL
CREATE PROCEDURE statement. Tables are a little
more complex however. A table's SQL CREATE
TABLE statement is stored in the .tab
file. Indexing settings are stored in the .kci
file. The .fky files add foreign key
constraints, and the .ext files contain any
extended properties, such as column description strings.

The script files are just series of SQL statements. If you
double-click one, it will open in a Visual Studio .NET editor window.
You can execute the scripts using the Run or Run On... items from the
script's context menu in the Solution Explorer. (Run
On... lets you choose which database connection to use.) Remember
that, by default, a create script will drop any existing tables
before creating new ones, so don't do this on a
database if you care about its current contents. If you elected to
generate a create script for each object, you will need to be careful
about the order in which you execute these filesyou must
create the tables first, since the keys, indexes, extended
properties, and stored procedures all refer back to tables. If you
generated a single create script for the whole database, it will
create items in the correct order automatically.

Create scripts are useful for building a new database with the
required schema from scratch, but they are of no use for modifying an
existing database. As projects evolve, schema changes must be applied
to the database nondestructively. For certain kinds of changes, it
will be possible to use a create scriptadding a new table for
example. But for any change that modifies an existing object in the
schema, you will need to use a change script instead.

5.7.3.2 Change scripts

Every time you modify a database using
the visual database design tools described earlier in this chapter,
Visual Studio .NET is able to generate a script containing the
changes you made. The intended use of this feature is for you to
start from a position in which your development server and production
server both have the same schema. You will then make changes to the
development server, saving those changes in change scripts. When you
are ready to apply the changes to the production server, you can
simply run the change scripts on that server. It will then have the
same changes applied that you made to the development server.

Visual Studio .NET will offer to create change scripts only if you have a Database project open. If you don't have a Database project open, you will not be prevented from making changes to the database, but no script file will be generated. (This is often the most convenient way to prototype a designhaving Visual Studio .NET generate a change script for every little modification you make can be obtrusive in early experimental stages of development.) Because databases do not normally provide any way of retrieving a change log, you will not be able to recover this information later on, so be sure that you have a Database project open if you need change scripts. Alternatively, you can ask VS.NET to create a change script for youif you are editing the table, the Diagram Generate Change Script... item will create a change script even if you don't have a Database project open.

If you have a Database project open, you will be offered the chance
to create a change script each time you save your changes to the
database. For example, after adding a new column to a table and
creating a relationship between that column and one in another table,
selecting File Save causes the dialog shown in
Figure 5-31 to appear.

Figure 5-31. Saving a change script

This dialog allows a change script to be created and shows the SQL
that will be generated. This one contains an ALTER
TABLE statement to add the new column. If you click on the
Yes button, a normal Save File dialog will appear asking where you
want to save the file. (By default, it will suggest the
project's Change Scripts folder.)

The default name for the change script will usually be the name of the item being changed. So, in this example, Visual Studio .NET suggested Books.sql. If you make a series of changes to the same table, you will need to store each set of changes in a separate script file. (A series of changes cannot be merged automatically into a single file.) Fortunately, if you use the same filename every time you save a change, Visual Studio .NET will add a number to the end of the file in order not to overwrite previous changes. So, if you save four changes with the name Books.sql, they will be saved into Books.sql, Books1.sql, Books2.sql, and Books3.sql. (It does not indicate that it is going to do this in the Save dialogit always shows the name without the number.)

As with create scripts, change scripts just contain SQL statements.
And again, you can execute them with the Run or Run On... items from
their context menus in the Solution Explorer.

If you attempt to modify a database item that you do not have
permission to change, you will not be allowed to save your change
into the database. However, Visual Studio .NET will still be able to
save these changes into a change script, even if you are not allowed
to modify the database itself. You could use this feature to design
the changes you want using the visual database design tools, generate
a change script, and then submit that script to a DBA.

Unfortunately, you cannot open change scripts with the visual design
toolsyou will only be able to edit their text. For example,
suppose you used the table designer to modify a table and then saved
your changes to a change script but not the database. If you then
closed the table designer window, you would not be able to reopen the
table designer to show your modified view. When you open a table
designer window, it will always show what is currently in the
database and unfortunately cannot incorporate any work in progress
stored in a script file. When you open a script file, you always get
the SQL editor view.

5.7.4 Query Files

If
you have a Database project, you can write a standalone query that is
saved as a .dtq file. You edit these using the
query and view designer described earlier in this chapter. This
allows you to write a query in the designer and execute it without
having to store it in the database.