# Friday, 06 October 2017

The following articles walk the user through creating an ASP.NET application; storing source control history in a Visual Studio Team Services project Git repository; and automatically deploying to an Azure web app whenever code changes are pushed to the repository.

Creating a Team Project in Azure

Creating an ASP.NET Web Project and Storing code in a VSTS Git Repository

Configuring Automatic Deployment from VSTS to an Azure Web App

ALM | Visual Studio | VSTS | Web
Friday, 06 October 2017 10:23:00 (GMT Daylight Time, UTC+01:00)
# Thursday, 05 October 2017

This is Part 3 in a series of articles about integrating VSTS, Azure Web Apps, ASP.NET applications, and Visual Studio.

In previous articles, I showed you how to create a VSTS Team Account and Team Project linked to Azure  and how to create an ASP.NET Web application and link this to your VSTS team project in order to store the project code in the Team Version Control Repository.

In this article, I will show you how to automatically deploy code from your VSTS Team Project repository to an Azure Web App every time you push code changes to that repository.

This configuration is done in the Azure portal.

Log into your Azure account and navigate to the portal by clicking the PORTAL link at the top right, as shown in Fig. 1.

VSTSp3-01-AzurePortalLink
Fig. 1

From the Azure portal, select

New | Web + Mobile | Web App

as shown in Fig. 2.

VSTSp3-02-NewWebApp
Fig. 2

The Create Web App blade displays, as shown in Fig. 3.

VSTSp3-03-CreateWebAppBlade
Fig. 3

At the App Name field, enter a name for your app. This must be unique among Azure App Services. Although you can assign a custom domain to this later, the default URL will be

http://xxxx.azurewebsites.net

where xxxx is the name you assigned to this Web App.

If you navigate to this URL (or click the hyperlink in the Overview tab), you will see a default Azure We App page.

At the Resource Group field, select "Create New" and enter a name for your resource group.

At the "OS" field, select the operating system on which you want to deploy your Web App.

Click the [Create] button to create your Web App. This usually takes less than a minute. A message displays when the App is created with a [Go to Resource] button to quickly open your Web App. (Note: You can also find your Web App by selecting "All Resources" in the left sidebar.)

The Web App Management blade displays, as shown in Fig. 4.

VSTSp3-04-WebAppManagment
Fig. 4

Select "Deployment Option" on the left menu of this blade to display the Deployment Option blade, as shown in Fig. 5.

VSTSp3-05-DeploymentOption
Fig. 5

Click "Choose Source" to display a list of Source repository types, as shown in Fig. 6.

VSTSp3-06-ChooseSource
Fig. 6

Select "Visual Studio Team Services".

You will return to the "Deployment Option" blade, but options specific to VSTS will now display, as shown in Fig. 7.

VSTSp3-07-DeploymentOptionVSTS
Fig. 7

Select your VSTS Account and Project from the dropdown. (Note: Your account and project will only appear if you have linked your VSTS project to Azure, as described here.)

Click the [OK] button to configure this automation.

It should take less than a minute to complete.

