T-SQL vs PL-SQL

"Oracle PL-SQL is the same as Microsoft T-SQL. If you know one, you know the other"

Not really.

Here are a few differences, some of them can have a serious impact of the way you write sproc code.

Good:

• T-SQL: stored procedures return result sets very easily: all you have to do is write a select statement without any 'INTO'. PL-SQL: you have to select into a ref cursor and define this ref cursor as output parameter.

Bad:

• T-SQL: stored procedures do not rollback automatically if something fails. Until SQL Server 2005 you had to test @@ERROR after each and every statement and goto a handler to rollback. Ugly, tedious and error-prone. In 2005 you can use a TRY CATCH, which is much more elegant. However you still have to rollback explicitly
  PL-SQL: sprocs are atomic. Any error inside a sproc rolls everything back up to the point where the sproc was called.
• T-SQL: no %TYPE! You can't refer to the type of a column without repeating it.
• T-SQL: converting a string to a date. You can't explicitely specify the target format.
  PL-SQL: to_char allows you to specify a target format which is the safest way to know what you're going to get
• T-SQL: RAISERROR does not break the flow. It simply returns an error string or message but the sproc still returns normally. Unless you use it within a TRY block, in which case the flow is diverted to the CATCH block. Oracle's raise_application_error throws an exception, exits the current sproc, rolls back till implicit savepoint at the beginning of the sproc...

Other differences:

• Oracle does not have a BOOLEAN column type, although you can define a BOOLEAN variable in PL/SQL. SQL Server has a BIT column type where values can be only 0 or 1.
  

Get time in HH:mm format regardless of the local culture

DateTime.Now.ToShortTimeString() returns a string that depends on the culture.

DateTime.Now.ToString("HH:mm") is deterministic.

Use DateTime.Now.ToUniversalTime.ToString("HH:mm") to get the current HH:mm time in UTC.

Using NUnit with native C++

NUnit was designed to be used with managed apps. So what?

All you need to do is create a C++/CLI project to host the test files. To link the test project to the native C++ project, simply add a configuration to the solution and call it ReleaseUnitTests for instance. This configuration will build the native C++ project as a static library as opposed to an executable.

The C++/CLI test project links to this library and can call into any public method of the native project. Job done.

Who needs cppunit?




Detailed steps:




1. Install NUnit
2. In Visual Studio Pro, create a native C++ project and call it CatHouse
3. Add a class called Cat with a public method void feed()
4. Open the properties of project CatHouse and in Configuration Properties > General, change configuration type to Static Lib (that will make it easy to link all the content with the test program)
5. Add another project to the solution: choose Visual C++ > CLR > Class Library and call it CatHouseTests. This will create a mixed assembly containing both native and managed code.
6. Open CatTests project properties, go to Common Properties > References and add the nunit.framework assembly
7. Add a C++ ref class called CatTests. Declare it with public ref because we want it to be a managed type that can be seen by Nunit. Add a public method called TestFeed().
8. In CatTests.h, add the line using namespace Nunit::Framework.
9. Now you can add the [TestFixture] and [Test] attributes to the class and test method declarations respectively
10. In Project Dependencies of CatHouseTests, tick CatHouse.
11. Add a #include "Cat.h" in CatTest.cpp and change the project properties so that it knows where to find the include.
12. In the TestFeed() method, instantiate a Cat object on the native stack and call its Feed method.
13. By default the CatHouse project has a Debug and Release configurations. Add a new one called ReleaseUnitTests: in Build > Configuration Manager in the Active Solution Configuration drop-down, select New. In the dialog, type ReleaseUnitTests and in Copy settings from choose Release. Click OK. Now all projects have a configuration called ReleaseUnitTests.
14. Using Configuration Manager, ensure that the Debug and Release solution configurations build CatHouse only and not CatHouseTests. Ensure that ReleaseUnitTests builds both CatHouse and CatHouseTests, both having project configuration ReleaseUnitTests.
15. Build the solution
16. In NUnit GUI type Ctrl O, select the CatHouseTests.dll. The TestFeed test should appear in the tree view.

TechEd EMEA 2008 Developers Wrap

What I got out of this TechEd

Saw a total of 15 sessions and did 4 labs.

If I had to pick just one thing, that would be VC9 DBPro (VSTS2008 Database Edition) with its database deployment and sproc unit-testing features. I saw a talk about it then played with it at the lab. It has the potential to remove a LOT of pain from schema upgrades.

Because most sessions I picked were centered around unit-testing I'm a bit less ignorant about the subject:

• now I know there is a descent framework for unit-testing native C++ code in VSTS2008 Dev. Ok you have to write C++/CLI but the next best thing is cppunit so... VSTS2008 Dev is apparently the best tool around for the moment.
• database unit-testing in VC9 DBPro where it creates a C# class for you that automatically calls a T-SQL sproc where you write your test.
• other tools: I had an overview of the tools available for managing unit-tests and IOC containers: Pex, TypeMock, TestDriven. They're on my list of things to try out next.

Integration Testing:

• It's worth digging into SpecExplorer 2007 and see what can be done with it. The idea of creating a simplified state model of your system and having the tool generate all possible paths for you is very interesting.

Methodologies

• I am now a bit more familiar with IOC frameworks, interface-based coding and TDD so I feel more confident to start writing my first unit-tests. One day maybe I'll write tests before the code...
  

Friday - TDD and Interface-based Programming

DVM303 - Understanding Test Driven Development

Speaker: Roy Osherove

This is my 3rd session with Roy following from Designing for Testability and Future of Unit-Testing.

TDD helps with

• trusting your tests
• designing the app (because it forces you to take the point of view of the client)
• implementing stuff that works partially as opposed to not at all
• code quality
• documenting the code
• reducing integration testing time, reducing number of bugs in production
  

How to write a unit test with TDD?

• add attribute [Test] to methods, [TestFixture] to classs
• naming: tests must have a good name to document their intention: method + scenario + expected behaviour.

Content of the test:

1. arrange (instatiate objects)
2. act (call the method under test) ,
3. assert (check the expected result)
   

Writing process
That's the interesting bit, the part that's totally couter-intuitive and requires a good dose of self-discipline.

1. Make it Fail: write a test that fails because the method is not doing what's expected. Purpose: test your test
2. Make it work: with minimum code, make the test pass by writing anything even if it's stupid as long as it doesn't break any other test.
   
3. Make it better: refactor but do not add functionality and make sure the test still passes.
   
4. Want to add more functionality? Ok but write another test first.
   

Tools:

• use Testdriven to run test by right-clicking method in the middle of the method. Apparently TestDriven allows you to run a test even if you don't have a fully working executable.
• If you use VSTS, use the built-in MSTest because it brings a lot of integration benefits. It runs slower though. Usually unit-tests are kept in a separate VS project.

Tip from Roy: it's a bit of a culture change so implement it incrementally and don't mention it by name to avoid scaring people off!

ARC213 Intentions & Interfaces - Making Patterns Concrete

Speaker: Udi Dahan

Very sarcastic talk by Udi, making fun of the abuses of the visitor and strategy patterns.

"Make your roles explicit" Instead of overriding virtual methods, use generic interfaces. You then retrieve concrete objects for those interfaces from service locators. In the end he was recommending Inversion of Control containers as Roy did in his testability session but with a different approach. Roy's emphasis was on writing code so that unit-tests stick to PC-COF while Udi's angle was on making it easy to change code in large projects.

Slides.

Friday - DB Deployment and Unit-Testing with DBPro

TLA03-HOL - Introduction to Visual Studio Team System 2008 Database Edition

I took this lab as a follow-up to Brian Randell's talk on Tuesday.

This is by far the most useful tool I learned about at this TechEd.

You start by creating an empty database project.

