DAT 433 Under the Hood of Visual Studio

DAT 433 Under the Hood of Visual Studio Team Edition for Database Professionals

DAT 433 Under the Hood of Visual Studio Team Edition for Database Professionals Gert E. R. Drapers Architect/Development Manager Microsoft Corporation

Agenda The Project System Understanding the Schema The Schema Object Container Build and Deploy Data Generation Generator Extensibility Distributor Extensibility Generating from the Command Line Database Unit Testing Using the Code Underneath Data Driven Testing

Project Model The center of gravity SQL Server Database Project Template SQL Script Import database schema Create New Project Database Project Reverse engineer existing. SQL script files (*) Not implemented in the current CTP Collection of. SQL file containing TSQL DDL fragments

Offline Model Project model Schema Objects representation Collection of T-SQL DDL fragments Objects are Parsed and Interpreted at: Project Load Time Object Change (save) Source Control Sync (external change)

Shredding in to SQL Fragments Loading, importing or reverse engineering shreds the schema definition into the smallest possible DDL fragments, for example: Table CREATE TABLE [dbo]. [Territories] ( [Territory. ID] [nvarchar] (20) NOT NULL, [Territory. Description] [nchar] (50) NOT NULL, [Region. ID] [int] NOT NULL ) ON [PRIMARY] Primary Key ALTER TABLE [dbo]. [Territories] ADD CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED ([Territory. ID]) ON [PRIMARY] FK ALTER TABLE [dbo]. [Territories] ADD CONSTRAINT [FK_Territories_Region] FOREIGN KEY ([Region. ID]) REFERENCES [dbo]. [Region] ([Region. ID])

Understanding Your Schema Build-up understanding of the DDL Fragments in Stages Phase-1 Parsing Retrieve the object identifier and object type Phase-1 Interpretation Retrieve additional type specifics like schemabinding Phase-1 SQL Server Compile Validation Perform compile time validation against (local) SQL Server, design database with is associated with the project Phase-2 Parsing Build a full AST (Abstract Syntax Tree, aka the parse tree) for the DDL fragment Phase-2 Interpretation Retrieve the remaining type specific detail from the AST All stages contribute to building and maintaining the schema context Object symbol list Object dependency graph (tracking)

Understanding Schema Objects P 1 Parsing Update Schema Context Warning List Failed Success Schema Manager Failed Success Failed P 2 Interpretation Failed Success Design DB s es cc Su Success Error List Success P 1 Interpretation P 2 Parsing Failed Add to Schema Context

File Naming & Extension Scheme Everything is a. SQL file Associated with the T-SQL editor Using a two part naming scheme to identify types This is not required, but helps identification of types By default the file name encodes the object name Not required Filename do not have to match the containing type name Required since SQL Server namespace restrictions do not match the file system naming restrictions

Directory Structure SQL 2000 Project Data Generation Plans Schema Objects Functions Stored Procedures Tables Views Security Roles Users Roles Application Roles Database Roles Storage File Groups Full Text Catalogs Types User-defined Data Types Scripts Post-Deployment Pre-Deployment

Directory Structure SQL 2005 Project Data Generation Plans Schema Objects Aggregates Assemblies Database Triggers Functions Security Asymmetric Keys Certificates Endpoints Roles Application Roles Database Roles Schemas Symmetric Keys Users Schema Objects… Storage File Groups Full Text Catalogs Partition Functions Partition Schemes Stored Procedures Synonyms Tables Types User-defined Data Types User-defined Types (CLR) XML Schema Collections Service Broker Contracts Event Notifications Message Types Queues Remote Service Binding Routes Services Views Scripts Post-Deployment Pre-Deployment

Exploring the File Structure

Build & Deploy SQL Server Database Project Template SQL Script Import database schema Deploy SQL Script Build project Create New Project Database Project Deploy project Reverse engineer existing. SQL script files (*) Not implemented in the current CTP SQL Server Database

Command Line Building Using devenv. exe Visual Studio shell in command line mode Using MSBuild. exe Important note: In CTP 3 the project needs to be opened inside Visual Studio!

Project Properties SET options Only override when different Collations Only override when different Difference between New and Update

Building Using MSBuild – New database script msbuild Northwind. Online. dbproj /t: build /p: Configuration="New Deployment" Build – Update for defined target server msbuild Northwind. Online. dbproj /t: build /p: Configuration="Update Deployment" /p: Target. Connection. String="Data Source=(local)sql 80; Integrated Security=True; Pooling=False; " /p: Target. Database="Northwind. Online. Test. Run"

