New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

Tools for working with databases

By James Hippolite, in , posted: 15-Mar-2007 21:23

Tools for working with databases
Mike Zeff

Developer Evangelist

Developer & Platform Strategy Group

Microsoft NZ

Or how to make the database guy part of the development team.

Visual Studio Team System

Team Edition for Database Professionals

  • Expand to database teams
  • Manage database change
  • Database Testing
  • Database Deployment

What we heard from customers

(or “why did we build it…”)

  • Managing data base change is hard…
    • A rollback means  a LONG night
    • Development teams can end up working with out-of-data versions
    • Finding errors at the end of the development cycle
  • Disconnect between development and database teams

Conceptual Overview

  • Schema change now managed in VS Team System and TFS
  • Production Database is now “One version of the truth” only for Data
  • “One Version of the truth for Schema” is Under Source Control

Creating a Database Project

  • Core concept: offline database development
  • Simply a series of files collected together into a single logical collection
  • The files represent the truth of your schema
  • Can be included in complete solution
  • Can be stored in source control such as Team Foundation Server for versioning

Offline Development

  • Import database schema to populate project from production
  • Nothing changes until deployment
  • A couple of other points, but Mike was too fast


Demo Summary

  • Import schema from existing database
  • Database project is created as a series of .SQL files.
  • Some more, but he’s too fast.

Isolated Iterative Development

Benefits of this Approach

  • Managed, project oriented evolution of database schema
  • Application code and database
  • Too fast

Managing Change

  • Changes are local to the project
  • Project can be compared with database
  •  All elements can be managed under version control
  • Template driven
    • Version specific Microsoft SQL Server 2000 or SQL Server 2005

Working with the Project

  • Add new elements
  • Too fast


  • Bring the power of refactoring to SQL
  • Update all dependent objects in a database project
  • Make an atomic change, see preview
  • Rename

Demo Summary

  • All changes occur offline, local to the project
  • Changes can be compared  to database
  • Refactoring can update all dependent objects

A Rollback Means a LONG night

  • Unit testing helps ensure that changes do not break existing code
  • Unit test designer is SQL focused
  • Work in the language of your choice: T-SQL, VB, C#
  • Builds on existing Team Test unit Test functionality

What We Can Test

  • Stored Procedures
  • Functions
  • Triggers
  • Arbitrary SQL
  • Too fast

Test Data

  • To create a solid foundation for testing we support data generation
  • Deterministic – always generate
  • Too fast

Demo Summary

  • Use of Regular Expressions to limit generated test data
  • (he couldn’t get the RegEx to validate an email address)
  • Can specify number of rows to generate (up to 1 million?)
  • Can specify ratio of rows to related tables


  • Standard Visual Studio build task
    • New versus update builds
    • Project properties for builds
    • Scheme compare for builds
  • Configurations
  • Pre/Post Deployment scripts
  • Build results in SQL script file
  • Deploy

Build Cycle

  • Can also be used in a “continuous” environment

Deployment Summary

  • Deploy using .SQL file
  • Too fast

Additional Resources

  • Team web forum
  • Team blogs

Call to action

  • Visit the Database Professional Team Centre
  • Watch the on demand web cast series
  • Visit for special offers on VSTS and VSTE DB Pro
  • Extra credit: install the beta version
  • Too fast


The Future of Data interaction


Data != Objects


  • Fix the impedance mismatch among different data domains
  • Focus on the needs of the application not bridging disparate data representations
  • Build on general purpose language features in C# 3.0 and ????

.NET Language Integrated Query (LINQ)

  • LINQ enabled data sources
  • LINQ enabled ADO.NET
    • LINQ to SQL
    • LINQ to Datasets
    • LINQ to entities
  • LINQ to Objects
  • LINQ to XML

.NET Language Integrated Query framework

  • Too fast

Querying relational data

  • current way (SqlCommand, SqlConnection, DataReader)
  • LINQ way (Define [Table], and use in code as a first-class object).

Call to Action


Other related posts:
Teach Yourself Azure in 4 hours
Geek Post Monthly Newsletter Volume 2 Issue 10

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".