• Then you import the schema from an existing database. VSTS automatically creates a folder struture for schema objects in the solution folder. Each db object has got its own script therefore you can commit them all into CVS if you need to (you don't have to use Foundation Server).
• To add an object, right-click Add Table in the Schema View. This generates a script skeleton that you edit as you see fit (add columns, add options, etc...). Save the file and the Schema View is updated automatically. Change an object in the Schema View and the script is updated automatically.
  

Schema compare
That's where it gets interesting:

• Choose a target database to compare the offline schema to the target schema.
• A view appears that shows all changed, new or missing objects.
• Select one of the changed object in the lists and view the difference (new column, changed line in sproc, etc..)
• You can also preview the DDL script that VSTS is about to generate
• You can enable/disable individual differences from the list if you don't want them to be part of the script or force some to be in the scripts if you really want them to be there.
• Export the DDL script to a .sql file then edit if you need to tweak it before testing it against a pre-prod database.
  

So the tool is nice because it does the dirty work for you, leaves you a high degree of control and makes the schema differences absolutely crystal clear.

Database unit-testing

• You can create a unit-test for a sproc.
• VSTS automatically creates a test class that calls the sproc that contains the unit test. All you have to do is write the T-SQL for the sproc that does the unit-test. DB Sprocs tests are therefore perfectly integrated to all other tests.
• You can attach a Pre-Test and Post-Test to a sproc test in order to put the db into an known state.

Thursday - Testing in Team System

TLA13-HOL - Visual Studio Team System code name "Rosario": Team Development

Unit Test management
Everytime you run tests it automatically creates a new test run with a default name and records all the test results. So you keep a history of what tests were run and when and which ones passed or failed.

Types of tests you can create
VS is used to managed both unit-tests and integration tests. You can create:

• coded UI tests (where you write UI tests using automation as opposed to record/replay)

• Database unit-tests to test T-SQL stored procedures.
  

• Generic tests. This is actually an external program that will look like an ordinary test from within visual studio. You specify the command line arguments to call the external program. You have the option to redirect the standard output / error to the test results or not.
• Load tests. That's quite fun: you can simulate heavy load conditions with many users. Among other things you can define a load pattern (constant load or increasing load) and the distribution (percentage of appareance for each test).
• Manual tests: simple text file describing a manual test procedure. This is there for auditing purposes only obviously.
• Ordered test: specify a list of tests to be run in a specific sequence in the situation where order counts (that's for integration tests only, unit-tests should not be order dependent)

Impact analysis: as you change code, VS gives you the list of recommended tests (tests that should be re-run as a result of your changes)

Thursday - SQL Tips and Tricks

Before he ran to the post office to mail stuff that Carl Franklin had left behind, Stephen Forte demonstrated a few tips:

• To write a fancy UPDATE statement that needs a complex subquery, use WITH (common table expression)
• To keep 2 tables in sync with one single statement, use the new MERGE syntax (new in 2008).
• To filter out the results of a ranking query without affecting the rank values, use WITH again.
  

Thursday - Test Creation Automation with SpecExplorer 2007

DVP302 - Automating Test Creation

Something clever to automate creation of integration tests: SpecExplorer 2007

Keith Stobie demonstrated a tool that automatically generates tests based on a simple model of a system. You start by defining states and actions. Actions have an effect on the System Under Test and cause a transition to another state. You also define expectations (the expected results of those actions). The evaluation of those expectations either causes an error (and the test fails) or results in a transition to another state.

In other words you model your system with a state machine.

The whole point of the tool is to generate all possible ways of navigating through the state transition graph. You end up with non deterministic tests that go through scenarios you didn't necessarily think of.

This makes the tool relevant to integration testing, not unit-testing.

Last night's UK Country drinks at BroadBar badly affected my attention span so all I remembered right after Keith's talk was:

Lots of little tests is better than a single big one

There are no perfect models, only useful ones

Start small: even very small models can find bugs

DVP01-IS Model Based Testing with Spec Explore

In the afternoon Keith did another demo of SpecExplorer 2007 followed by Q&A session.

• How does SpecExplorer interact with the System Under Test ?

You use adapters that hide the concrete implementation of the actions (such as calling an executable, sending a message, calling a sproc, clicking a button, etc...)

• SpecExplorer is a free add-in to Visual Studio that will be available as a powertoy in the beta of VS2010.
  

There is a video about SpecExplorer here.

Wednesday - More Unit Testing and more C++

ARC308 - The future of unit testing

Following up yesterday's session about designing for testability, Roy gave an opinionated overview of the existing unit-testing tools/frameworks (there are lots of them), comparing those he thinks will stick around against those that might fade away.

His final advice:

if you're new to unit-testing, don't get into TDD immediately, start with writing a few unit-tests incrementally to get the hang of it.

His powerpoint is here.

TLA401 - Microsoft Visual C++ 2008 for Unrepentant C++ Developers

The last time I saw Kate Gregory was at TechEd 2007 in Orlando. She hasn't changed: her demos go really fast :-).

VS2008 SP1 comes with TR1 which is a set of proposed additions to the next C++ standard C++0x. It includes stuff that is currently in the boost library. Kate explained and demoed each of the following language additions:

• shared_ptr: safer, more intuitive, more powerful than auto_ptr.
  
• lambda expressions: [] or [&] or [=] equivalent of => in C#. Allows you to define short functions inline. She showed how elegantly they integrate with STL.
  
• auto: equivalent of C# var so you don't have to spell out a type when the compiler can work it out by itself.

Kate's question to the audience: "who in this room learned C++ in this century?" Just one person raised his hand (that wasn't me).

Kate's final message: "C++ is not dead!". Indeed, new versions of Visual Studio keep adding new things for C++:

• TR1 additions
• New MFC classes, ribbon toolbar
• New libraries for parallel development
• Some Vista features are only available to C++
• C++ is the most practical language to do interop
  

Tuesday - Unit Testing in Visual Studio

DVP304 - Building fast and secure native applications with VSTS 2008

The good news from this session by Lin Xu: yes, you can run unit-tests against native C++ from VSTS!
The catch is you have to write them in C++/CLI and compile the test project with the /clr option.

VSTS 2008 also comes with the sort of features that tools like DevPartner offer:

Profiling:

• choose between sampling (takes snapshots at intervals) and instrumentation (records all function calls).
• instrumentation generates a call tree view
• performance reports show how much time was spent in each function.

Static analysis:

• compile with the /analyse option to raise warnings highlighting potential coding mistakes. This tool is based on another one called Prefast.

Coverage:

• display the portions of code that were executed by the unit-tests.

Lin Xu's powerpoint presentation: https://emea1.msteched.com/resources/presentations1/DVP304_Xu.pptx

It turns out that the unit-testing feature alone is also available in the Pro version.

Visual Studio Pro Academic on Amazon costs less than £120.

Tuesday - Designing for Testability

ARC307 - Designing for Testability: Bridging the Gap between Design and Testing in Object-Oriented Software

Speaker: Roy Osherove

Probably the most inspiring session so far. It struck a chord because I recently gave some serious thought to coding unit-tests and using TDD. What has been holding me back so far is that most unit-testing frameworks seem to be targeting the .NET framework as opposed to native C++.

For each piece of coded logic says Roy Osherove you should follow the PC-COF rules:

• Partial runs possible
• No need for Configuration
• Consistent pass/fail
• Order does not matter
• Tests must be Fast

Dependencies between objects should be based on interfaces (to enable creation of mock objects), which then raises the problem of dependency injection: how and when do you pass a concrete object A to an object B that expects interface A?

You can do injection in various ways:

• Constructor (forces you to pass a concrete object)
• Property (passing the concrete object is optional)
• Factory (passing the concreate object is done as part of a factory method)

It can become very clunky to do code injections for all objects unless you use inversion of control containers. Those containers hold associations between abstract interfaces and concrete objects.

Inversion of control frameworks:

• Spring.net (I met one of the authors of Spring.net at TechEd Orlando)
• Castle.Windsor
• StructureMap

Unit-testing tools:

• MSTest (the one integrated in Visual Studio Team System)
• Nunit
• TestDriven.NET
• Typemock isolator

Slides

Tuesday - Schema Compare in VSTS2008

TLA313 - End-to-End Database Development Using Microsoft Visual Studio Team System 2008 Database Edition

Brian Randell talked about how to manage change with SQL Server.

That was a useful session! I've been struggling with this problem for years with Oracle and SQL Server: you have to manually write DDL scripts when doing database upgrades. Those scripts make visualising changes difficult and reversing changes impossible unless you write undo scripts.

DBPro (VSTS 2008 Database edition) generates those DDL scripts automatically: you edit your schema offline within your solution (all under source control) then when you need to deploy the schema, DBPro compares the target DB model to the offline model and generates the scripts automatically.

So no need to store the DDL scripts in source control any more (ALTER statements to add a column for instance). What you store in source control is the declarative model (i.e. the CREATE statements for the successive versions of the table).

If a DBA makes a change in production, you can have DBPro compare the schemas and highlight the differences. You can then select an individual difference and bring it back into the offline model to keep it in sync.

Sounds brilliant.

By the way, this is available in VSTS2008 but works against SQL Server 2005. Brian did the whole demo against 2005 to make a point that there is nothing specific to SQL Server 2008.

Resources:

• Gert Drapers blog (Data Dude): http://blogs.msdn.com/gertd/
• Will take ages to download the trial version of DBPro from here
• The list of features in VSTS2008
• Power Tools for DBPro
  

Monday - Agile Development in VS2008 and VS2010

DVP03-IS - Agile development with Visual Studio

Interactive session with Stephanie Saad who demonstrated some features of VS2008/2010.

There are very interesting things for database development in the DB edition of Visual Studio Team System:

• DB refactoring: changing a table name automatically updates all dependencies
• DB deployment: you can generate a script that contains all differences between the current schema and the production schema.
• DB testing:
  C# Unit-tests for T-SQL stored procedures where you define setup, test and teardown
  Test data generation (that's really handy for the setup part of the unit tests)
  

Apparently the dev and db versions of Visual Studio Team System have been merged into one license for 2008 so if you buy one, you get the other one for free.

Other notes:

VSTS 2008

• see author/changes directly in code
• define check-in policies (to force you to pass unit-tests for instance)
• define triggers such as on check-in build
• setup build notifications popups (through powertools)
• code metrics: show the number of lines, cyclomatic complexity
• all of the above present in VSTS for DB, Team System Server is not required

Coming up in VSTS 2010

• Planning, task management: creation of user stories / tasks hierarchy, capacity planning, reports to show test results per user story / task.
• Gated check-in: define check-in conditions, for instance you can't check-in if it doesn't build or if the unit-tests don't pass

Monday - Keynote

The CCIB got much busier after lunch, delegates with orange badges running everywhere, and the auditorium was packed for the keynote.

The Keynote Session

Although Christopher Lloyd didn't appear on stage in a De Lorean as happened in TechEd 2007 in Orlando, this keynote was a pretty good one. Nothing about adding-business-value-by-leveraging-customer-statisfaction bs: Jason Zander knew his audience so he did a demo-based presentation, going through some of the new features of Visual Studio 2010, changing, building and running code.

To show that VS still provides support for unmanaged C++ he imported a VC6 implementation of Pong into VS2010 then

• sexed-it up with the MFC C++ ribbon classes,
• parallelised a lengthy for loop across 8 cores with parallel_for
  
• and eventually showed off the multi-touch capabilities of Windows 7 by moving both pong racquets with his fingers simultaneously, under a round of applause.

video of the keynote

Monday - Silverlight 2.0

I am in Barcelona for TechEd. The keynote is planned for the afternoon, no sessions until after the keynote so my morning will be spent at the self-paced labs.

I had planned to do some labs about multithreading in Visual Studio but it looks like they've gone from the schedule!

Nevermind, I just did a lab on Silverlight Foundations lab (WUX11-HOL)

Notes from the lab:

• Silverlight projects produce xap files, wich are just zip files that contain dlls and a manifest.
• When you create a Silverlight project in Visual Studio, it generates a new ASP.NET project by default to host the silverlight control.
• Layout: VS contains some sort of read-only designer to preview the results of changes to the XAML file. Editing the layout WYSIWYG-style is done in Expression Blend.
• Blend much more powerful than Winforms editor: add rectangles, ellipses, bezier curves, clever resize/skew commands... Apparently you can switch back and forth from Blend to Visual Studio using the same solution/project files.
• Expression Design is used to build vector graphics that produce a XAML file.

I'm considering Silverlight to build business apps so I also started the Communications and Data with Microsoft Silverlight 2.0 (WUX14-HOL)

Free Wifi in Istanbul

It seems that in Istanbul, many coffee/restaurant chains provide free Wifi access.

• Palladium shopping center:
  

Cafe Nero

Midpoint restaurant

SushiCo restaurant

• Capitol shopping center:
  

Kitchenette

Sugar Sync

Currently visiting a friend in Istanbul who showed me a useful on-line storage tool: for $50 a year you can store 26GB of data online.

• your data is encrypted with AES, which is the least you should ask for, really.
• the really nice thing: it comes with a client that automatically syncs your PC folders with the remote storage.
• the sync works accross many PCs, which is cool as well.

That means you don't need to schedule back-ups of your important files, this is an automatic instant off-site backup (much more useful than my daily incremental Vista-based on-site backup).

An there's more:

• you can access your files online over the web.
• you can, and that's really convenient, send links to large files per email to other people. The recipient doesn't need to login to retrieve the file, he just clicks it. You get notified when he receives it.

Timezone conversion

How to convert a local time from a given time zone into UTC?

For instance how to convert the time in New York (Time zone: Eastern Standard Time) into UTC time, taking into account the fact that daylight saving is currently active in New York (until November 2)?

Sounds trivial? Well it's not, at least not until .NET Framework 3.5.

The standard time zones are listed in the Windows registry: each time zone string is a key. The values under that key describe the timezone. For instance the "Eastern Standard Time" key contains info such as UTC offset and daylight saving start/end dates. Unless you use 3.5, you have to write code yourself to retrieve that info from the registry.

It looks like the simplest way to do it is to use System.TimeZoneInfo in .NET 3.5.

Example:

DateTime time = new DateTime(2008, 11, 7, 11, 46, 0, 0);
TimeZoneInfo info = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
DateTime utcTime = TimeZoneInfo.ConvertTimeFromUtc(time, info);

Console.WriteLine("New york time: " + time.ToString());
Console.WriteLine("UTC time : " + utcTime.ToString());

Some resources:

• General info about time zones: http://www.timeanddate.com/
• Upcoming daylight saving changes: http://www.timeanddate.com/time/dst2008b.html

Curly Braces

Visual Studio Add-in to highlight curly braces:

http://www.codeproject.com/KB/macros/matchingbraces.aspx