第 156 页

Framework itself is able to raise some diagnostic events.

You can find a list of these in the IntelliTrace options.

The other type of information, that IntelliTrace

can track is method calls along with parameter

information. To use this information, you need to turn

it on before starting the debugging session (Tools .

fiGure 55-7

Options . IntelliTrace). Once Call Information has

been activated, you can switch the IntelliTrace window over to the Show Calls View

(Figure 55-7), which shows each method call entry and exit along with a sublist of

events that occurred during their execution.

When you are in an IntelliTrace session with Call Information enabled, a new border

is added to the editor window, which contains IntelliTrace navigational markers

(Figure 55-8). You can use these to navigate the IntelliSense information from inside

the editor. This border is a light grey during normal debugging, but turns a darker fiGure 55-8

grey when IntelliSense is activated.

IntelliTrace files (.tdlog files) are stored in a default location on your hard drive and can be

archived and re-opened later. When you open a .tdlog file you see the IntelliTrace Summary view.

By double-clicking a thread or an exception, you can open the IntelliTrace session at the appropriate

point and begin debugging again.

By default, IntelliTrace files are stored in the C:\ProgramData\Microsoft

Visual Studio\10.0\TraceDebugging folder. You can always check where

the files are stored from Tools . Options . IntelliTrace . Advanced.

1086 .

chaPter 55 ViSuAl STudio ulTimATe For deVeloperS

database tools

Most applications require some kind of database to store data when it is not being used. Visual

Studio 2010 Premium provides all of the capabilities that were previously introduced with the

Database edition of VSTS and adds tools that were previously only available as Power Tools.

sql-clr database Project

In the New Project dialog inside the Database node are two nodes for C# and VB SQL-CLR

Database projects. These are used to create managed classes to use inside a SQL Server. When

you create a new project of this type, you will be prompted for a connection and to enable CLR

debugging. Use CLR debugging only on development database servers and isolated from other

developers, as explained later in the “Best Practices” section.

You can add to this project some types like Aggregates, User-Defined Functions, Stored

Procedures, Triggers, User-Defined Types, and Classes. You can think of this as a normal VB/

C# project; you can add classes, references, and even web references. You can create unit tests

for your methods as explained in Chapter 11, refactor your code, and build, in the same way

you would for other library projects. However, the debugging history is a bit different, because

your code is running in an SQL Server context. First you will need to allow VS to remote debug

the code. This is needed only the first time you debug your project. However, the point of entry

for your code will be through a script that will use any of your managed objects. As you may

have noticed when you created the project, there’s already a file named Test.sql, which is used

to create and test your types.

The test scripts can grow quickly, and long script files are not easy to maintain. You can add new

test scripts and right-click to set them as Default Debug Script. This is similar to the way you would

set an ASP.NET page as the start page. You can set a breakpoint on the script or the classes you

are testing and hit F5 (or from the menu Debug . Start Debugging). This will build the project,

drop the assembly from SQL Server in case it was previously deployed by Visual Studio, and then

register the assembly again with SQL Server using the Create Assembly procedure. Finally, it will

attach to the SqlServr.exe process to allow you to debug scripts and managed code. You can also

create Database Unit Tests that use your SQL objects (including SQL-CLR types) as explained in the

section on Database Unit Tests in Chapter 56.

offline database schema

There’s another type of Database Project and it’s inside the Microsoft SQL Server node, as shown

in Figure 55-9. As you can see, there are three options each for SQL Server 2005 and SQL Server

2008, but it’s basically the same project type; the difference between them is the use of a wizard

and the SQL Server version being targeted. Some options are available only from the creation of the

project using the wizard, so I suggest you start using that. These projects will let you have an offline

representation of your DB schema, so that you can version it along with your code. It will create a

file for each schema object and deploy the changes to a database.

Database Tools .

1087

fiGure 55-9

If you run the wizard for SQL Server 2008,

the first option will let you choose whether

your project will manage a single database or

a SQL Server. This is the same as choosing

either Database Project or Server Project in

Figure 55-9. The other option on this page is

to organize the files by schema or object type

(tables, views, stored procedures, and so on).

This is important because you can’t change this

option once the project is created, although you

will find yourself working on the Schema View

most of the time instead of doing it directly on

the files — from that window you can select or

deselect the filter by Schema View. However, if

you select “No” to organizing by schema inside

the types, you can still distinguish the schema

because of the prefix added to each filename.

On the other hand, if you select to organize

by schema, inside of each you will have folders

for each type. Figure 55-10 shows at the left

the Schema View filtered by schema. You could

change this option to organize all the objects by

type. The Solution Explorer (at the right) shows

two different projects where files are physically organized differently.

The database options are the next step of the wizard; if you don’t know your defaults now, don’t

be afraid to choose something and change it once you get this information from either your DBA

fiGure 55-10

1088 .

chaPter 55 ViSuAl STudio ulTimATe For deVeloperS

or your production DB. You can select to import this configuration directly from a DB. If you can

connect to your production DB, it’s a good idea to import them at this moment. To do this, simply

go to the next step in the wizard and mark the needed checkboxes as shown in Figure 55-11.

The last step is to configure the build and deployment options. You will need more deployment

configurations, so here you will only select your development database. Keep in mind this doesn’t have

to be the same as in the previous step — here you are configuring your development database and in

the previous step, if available, you would import existing objects and configuration from a production

DB. You see the three configurations needed in more detail later, so for now you can go with the