Deploying Using MSBuild Deploy – New database msbuild Northwind. Online. dbproj /t: deploy /p: Configuration="New Deployment" Deploy – Update Database msbuild Northwind. Online. dbproj /t: deploy /p: Configuration="Update Deployment" /p: Target. Connection. String="Data Source=(local)sql 80; Integrated Security=True; Pooling=False; "

Misc. Actions Using MSBuild All (Build + Deploy) msbuild Northwind. Online. dbproj /t: all Clean msbuild Northwind. Online. dbproj /t: clean /p: Configuration="New Deployment" msbuild Northwind. Online. dbproj /t: clean /p: Configuration="Update Deployment"

MSBuild Task: Sql. Build/Sql. Deploy Build. Type { "New Deployment" | "Update Deployment“ } Target. Connection. String Target. Database Build options: Default. Collation {"True" | "False“} Enable. Full. Text. Indexing {"True" | "False“} Script. Create. DBStatement {"True" | "False“} Generate. Drops. If. Not. In. Project {"True" | "False“} Source. Database {"True" | "False“} Set options: ARITHABORT {"True" | "False“} NUMERIC_ROUNDABORT {"True" | "False“} ANSI_NULLS {"True" | "False“} CONCAT_NULL_YIELDS_NULL {"True" | "False“} ANSI_PADDING {"True" | "False“} ANSI_WARNINGS {"True" | "False“} QUOTED_IDENTIFIER {"True" | "False“}

Command Line Build & Deploy

Provisioning Multiple Servers How can I deploy to multiple targets? The Database Project only understand a single target server/database at the time You can use the MSBuild tasks to provision multiple servers Using command line or tool that calls the MSBuild infrastructure Pseudo code for each server+database combination in list { Sql. Build. Task Sql. Deploy. Task }

Data Generator Extensibility Generators Implement: IDesigner IGenerator Base class Generator Attributes Generator. Attribute Generator. Name. Attribute Distributions Implement: IDistribution Registration

Registration Generators and Distributions have to: Live in or under the: %Program. Files%Microsoft Visual Studio 8DBProExtensions directory Get registered in the %Program. Files%Microsoft Visual Studio 8DBProMicrosoft. Visual. Studio. Team. System. Data. Extensions. x ml file Be strong key signed <? xml version="1. 0" encoding="us-ascii"? > <types version="1"> <type>Microsoft. Visual. Studio. Team. System. Data. Generators. Regex. String, Microsoft. Visual. Studio. Team. System. Data. Generators, Version=2. 0. 0. 0, Culture=neutral, Public. Key. Token=b 03 f 5 f 7 f 11 d 50 a 3 a </type> <type>Microsoft. Visual. Studio. Team. System. Data. Generators. Exponential, Microsoft. Visual. Studio. Team. System. Data. Generators, Version=2. 0. 0. 0, Culture=neutral, Public. Key. Token=b 03 f 5 f 7 f 11 d 50 a 3 a </type> </types>

Extending the Data Generation

Database Unit Testing Database Unit Test Designer Similar to Win. Forms Designer Provides T-SQL View of Database Unit Test Round-trips C# / VB Team Test Unit Test Code Generates C# or VB. NET Code Standard Team Test classes &methods Standard ADO. NET Data Access Code

Extending Database Unit Tests Customizing generated code Custom Verification Logic Go beyond supplied test conditions with custom C#VB verification logic Managing Transactions Capability of putting tests in automatic rollback mode to always maintain original state of database Data Driven Testing Drive database test with parameters supplied from specified data source

Extending Database Unit Tests Sachin Rekhi Program Manager Visual Studio Team System

Resources Chalk Talks Visual Studio Team Edition for Database Professionals: Overview DEV TLC Theatre 6/15/2006 9: 45 AM-11: 00 AM Hands on Labs DEV 008 Take a Tour of Visual Studio 2005 Team System for Database Professionals

Resources… CTP 3 Download Site http: //download. microsoft. com/download/1/a/3/1 a 32 ea 84 -11 a 34 adf-953 e-7 a 65 b 9831 f 5 a/VSDATAD 1. img Team Website http: //msdn. microsoft. com/vstudio/teamsystem/products/dbpro/de fault. aspx Product Forum http: //forums. microsoft. com/MSDN/Show. Forum. aspx? Forum. ID=7 25&Site. ID=1 Power. Toys and Samples http: //gotdotnet. com/Workspaces/Workspace. aspx? id=378460 fd 1254 -427 b-aa 7 d-e 777 a 826 a 564 Blogs http: //blogs. msdn. com/gertd

Summary Understanding the schema references and relationships Using. SQL files as canonical representation Command Line Access through MSBuild tasks Extensibility of Data Generation Customization of the Database Unit Testing

Fill out a session evaluation on Comm. Net for a chance to Win an XBOX 360!

© 2006 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.