Any code currently in the project's repository will be deployed to Azure. You can see this by once again navigating to the Web App's URL (http://xxxx.azurewebsites.net).

Future code code pushed to the Team repository will also be deployed automatically to Azure.

In this article, you learned how to set up automatic deployment to an Azure Web App each time code is pushed to a VSTS project code repository.

ALM | Visual Studio | VSTS | Web
Thursday, 05 October 2017 10:48:00 (GMT Daylight Time, UTC+01:00)
# Wednesday, 04 October 2017

This is Part 2 in a series of articles about integrating VSTS, Azure Web Apps, ASP.NET applications, and Visual Studio.

In my last post, I showed you how to create a new Team Account and Team project linked to Azure.

In this article, I will show how to open this new project in Visual Studio, start working with it, and check your code into VSTS Git repository.

To view your account and project in VSTS, open a new browser tab and navigate to https://xxxx.visualstudio.com/_admin

where xxxx is the name of the account you just created. You can see my account displayed in Fig. 1.

VSTSp2-01VisualStudioDotCom
Fig. 1

Click on the name of your project to display details about it, as shown in Fig. 2.

VSTSp2-02VstsProject
Fig. 2

Click the Code link at the top menu. If you have already checked in code, you will see that code in your Version Control Repository. New projects will look like my project, shown in Fig. 3.

VSTSp2-03VstsCode
Fig. 3

Click the [Clone in Visual Studio button]. This launch Visual Studio (You will probably be prompted to switch apps)

In Visual Studio, you should see the Team Explorer, as shown in Fig. 4. If you don't see it, you can find it by selecting View | Team Explorer from the menu.

VSTSp2-04VisualStudioTeamExplorer
Fig. 4

Click "Clone this repository" to display the Clone Repository dialog, as shown in Fig. 5.

VSTSp2-05VisualStudioTeamExplorerClone
Fig. 5

You have the opportunity to change where this new local repository will be saved on your hard drive. Whether you change it or not, you should note the location.

Click the [Clone] button. This will initialize a local GIT repository that points to your VSTS Team project.

You can now create a new Web App project in Visual Studio.

Select File | New | Project from the menu.     The "New Project" dialog displays, as shown in Fig. 6.

VSTSp2-06VisualStudioNewProject
Fig. 6

Under the "Templates" tree on the left, expand Visual C#; then select "Web". Select "ASP.NET Web Application" or "ASP.NET Core Web Application" as your template.

The location of your project is determined by the combination of the "Location" textbox and the "Solution name" textbox (assuming you have not unchecked "Create new directory for solution"). Verify that this points to the location of the local Git repository that was created above.

Uncheck the "Create new Git repository" checkbox.

Make any other desired changes and click the [OK] button to create a new button.

At the New ASP.NET Application dialog (Fig. 7), click [OK] to create the project. 

VSTSp2-07VisualStudioNewASPNetProject
Fig. 7

A new project will be created in the repository directory similar to the one shown in the Solution Explorer (View | Solution Explorer) in Fig. 8.

VSTSp2-08VisualStudioSolutionExplorer
Fig. 8

You can check this initial code into your version control repository from the Visual Studio Team Explorer (View | Team Explorer). Click the [Changes] button in the Team Explorer to display the Changes panel, as shown in Fig. 9.

VSTSp2-09Changes
Fig. 9

Enter a comment, such as “Initial commit” and click the [Commit All] button. This will commit your changes to the local Git Repository.

To push your changes up to the VSTS repository, you will need to access the Team Explorer Sync panel. You can navigate to this panel by clicking to dropdown near the top of the Changes panel and selecting “Sync” from the menu displayed, as shown in Fig. 10.

VSTSp2-10TeamExplorerDropdown
Fig. 10

The Sync panel displays, as shown in Fig. 11.

VSTSp2-11SyncPanel
Fig. 11

Click the “Sync” link. You should see a message indicating that the local and remote repositories are syncing, meaning that the local repository is merging any changes from the remote repository and your local commits are being pushed up to the remote repository in VSTS. The following message displays when this sync is complete:

Successfully synchronized incoming and outgoing commits.

If you return to the project code page in VSTS and refresh the web page, you should see all your code checked in. Fig. 12 shows my project after I checked in my initial commit.

VSTSp2-12VSTSCode
Fig. 12

In this article, I showed you how to create a new ASP.NET project in Visual Studio and store the code in an existing Visual Studio Team Systems Git repository.

Wednesday, 04 October 2017 11:00:00 (GMT Daylight Time, UTC+01:00)
# Tuesday, 03 October 2017

This is Part 1 in a series of articles about integrating VSTS, Azure Web Apps, ASP.NET applications, and Visual Studio.

If you want to publish code from your repository to Azure, the easiest way to do this is to create the account from within the Azure portal.

Log into your Azure account and navigate to the portal by clicking the PORTAL link at the top right, as shown in Fig. 1.

VSTSp1-01-AzurePortalLink
Fig. 1

From the Azure portal, select

New | Developer Tools | Team project

as shown in Fig. 2.

VSTSp1-02AzureNewTeamProject-01
Fig. 2

The "New Team Project" blade displays, as shown in Fig. 3

VSTSp1-03NewTeamProject-1
Fig. 3

Enter a Name to identify this team.

Click "Configure required settings" under "Account"; then, click "Create a new account" and enter a name for your VSTS Account (Fig. 4). This name must be unique among all VSTS account, because it will be used in a URL to connect to the account.

VSTSp1-04NewTeamProject-2
Fig. 4

Click [OK] to close the "New Account" blade.

Review all the settings in the New Project dialog. You may wish to change the location, so that it is closer to your team members. (For me, this defaulted to Brazil, so I changed it to Central US). My completed blade is shown in Fig. 5.

VSTSp1-05NewTeamProject-3
Fig. 5

Click [Create] to begin creating the Team Project.

It may take a minute or two to create the VSTS Account and a project within that account.

To view the account and project in VSTS, open a new browser tab and navigate to https://xxxx.visualstudio.com/_admin

where xxxx is the name of the account you just created. You can see my account displayed in Fig. 6.

VSTSp1-06VSTSAccountAndProject
Fig. 6

In this article, I showed how to create a team project in VSTS and link it to Azure. In the next article, I will show how to create an ASP.NET application in Visual Studio and push that application’s code into this team project’s code repository.

ALM | VSTS
Tuesday, 03 October 2017 11:08:00 (GMT Daylight Time, UTC+01:00)
# Wednesday, 05 May 2010

Microsoft Product Unit Manager Cameron Skinner came to the midwest to show off the Architecture features of Visual Studio 2010. He began his tour in the Detroit area, speaking at local companies in the afternoon and at the Great Lakes Area .Net User Group (GANG) in the evening. I recorded two of his presentations, which are available here.

Here is the presentation at GANG

Part 1:

Part 2:

Part 3:

Part 4:

Part 5:

Here is the presentation at a Detroit-area company.

Part 1:

Part 2:

Part 3:

Part 4:

Wednesday, 05 May 2010 03:04:35 (GMT Daylight Time, UTC+01:00)
# Thursday, 23 April 2009

Episode 17

Microsoft Visual Studio Team System 2010 is currently in CTP3.

In this interview, Microsoft Technology Specialist Randy Pagels describes the new features of the upcoming release of this product.

Randy maintains a great deal of information on VSTS at TeamSystemCafe.net

11 mins, 52 secs

Thursday, 23 April 2009 12:25:58 (GMT Daylight Time, UTC+01:00)
# Thursday, 26 March 2009

Episode 16

Microsoft Technology Specialist Randy Pagels describes the benefits of Microsoft Visual Studio Team System.  You can learn more about VSTS from Randy at http://www.teamsystemcafe.net/

4 mins, 18 secs

Thursday, 26 March 2009 10:32:10 (GMT Standard Time, UTC+00:00)
# Saturday, 16 August 2008
# Friday, 15 August 2008

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases. 

In this article, we will discuss how to migrate data from one database to another.   Data Dude provides the Data Compare tool for this purpose.

In order to use the Data Compare tool, the following conditions must be true

1.       Data exists in a source table.  You want to migrate that data to a table of the same name in a different database.

2.       Both tables must have the same structure.

3.       Both tables must have a primary key to uniquely identify rows.

Follow the steps below to migrate data with the Data Compare tool.

1.       Launch Visual Studio 2008

2.       Select Data | Data Compare | New Data Compare.  The New Data Comparison dialog displays

  Figure 1

a.       When migrating data from a table in one database to another, the database you intend to update is known as the “Target Database”.  The other database is known as the “Source Database”.  In the New Data Comparison dialog, select the Source Database and Target Database connections.  If you have not created a Visual Studio connection to these databases in Visual Studio, you can click the New Connection button to create them.

b.      The New Data Comparison dialog contains checkboxes that allow you to specify which rows you want to see and compare.  I don’t usually change these (they are all checked by default) but it may speed up the process to clear the Identical Records checkbox.

c.       Click the Next button to advance to the second screen of the wizard.

    Figure 2
On this screen, you can choose which tables to compare.  Usually I am only interested in one or two tables, so I clear the rest of the checkboxes.  If I have a million rows in my customer table and I’m not interested in migrating any of those rows, I can save a lot of processing time by un-checking the customer table.

d.      Click the Finish button to display the Data Compare window.

3.       The Data Compare window consists of two panes: the Object List on top and the Record Details at the bottom.

    Figure 3

a.       The object list displays each table or view as a single row with columns summarizing the number of new, removed, changed and identical rows.  Rows are matched on their primary key.

b.      Click a row in the object list to display details in the record details pane.  Here you can click a tab to view the rows that are new, missing or changed.  Checking the checkbox next to a record flags it to the Data Compare tool, meaning you want to update the target database to match the same row in the source database.  This may result in an INSERT, UPDATE, or DELETE statement depending on the tab on which the record is listed. 

c.       For a record to be flagged for update, both the table and the record must be checked.

4.       After checking all the rows you wish to update, click the Write Updates toolbar button to commit your changes to the target database. 

5.       Alternatively, you can click the Export To Editor toolbar button to generate a SQL script that you can run in the SQL Server query editor.  This method requires an extra step but has the following advantages

a.       You can modify the script before running it.

b.      You can send the script to someone else to run.

c.       You can view the script to learn what Data Dude is doing.  It’s interesting to note that constraints on each table are dropped before copying data, then created after the data is copied.  This speeds up the process.  Also, note the use of transactions to prevent incomplete data copies.  Below is a sample script updating data in one table.
/*
This script was created by Visual Studio on 8/15/2008 at 8:57 AM.
Run this script on dgiard.Test_QA.dbo to make it the same as dgiard.Test_Dev.dbo.
This script performs its actions in the following order:
1. Disable foreign-key constraints.
2. Perform DELETE commands.
3. Perform UPDATE commands.
4. Perform INSERT commands.
5. Re-enable foreign-key constraints.
Please back up your target database before running this script.
*/

SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*Pointer used for text / image updates. This might not be needed, but is declared here just in case*/
DECLARE @pv binary(16)
BEGIN TRANSACTION
ALTER TABLE [dbo].[OrderDetails] DROP CONSTRAINT [FK_OrderDetails_Orders]
ALTER TABLE [dbo].[OrderDetails] DROP CONSTRAINT [FK_OrderDetails_Products]
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Orders_Customers]
DELETE FROM [dbo].[ProductTypes] WHERE [ProductTypeID]=N'5646953f-7b89-4862-bcf3-bf53450d28bb'
INSERT INTO [dbo].[ProductTypes] ([ProductTypeID], [ProductTypeName]) VALUES (N'7beb0d99-d034-41b9-bbf7-f9cdcdbedc30', N'Furniture')
INSERT INTO [dbo].[ProductTypes] ([ProductTypeID], [ProductTypeName]) VALUES (N'abc19a14-5968-4c5f-9f0f-4debc034cb90', N'Hardware')
INSERT INTO [dbo].[ProductTypes] ([ProductTypeID], [ProductTypeName]) VALUES (N'b9e446ed-eeb1-4334-b191-c70a55ef1a05', N'Books')
ALTER TABLE [dbo].[OrderDetails] ADD CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY ([OrderID]) REFERENCES [dbo].[Orders] ([OrderID])
ALTER TABLE [dbo].[OrderDetails] ADD CONSTRAINT [FK_OrderDetails_Products] FOREIGN KEY ([ProductID]) REFERENCES [dbo].[Products] ([ProductID])
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[Customers] ([CustID])
COMMIT TRANSACTION