default options. The final step will take a while, especially if you have many objects to import.

fiGure 55-11

Once the wizard finishes, you can add, edit, or delete schema objects right from the Solution

Explorer or the Schema View (View . Schema View) — these will be represented as files. When

you build the project, a .sql file containing all the DML necessary to re-create your schema will be

generated and evaluated. The script can then be deployed to SQL Server. You could also choose to

deploy on every build, so your DB will always get your last changes.

When working with Source Control, this type of project becomes really useful. The scenario would be

that each developer works on his own server instance. One of them has made changes to the schema

and his .NET code and deployed them to his instance. He then tests the .NET application and it

works fine with the new database changes. Then he checks in all of his changes to the server and

another developer retrieves the changes. When the second developer deploys his database instance it

Database Tools .

1089

is also updated with the latest changes, and he will be running the new code changes along with the

new schema changes applied to his SQL Server instance.

data Generation

In addition to versioning the schema, you might want a way to generate data for testing purposes.

To do this, you need to add a Data Generation Plan. If you right-click the Offline Schema project

under the Add menu, you will find the option for Data Generation Plan. By default, all the tables on

the schema will be selected, but you can override these settings. In the top section of Figure 55-12,

only the Products table is checked. The ProductModel and ProductCategory tables are automatically

added (darkened checkboxes) because of the foreign key between Products and those tables.

Keep in mind these generation plans are only for development and testing

purposes. Don’t run them on production servers.

fiGure 55-12

In the plan, you can select the ratio between Related Columns; for example, in Figure 55-12 we are

specifying to have 10 products for each subcategory (10:1).

1090 .

chaPter 55 ViSuAl STudio ulTimATe For deVeloperS

In the Column Details window, all columns are selected except for the ProductID, which is an

identity column. You can deselect columns as long as they have either a Default value or allow nulls.

In the properties window you can specify the percentage of nulls to be generated.

For each column, you can also specify the type of generator to use. By default a generator will be

selected depending on the data type, or in case a column is a foreign key, the values will come from

the related table’s primary keys. You can use other generators. In the example in Figure 55-12, for the

Name on the Products table we used a regular expression to mix first names and last names. Another

option would be to use a Data Bound Generator. In that case, you would need to specify a connection

string and a select query to get the values to insert. You could also create a Custom Generator and

implement your own logic simply by implementing a base class and registering your assembly as a

Customer Generator. For more information see “Creating Custom Generators” on MSDN.

To run the Data Generation Plan, simply select Generate Data from the Data . Data Generator

menu. Remember to run this plan only on a development or testing database.

database refactoring

Chapter 8 introduced the topic of refactoring for normal .NET code. For databases you have only

three refactoring tasks: Rename, Move Schema, and Fully-Qualify Names. You can rename tables,

columns, stored procedures, constraints, and indexes, as well as other database objects. You can also

rename the references to other servers or databases if a cross-database reference is used in the scripts.

To rename an object from the Schema View, right-click it and select Rename from the Refactor

submenu. When renaming an object, you will see a preview of the changes as shown in Figure 55-13.

In the upper pane you will see the old version, and in the lower pane you will see the new one for all

the dependencies.

fiGure 55-13

Database Tools .

1091

Renaming is easily done on an offline schema version, but for tables and columns it can be hard

to deploy to the database. Because the old object will be dropped and re-created, this can result in

data loss. If you have a Data Generation Plan, you can regenerate the data after the changes are

deployed, but if you need to preserve your data you should either modify the deployment script or

apply the refactoring manually. See “Protecting Data during a Renaming Operation” on MSDN for

more information.

schema compare

This tool enables you to compare schemas between databases or Database Projects. To use it, select

New Comparison from the Data . Schema Compare menu. You will have to select a project or

database as Source and a project or database as Target. When you do that, you will be presented

with a window similar to the one shown in Figure 55-14. The lower pane will show both versions

of the selected object with the changes highlighted. It’s handy to use the filters from the toolbar; by

default all objects will be shown. You should select Non Skip Objects to see only the differences.

fiGure 55-14

For each DB object, you can see the action that will be applied. Then from the toolbar you can either

select Write Updates to apply all the changes or Export to Editor to get the SQL statements used for

the update in case you need to do manual changes or hand it out to your DBA.

1092 .

chaPter 55 ViSuAl STudio ulTimATe For deVeloperS

To customize the options for Schema Comparisons, go to Tools . Options and to the Schema

Compare node under Database Tools. By default, Block Schema Updates if Data Loss Might Occur

is selected; this is recommended but can cause some updates to fail. You can uncheck this option,

but be sure you’re running on a test database and that you can regenerate the data. Other options

such as Ignore White Space can be useful to reduce unnecessary changes.

Remember that if you are using a Database Project, the deploy option will write the changes from

your Database Project to the database selected in the build option in the project’s properties. This

tool can be useful to see the changes or do manual tweaking.

data compare

This tool is useful for copying data from one database to another. Go to Data . Data Compare .

New Data Comparison to start the wizard. You will need to select the Source and Target database

and the records to compare. Depending on what you want to do, you can choose between the

different Compare options, “Different Records” for updates, “Only in Source” for inserts, “Only

in Target” for deletes, and Identical Records just as a reference. Finally, you can select which tables

and columns to compare.

You will be presented with all the objects selected in the last step of the wizard, but only the objects

with differences will be selected. I recommend filtering the view to show only the Selected Records.