La Voie Du Code

A program I am working on heavily uses XML deserialization to build object models from XML files.

I didn’t have to write the deserialization code thanks to the excellent tool provided by Microsoft called XML Schema Definition Tool (Xsd.exe). This tool generates a bunch of C# classes that capture the data structure described in a given schema file (XSD files).

The generated code do have a few shortcomings though, like the use of arrays to handle multiple elements or the generation of FxCop unfriendly code. Jonathan ‘Peli’ de Halleux has already solved most of these problems in his XsdTidy, XSD mapping beautifier tool.

In my case, XsdTidy didn’t really work out of the box. My problems where slightly different:

• Use generics for multiple elements instead of array, e.g. List<sometype> instead of SomeType[].
• Use a tool runnable from the command line to integrate easily in my automated build system.

I could have hacked XsdTidy to solve these problems, but instead I went for the bad and the ugly: a quick and dirty regex script. I could have used perl, python or any other language that has a reasonably powerful regex engine, but I really have a soft spot for ruby. So here it goes, tweakXsd.rb, a quick and dirty, one-off job to tweak an xsd-generated class file to use generics.

Everytime I change my XSD schema files, I run a the MS-DOS script like the one below to regenerate C# classes, et voilà!

    REM 
    REM This script generates Foo.cs from Foo.XSD
    REM
    SET XSD="C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\bin\xsd.EXE"
    SET RUBY=C:\ruby\bin\ruby.exe
    %XSD% /c /namespace:My.Namespace /order Foo.xsd 
    %RUBY% tweakXsd.rb < Foo.cs > Foo.cs.tmp
    del Foo.cs
    ren Foo.cs.tmp Foo.cs

As a bonus, the script tweaks elements defined as XmlTextAttribute to string properties instead of string[]. To produce a XmlTextAttribute compatible element, use the following schema declaration:

 <xs:element name="elementWithText">
   <xs:complexType mixed="true" />
 </xs:element>

<french>

L’outil Xsd.exe proposé par Microsoft pour la création de classes de désérialisation XML à partir d’un fichier de schéma XML est réellement très utile. Le seul problème c’est que le code généré utilise des tableaux pour permettre l’accès aux collections d’éléments. Pour un projet je souhaitais pouvoir accéder à ces collections en utilisant des collections génériques de type List<T>. Après avoir écarté l’outil XsdTidy j’ai codé moi-même un script en mode “porcho” (cf. le Dictionnaire vosgien-français).

Le script tweakXsd.rb prend en entrée le résultat de “xsd.exe /c” et produit une version modifiée qui utilise les génériques et expose les attributs taggés XmlTextAttribute en tant que propriétés de type string au lieu de string[].

Le script MS-DOS cité plus haut montre un exemple d’automatisation du script.

</french>

Straight port from VBA routines written by Dejan Mladenovic.

Very useful to round to a quarter of a day for example. The following query should yield 12.75:

   SELECT dbo.FN_RoundNear(12.63, 0.25)

You can download the script here.

<french>

Portage vers SQL Server d’un code VBA qui permet d’arrondir un nombre à un multiple donné. C’est le genre de truc qu’on faisait au collège en 6ème, et pourtant c’est bien pratique de le trouver pré-mâché!

P.S.: la syntaxe de TSQL est vraiment laide à pleurer.

</french>

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION FN_RoundUpDown
(
   @nb FLOAT,
   @factor FLOAT
)
RETURNS FLOAT
AS
BEGIN
-- ***********
-- Name:      FN_RoundUpDown (Function)
-- Purpose:   round @nb to @factor, up or down
-- Inputs:    @nb = number to round
--            @factor  = rounding precision
--           +@factor = rounds UP
--           -@factor = rounds DOWN
-- Example:   FN_RoundUpDown(5.12,+0.25) = 5.25
--            FN_RoundUpDown(5.12,-0.25) = 5.00
-- Output:    @nb rounded UP/DOWN
-- ***********
  DECLARE @result FLOAT
  DECLARE @temp_nb FLOAT
  SET @temp_nb = @nb / @factor
  IF (FLOOR(@temp_nb) - @temp_nb) = 0
    SET @result = @nb
  Else
    SET @result = FLOOR((((@nb + (2 * @factor)) / @factor) - 1)) * @factor
  RETURN @result
END
GO
CREATE FUNCTION FN_RoundNear
(
   @nb FLOAT,
   @factor FLOAT
)
RETURNS FLOAT
AS
BEGIN
-- ***********
-- Name:      FN_RoundNear (Function)
-- Purpose:   rounds @nb to the nearest fraction equal 
--            @factor
-- Inputs:    @nb - number to round
--            @factor - the fraction used as measure of 
--            rounding
-- Example:   FN_RoundNear(53,6) = 54
--            FN_RoundNear(1.16,0.25) = 1.25
--            FN_RoundNear(1.12,0.25) = 1.00
--            FN_RoundNear(1.125,0.25)= 1.25
-- Output:    @nb rounded to nearest
--            multiple of @factor.
-- ***********
  DECLARE @result FLOAT
  DECLARE @decValue FLOAT
  DECLARE @intX INT
  DECLARE @divX FLOAT

  SET @divX = @nb / @factor
  SET @intX = FLOOR(@divX)
  SET @decValue = @divX - @intX

  IF @decValue >= 0.5
    SET @result = @factor * (@intX + 1)
  Else
    SET @result = @factor * @intX  

  RETURN @result
END
GO