Basel · Baden · Bern · Lausanne · Zürich · Düsseldorf · Frankfurt/M. · Freiburg i. Br. ·...

Basel · Baden · Bern · Lausanne · Zürich · Düsseldorf · Frankfurt/M. · Freiburg i. Br. · Hamburg · München · Stuttgart

Visual Studio for Database Professionals

MSDN TechTalk

M. Lattmann, Consultant

Wallisellen, 4.12.2006

Kurs- bzw. TC-Namen eingeben 2 © 2006

Agenda

Data Generation Generating Test Data

Database Unit Testing Creating Unit Tests Extending Unit Tests

Extensibility Generator Extensibility Distributor Extensibility Conditions Extensibility

Core messages

Knowledge transfer is only the beginning. Knowledge application is what counts.


DemoDemoGenerating Test Data


Value generators Simple generators for each data type

Strings: ASCII and Unicode ((var)char, n(var)char, (n)text) Numbers: tinyint, smallint, int, bigint, real, float, decimal, numeric, money Binary ((var)binary, image) Date and Time UniqueIdentifier (GUID) Bit

Complex generators Foreign Key, Regular Expression, Data Bound Generator

Distributions Uniform, Normal, NormalInverse, Exponential, ExponentialInverse Can import column distributions from production database (*)

(*) Not implemented in CTP7(*) Not implemented in CTP7

Generating Test Data


Agenda




Core messages



Unit Testing

Do changes break your existing code?

Tests for Stored Procedures, Functions Triggers Any SQL

Unit test designer is SQL focused Work in the language of your choice: T-SQL, C#, VB.NET

Builds on existing Unit Test functionality in Team System Works together with your application tier unit tests

Well established in .NET development


Automatically generate unit tests stubs for: Stored Procedures, Functions, Triggers

Test Validation (assertions) T-SQL (server based) Assertions

RAISERROR command Client Side Assertions

None Empty ResultSet Row Count Execution Time, …

Database Unit Testing


DemoDemoUnit Testing


Automatic Deployment Integration Automatically deploy database project prior to running tests

Data Generation Integration Automatically generate data based on generation plan prior to

running tests

Test Scripts use standard Team System Unit Tests

Pre- und Post Test Scripts are specific for a single unit Test. TestInitialize() and TestCleanup() are common within a test class.

Generated Code can be extended using C# or VB.NET

Pre & Post Test Scripts


Pre & Post Test Scripts

[AssemblyInitialize]...

DeployDatabaseProject();

GenerateData();

[TestInitialize] Common Script

[Test]

Pre-Test 1

Test 1

Post-Test 1

[TestCleanup()] Common Script

[TestInitialize()] Common Script

[Test]

Pre-Test 2

Test 2

Post-Test 2

[TestCleanup()] Common Script

[AssemblyCleanup]...

<DatabaseUnitTesting> <DatabaseDeployment ... <DataGeneration ... </DatabaseUnitTesting>

App.config

(DatabaseSetup.cs)

Per test project

per Test

Per Test

Per test class


Managing Transactions in Unit Tests

BEGIN TRANSACTION TestTransaction

UPDATE "Order Details" set Quantity = Quantity + 10 IF @@ROWCOUNT!=50 RAISERROR('Row count does not equal 50',16,1)

ROLLBACK TRANSACTION TestTransaction

Capability of putting tests in automatic rollback mode to always maintain original state of database

Implementation in T-SQL



C#, per Test

[TestMethod()]public void dbo_InsertTable1Test(){ CommittableTransaction tx = new CommittableTransaction(); ExecutionContext.Connection.EnlistTransaction(tx); PrivilegedContext.Connection.EnlistTransaction(tx); // run tests tx.Rollback();}



C#, for all Tests

TransactionScope _trans; [TestInitialize()]public void Init() { _trans = new TransactionScope(); base.InitializeTest(); }

[TestCleanup()]public void Cleanup() { base.CleanupTest(); _trans.Dispose(); }


DemoDemoExtending Unit Tests


Agenda




Core messages



Generator Extensibility

Team System has many Extension Points Generator Extensibility is just an Example

Implemented by .NET Developer

Used by DB Professional


Data Generator Extensibility

Generators Implement:

IDesigner IGenerator

Base class Generator

Attributes GeneratorAttribute GeneratorNameAttribute

Distributions Implement:

IDistribution

Conditions Implement:

ICondition


Generators and Distributions have to: Live in or under the: %ProgramFiles%\Microsoft Visual Studio 8\DBPro\

Extensions directory Get registered in the %ProgramFiles%\Microsoft Visual Studio 8\DBPro\

Microsoft.VisualStudio.TeamSystem.Data.Extensions.xml file Be strong key signed

<?xml version="1.0" encoding="us-ascii"?><?xml version="1.0" encoding="us-ascii"?><types version="1"><types version="1">

<type>Microsoft.VisualStudio.TeamSystem.Data.Generators.RegexString, <type>Microsoft.VisualStudio.TeamSystem.Data.Generators.RegexString, Microsoft.VisualStudio.TeamSystem.Data.Generators, Microsoft.VisualStudio.TeamSystem.Data.Generators, Version=2.0.0.0, Version=2.0.0.0, Culture=neutral, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3aPublicKeyToken=b03f5f7f11d50a3a

</type></type><type>Microsoft.VisualStudio.TeamSystem.Data.Generators.Exponential,<type>Microsoft.VisualStudio.TeamSystem.Data.Generators.Exponential,

Microsoft.VisualStudio.TeamSystem.Data.Generators, Microsoft.VisualStudio.TeamSystem.Data.Generators, Version=2.0.0.0, Version=2.0.0.0, Culture=neutral, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3aPublicKeyToken=b03f5f7f11d50a3a

</type></type></types></types>

Registration


DemoDemoGenerator Extensibility


Core messages

All Tasks of DB Developers integrated in the Team System Lifecycle

Less support for DBAs and DB Architects

Strong Support of Database Changes Automatically generate Change Scripts Repository, Versioning

Strong Testing Features Unit Tests, Data Generation

Integrated in MSBuild Automated Regression Tests

Extensible

Data are always part of the game.


Pricing, Licensing and Availability

CTP 7 Available Today

Availability for MSDN Universal Subscribers Around 8. Dec 2006

General availability in January 2007: Included in Team Suite at No Extra Cost Purchase as an individual Edition Same pricing as other Team System Editions


Literature

Ambler, Sadalage: Refactoring Databases: Evolutionary Database Design

Basel · Baden · Bern · Lausanne · Zürich · Düsseldorf · Frankfurt/M. · Freiburg i. Br. ·...

Documents

Transcript of Basel · Baden · Bern · Lausanne · Zürich · Düsseldorf · Frankfurt/M. · Freiburg i. Br. ·...