The Data Compare tool is a simple tool for accomplishing a useful task.  Since I discovered it, it has saved me a lot of time setting up new data environments.

 

.Net | SQL Server | VSTS
Friday, 15 August 2008 14:02:53 (GMT Daylight Time, UTC+01:00)
# Thursday, 14 August 2008

Writing Unit Tests is an essential step in developing robust, maintainable code.  Unit Tests increase quality and mitigate the risk of future code changes.  However, relatively few developers take the time to write unit tests for their stored procedures.  The primary reason for this is that few tools exist to test stored procedures.

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools to help developers write unit tests against SQL Server stored procedures.  The tool integrates with MSTest, wich is a testing framework many developers are already using for their other unit tests.

In order to write unit tests for your stored procedures, those stored procedures must be in a database project.  For information on how to create a database project from a SQL Server database see: http://www.davidgiard.com/2008/08/11/DataDudeTutorial1CreatingADatabaseProject.aspx

This document describes how to create a database unit test.

1.       Launch Visual Studio and open your Database Project.

2.       Open the Schema View.  Select View | Schema View.

3.       Right-click a stored procedure and select Create Unit Test from the context menu.  The Create Unit Tests dialog displays.

    Figure 1

4.       Check the checkboxes next to all the stored procedures for which you wish to create unit tests.  Select the .Net language (Visual Basic .Net or C#) in which you want the automatic code to be generated.  You won’t be modifying this code so it isn’t that important, but I tend to keep all my code in the same language, so you may as well choose your favorite language here.  Enter a meaningful name for the Unit Test Project and class.  I like to name my Unit Test projects the same as my database project, followed by “Tests” or “UnitTests”.  If this is a new Database Unit Test Project, the Database Unit Test Configuration dialog displays.

    Figure 2

5.       The Database Unit Test Configuration dialog allows you to specify what you want to occur when you run these unit tests.  The dialog is organized into the following sections.

a.       Database connections

                                                               i.      Execute unit tests using the following data connection
This is the database against which tests will run.  Typically I set this to my Development or QA database.

                                                             ii.      Use a secondary data connection to validate unit tests
You may specify a different database to validate the syntax of your unit tests and test that all the objects you refer to exist.  I can only think this might be good if you are writing tests while disconnected from your testing database, but I never set this option.

b.      Deployment

                                                               i.      Automatically deploy the database project before unit tests are run
To save manual steps, you may wish to check this box and deploy the database project to the database each time you run your unit tests.  This slows down the testing step so I do not select this option.  I prefer to deploy my changes once; then run my unit tests – sometimes several times.

c.       Database state

                                                               i.      Generate Test data before Unit tests are run
It is often useful to populate your database with some test data prior to your test run.  Use this button to do this.

6.       After creating your unit tests, you need to modify each one and specify what you are testing.  Open the Solution Explorer (View | Solution Explorer).

7.       Double-click the unit test class to open it in the unit test designer. 

    Figure 2

8.       The Unit Test Designer contains some controls and two panes as described below. 

a.       A class can contain multiple tests.  The first control is a dropdown that allows you to select which test you are designing.

b.      To the right of the Test Name dropdown is another dropdown that allows you to specify what part of the test you are writing.  You can choose between the test itself, the “Pre-test” (which runs before the test is executed) and the “Post-test” (which runs after the test has completed – successfully or unsuccessfully).

   Figure 3

c.       Further to the right are three buttons that allow you to add a new test or to delete or rename the currently active test.

d.      Below the controls is the test editor.  This is where you will write your test.  You will write your test in T-SQL and Data Dude provides some stub code to get you started.  Write SQL statements that call your stored procedure and return one or more results.

e.      Below the test editor is the Test Conditions pane.  It is here that you enter your assertions. 

    Figure 4

                                                               i.      You can test for a given result set having 0 rows, 1 or more rows, or an exact number of rows. 

                                                             ii.      You can also test if a specific column and row in a given result set evaluates to a given value. 

                                                            iii.      Click the “+” button to add new assertions.  Highlight an existing assertion row and edit the row or click the “x” button to remove the assertion. 

                                                           iv.      Use the properties window to modify properties of the assertion.  Many assertions are based on a given resultset.  When I first started writing unit tests, I found it difficult to determine which resultset was which.  Basically, any line in your SQL script that begins with the word “SELECT” creates a resultset.  Each resultset is numbered, beginning with 1, in the order it is created in your script.  I sometimes find it useful to copy the SQL code and paste it into SQL Management Studio query window and run it.  Each resultset then appears in a separate grid in the Results pane.  Looking at these grids allows me to more easily see a sample result set and in what order they are created.

f.        You run your database unit tests the same ways you run any MS Test unit test.  One way to run the tests is to open the Test List Editor (Test | Windows | Test List Window), check the tests you want to run, and click the Run Checked Test toolbar button.  Tests in which all assertions prove true are passed; all others are failed.

Using Data Dude, you can extend your unit tests to cover your database objects and, therefore, improve the overall quality and maintainability of your code.

.Net | SQL Server | VSTS
Thursday, 14 August 2008 14:56:47 (GMT Daylight Time, UTC+01:00)
# Wednesday, 13 August 2008

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases. 

In our last tutorial, we described how a database developer would use the Schema Compare tool to update a database project with changes to a SQL Server database.

This article describes how to use the Schema Compare tool to push those changes out to a different SQL Server database.  There are two scenarios where you would do this. 

In Scenario 1, a developer has a local copy of the development database and wishes to get the latest updates to the database. 

In Scenario 2, a database administrator (DBA) or build master who is charged with migrating database changes from one environment to the next.  Just as .Net and web code gets regularly migrated from a development environment to a QA or Production environment, database object code must also be migrated, and that migration generally must be kept in sync with all code that depends on those database objects.

We start this process by launching Visual Studio and opening the database project.  If a source code repository such as TFS is used, we need to get the latest code from the repository.

The database to which we wish to write the changes is known to Data Dude as the “target database”.  We need to make sure that a connection exists in Visual Studio to the target database.  This is a one-time step and you can use the Server Explorer (View | Server Explorer) to create a connection.

The following steps describe how to propagate changes to the database.

1.       Launch Visual Studio and open the database project.  Get the latest source code from your source code repository.

2.       From the Visual Studio menu, select Data | Schema Compare | New Schema Compare.  The New Schema Compare dialog displays.

    Figure 1

3.       Under Source Schema, select the Project radio button and select your database project from the dropdown list.

4.       Under Target Schema, select the Database radio button and select the connection to your database from dropdown list.

5.       Click the OK button to display the Schema Compare window.

    Figure 2

6.       The Schema Compare window lists every object that exists in either the database or the database project.  The objects are grouped in folders by object type (Tables, views, stored procedures, etc.)  You can expand or collapse a folder to view or hide objects of that type.  The important column is “Update Action” which describes what will happen if you write the updates to the target.

a.       Objects that exist in the source (the project) but not in the target (the database) were likely recently added after the last synchronization.  By default, the Update Action will be “Create” meaning the object will be created in the target database.

b.      Objects that exist in both the source and the target will have an Update Action of “Update” if they have been modified in the database since the last synchronization or “Skip” if they have not.

c.       Objects that exist in the destination (the database) but not in the source (the project) were likely dropped after the last synchronization. By default, the Update Action will be “Drop” meaning the object will be removed from the database.

7.       On a database with many objects, it is useful to view only the objects that have changed since the last synchronization.  To do this, click the Filter toolbar button and select Non Skip Objects. 

    Figure 3

8.       If you wish, you can modify the Update Action on objects by selecting the dropdown in the “Update Action” column.  Some actions are grayed out because Data Dude will not allow you to perform any action that would violate referential integrity rules. 

9.       After you have set the “Update Action” of every object appropriately, you have a couple options.

a.       You can migrate your changes immediately to the target database by clicking the “Write Updates” toolbar button.  Click Yes at the confirmation to write the updates to the database project.

b.      Alternatively, you can export your changes to a SQL script by clicking the Export To Editor toolbar button.  This will create a single text file containing SQL script that you can run from a query window of SQL Server Management Studio.  This is useful if you need to make changes to the script prior to executing.  I have used this technique when my database contains views or stored procedures that refer to remote servers and I want to modify the name of the server before migrating the object.

Alternatively, you can deploy changes from a database project to a database by “Deploying” the project (select Build | Deploy Solution).  This deploys your changes using the settings found on the Build tab of the project properties page.  This method requires fewer steps, but it is less flexible than the method described above.  In particular, it does not allow you to select which objects are deployed or export and modify the script of database changes.

In the next article, we will discuss how to use Data Dude to write Unit Tests against SQL Server stored procedures.

.Net | SQL Server | VSTS
Wednesday, 13 August 2008 12:46:54 (GMT Daylight Time, UTC+01:00)
# Tuesday, 12 August 2008

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases. 

In our last tutorial, we described how to create a new database project based on an existing SQL Server database.  As the source database changes, you will want to update the database project to reflect those changes.  This article describes how to use the Schema Compare tool to import database schema changes into your database project.  The steps in this article are typically performed by a database administrator (DBA) or database developer who is charged with creating tables, views, functions and stored procedures.

The Schema Compare tool can be used to display and manage differences between two databases, between two database projects, or between a database and a database project.  Most of the time, I use it to compare a database with a database project. 

After making a change to a database schema (for example, adding a new table or adding a new column to a table), use the Schema Compare tool as described below to update an existing database project with these changes.

1.       Launch Visual Studio and open your Database Project. (For info on how to create a database project from a SQL Server database see: http://www.davidgiard.com/2008/08/11/DataDudeTutorial1CreatingADatabaseProject.aspx )

2.       From the Visual Studio menu, select Data | Schema Compare | New Schema Compare.  The New Schema Compare dialog displays.

    Figure 1

3.       Under Source Schema, select the Database radio button and select the connection to your database from dropdown list.

4.       Under Target Schema, select the Project radio button and select your database project from the dropdown list.

5.       Click the OK button to display the Schema Compare window.

    Figure 2

6.       The Schema Compare window lists every object that exists in either the database or the database project.  The objects are grouped in folders by object type (Tables, views, stored procedures, etc.)  You can expand or collapse a folder to view or hide objects of that type.  The important column is “Update Action” which describes what will happen if you write the updates to the target.

a.       Objects that exist in the source (the database) but not in the target (the project) were likely added to the database after the last synchronization.  By default, the Update Action will be “Create” meaning the object will be created in the database project.

b.      Objects that exist in both the source and the target will have an Update Action of “Update” if they have been modified in the database since the last synchronization or “Skip” if they have not.

c.       Objects that exist in the destination (the project) but not in the source (the database) were likely dropped from the database after the last synchronization. By default, the Update Action will be “Drop” meaning the object will be removed from the database project.

7.       If you are updating your database project frequently, most of the objects will be unchanged and marked “Skip”.  On a database with many objects, it is useful to view only the objects that have changed since the last synchronization.  To do this, click the Filter toolbar button and select Non Skip Objects 

    Figure 3

8.       At this point, you can view differences and you may wish to modify the Update Action of some objects.

a.       If you click on an object row in the Schema Compare window, the SQL definition code of both the source and destination version appears in the Object Definition window.  Any differences between the two versions will be highlighted (changed lines in darker blue; new lines in darker green).

b.      If you like, you can modify the Update Action any object by selecting the dropdown in the “Update Action” column.  Some actions are grayed out because Data Dude will not allow you to perform any action that would violate referential integrity rules.  If several developers are sharing the same development database, you may wish to skip those objects on which you are not working.  You may also decide that some objects are ready to share with the rest of the team and others are not fully tested and should be skipped.  It is possible to change the Update Action of every object of a given type by right-clicking the type folder and selecting the desired action to apply to all objects of that type.

9.       After you have set the “Update Action” of every object appropriately, you can migrate your changes to the database project by clicking the Write Updates toolbar button.  Click Yes at the confirmation to write the updates to the database project.

    Figure 4

10.   If you are using a source control repository, such as TFS, you will want to check in your changes.

In the next article, we will discuss how to use the Schema Compare tool to write changes to a new database environment.

.Net | SQL Server | VSTS
Tuesday, 12 August 2008 13:40:04 (GMT Daylight Time, UTC+01:00)
# Monday, 11 August 2008

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases.  In order to use Data Dude to manage an existing SQL Server database, the first step is to create a database project. 

There are a couple key points you will need to know before using Data  Dude.

1.       The current version of Data Dude only works on SQL Server 2000 and SQL Server 2005.  Visual Studio 2008 Service Pack 1 should provide support for SQL Server 2008.  I will describe an example using SQL Server 2005.

2.       The validation engine in Data Dude requires that you install either SQL Server or SQL Express on the same machine on which Data Dude is installed. 

3.       You must grant “Create database” rights in this database engine to the currently logged-in user.

Now, let’s discuss how to create a database project to manage an existing SQL Server 2005 database.

1.       Open Visual Studio. 

2.       Select File | New Project… The New Project dialog displays

3.       Under Project Type, select Database Projects\Microsoft SQL Server

4.       Under Templates, select SQL Server 2005 Wizard.

5.       Enter a meaningful name and location for this project. 
Typically, my databases have names like “AdventureWorks_Dev” and “AdventureWorks_QA” which describe both the data and the environment to which the data belongs.  Because a single database project is used for all environments, I name my database project to describe the data and follow it with “DB” to make it obvious it is a database project.  In the above example, I would name my database project “AdventureWorksDb”.  In this exercise, I’ll create a project named “TestDB”.
New Project dialog
  
Figure 1

6.       The New Database Project Wizard displays with the Welcome screen active.

7.       At the Welcome screen, click the Next button to advance to the Project Properties screen.
Project Properties screen
  Figure 2

8.       I almost never change the options on the Project Properties screen.   If my database contains any stored procedures or functions written in C# or VB.Net, I will check the Enable SQLCLR checkbox. 

9.       Click the Next button to advance to the Set Database Options screen.
Set Database Options screen
  Figure 3

10.   The options on the Set Database Options screen correspond to the settings you will find in SQL Server Management Studio when you right-click a database and select Properties.  The defaults in the database project wizard are also the defaults in SQL Server.  Since I seldom override these defaults in SQL Server, there is usually no reason to change them on this screen.

11.   Click the Next button to advance to the Import Database Schema screen.
Import Database Schema screen
  Figure 4

12.   On the Import Database Schema screen, check the Import Existing Schema checkbox.  This enables the Source database connection dropdown.  If you already have created a connection to your database, select it from the dropdown.   If you have not yet created a connection, click the New Connection button to create one now.  The process for creating a database connection in Visual Studio hasn’t changed for several versions of the product so I won’t repeat it here.  However it is worth noting that, although Data Dude requires that you have a local installation of SQL Server, the database you connect to here can be located on any server to which you have access.  I always usually connect to the Development database because this is the first database I create for an application.

13.   Click the Next button to advance to the Configure Build and Deploy screen.
Configure Build and Deploy screen
  Figure 5

14.   The Configure Build and Deploy screen contains settings that will take effect when you “deploy” your database project.  Deploying a database project writes changes to the schema of a target database (specified in the Target database name field of this screen) and is accomplished by selecting the menu options Build | Deploy with the database project open and selected.  Deploying is most useful when each developer has his own copy of the development database and needs a quick way to synchronize his schema with a master copy.  

15.   Click the Finish button to create the database project initialized with schema objects found in the source database and with the settings you chose in the wizard screens.

16. After Visual Studio finishes creating the database project, view the objects in the Solution Explorer (Select View | Solution Explorer).  You should see a "Schema Objects" folder in the project containing a subfolder for each type of database object.  Open the "tables" subfolder to view you will see files containing scripts for each table in your database.  Double-click one of these script files to see the SQL code generated for you.
Database project in Solution Explorer
    Figure 6

17. If you use a source control repository, such as TFS, you will want to check this project into the repository to make it easier to share with others.

As you can see, when you use the wizard to create your project, most of the work is done for you.  You are able to change the default settings, but in most cases this is not necessary.  Often, the only change I make on the wizard screens is when I select a database connection.

In the next article, we will discuss how to use the Schema Compare tool to bring data changes into or out of your database project.

.Net | SQL Server | VSTS
Monday, 11 August 2008 15:12:22 (GMT Daylight Time, UTC+01:00)
# Sunday, 10 August 2008

Visual Studio Team System 2008 Database Edition is a mouthful to say, so a lot of people affectionately call it “Data Dude”.

Data Dude provides a set of tools integrated into Visual Studio that assist developers in managing and deploying SQL Server database objects.

There are four tools in this product that I have found particularly useful: the Database Project; the Schema Compare tool; the Data Compare Tool; and Database Unit Tests.

A Database Project is a Visual Studio project just as a class library or ASP.Net web project is.  However, instead of holding .Net source code, a Database Project holds the source code for database objects, such as tables, views and stored procedures.  This code is typically written in SQL Data Definition Language (DDL).  Storing this code in a Database Project makes it easier to check it into a source code repository such as Team Foundation Server (TFS); and simplifies the process of migrating database objects to other environments.

The Schema Compare tool is most useful when comparing a database with a Visual Studio Database Project.  Developers can use this tool after adding, modifying or deleting objects from a database in order to propagate those changes to a Database Project.  Later, a Database Administrator (DBA) can compare the Database Project to a different database to see what objects have been added, dropped or modified since the last compare.  The DBA can then deploy those changes to the other database.  This is useful for migrating data objects from one environment to another, for example when moving code changes from a Development database to a QA or Production database.

The Data Compare is another tool for migrating from one database environment to the next.  This tool facilitates the migration of records in a given table from one database to another.  The table in both the source and destination database must have the same structure.  I use this when I want to seed values into lookup tables, such as a list of states or a list of valid customer types that are stored in database tables.

Unit tests have increased in popularity the last few years as developers have come to realize their importance in maintaining robust, error-free code.  But unit testing stored procedures is still relatively rare, even though code in stored procedures is no less important than code in .Net assemblies.  Data Dude provides the ability to write unit tests for stored procedures using the same testing framework (MS Test) you used for unit tests of .Net code.  The tests work the same as your other unit tests – you write code and assert what you expect to be true.  Each test passes only if all its assertions are true at runtime.  The only difference is that your code is written in T-SQL, instead of C# or Visual Basic.Net. 

There are some limitations.  In order to use Data Dude, you must have either SQL Server 2008 or SQL Express installed locally on your development machine and you (the logged-in user) must have "Create Database" rights on that local installation.  To my knowledge, Data Dude only works with SQL Server 2000 and 2005 databases.  Plans to integrate with SQL Server 2008 have been announced but I don't know Microsoft's plans for other database engines.   I also occasionally find myself wishing Data Dude could accomplish its tasks more easily or in a more automated fashion.  I wish, for example, I could specify that I always want to ignore database users in a database and always want to migrate everything else when using the Schema Compare tool.  But overall, the tools in this product have increased my productivity significantly.  Nearly every application I write has a database element to it and anything that can help me with database development, management and deployment improves the quality of my applications.

.Net | SQL Server | VSTS
Sunday, 10 August 2008 13:34:41 (GMT Daylight Time, UTC+01:00)