dotNed

[Edit: ik heb even de layout aangepast. Excuses voor de eerste post: het leek helemaal nergens naar. Dit moet beter zijn :-)]

Zo af en toe vind ik een oplossing voor een probleem dat ik niet heb.

Herken je dat? Je zit bij een demo van Microsoft en je ziet nieuwe technologie. Het ziet er allemaal fantastisch uit, het spettert gewoon van het scherm, je voelt de adrenaline door je lijf razen en het enige waar je aan kunt denken is: "Dit is gaaf! Dit ga ik meteen toepassen!". De volgende dag kom je op je werk en denkt "Ja…. Maar wat moet ik er nou mee?".

Ik zie veel nieuwe technologieën, ik ben bij veel demo's, lees veel boeken, blogs en websites en heb bovenstaand gevoel dus heel vaak. Leuke techniek, absoluut fantastisch maar wat moet ik er nu in de praktijk mee?

Een tip: negeer dat gevoel. Als je de volgende keer bij een demo bent (bijvoorbeeld op de komende SDE op 23 maart te Bunnik, of liever nog: een avond eerder bij de demonstraties van Rolf bij dotNed) en je hebt dat gevoel van "Wauw, maar wat moet ik er nou mee?", probeer dan zoveel mogelijk van de geboden info op te nemen. Geloof me: vroeg of laat kom je in een situatie waarop je je ineens realiseert dat dit nou precies zo'n probleem is waarbij die ene oplossing voor jou de oplossing is! Als je die kennis paraat hebt, of je weet in ieder geval dat er een oplossing bestaat voor het probleem, zul je in de ogen van je collega's nog meer alwetend lijken dan je al bent.. Geintje.

Hetzelfde overkwam mij tijdens de eerste demo die ik zag van embedded CLR in SQL Server 2005. Dat is dus al weer 2 jaar geleden, maar ik weet nog goed hoe ontzettend enthousiast ik was. Wauw! C# code in mijn storedprocs! Geweldig.

Echter, al gauw kwam ik tot de conclusie dat een database voor mij niets anders was dan een black box waar data in ging en met een beetje mazzel ook weer uit kwam (als ik niet geplaagd werd door dbNull errors). Meer deed ik eigenlijk niet met databases. In de loop van de tijd echter deed ik meer en meer met databases en mijn kennis van SQL2005 nam hand over hand toe. Toch zag ik nog steeds geen praktische toepassing voor de CLR in SQL Server. Tot aan mijn laatste project…

Ik was bij een bedrijf gedetacheerd als architect / lead developer. Een ontzettend leuke rol: ik kon de architectuur bepalen en het ook zelf nog toepassen. Uiteraard deed ik zelf alle leuke dingen en liet ik de minder interessante (voor mij dan) taken over aan de rest van het team. Dit project was een reissite, een plek waar mensen informatie over vakanties kunnen uitwisselen. Zoals je zult begrijpen, ging veel van de data over bestemmingen. Niet alleen de naam, land, stad en dat soort dingen staan in het systeem, maar ook de temperaturen, culturele info en waar de bestemming zich nou precies bevond. Dat laatste werd keurig in de database opgeslagen met de lengte- en breedtegraad coördinaten: die hadden we dan weer nodig om op een Google-Maps kaartje te plotten. Erg leuk allemaal.

In de specificaties echter stond het volgende stukje:

"Nadat gebruikers een bestemming gekozen hebben, moeten ze op de kaart de 20 dichtstbijzijnde locaties kunnen zien."

Ah. De 20 dichtstbijzijnde locaties. Welke zijn dat? Ik kon niet uitgaan van land informatie: als ze Maastricht kozen zou Luik dichterbij zijn dan Groningen. Ik moest dus met de coördinaten aan de gang gaan. De oplossing lag voor de hand: selecteer de gekozen bestemming en zoek de 20 bestemmingen die het meest dicht bij liggen. Mmmm… dat helpt niet echt.

Het eerste probleem was het uitrekenen van afstanden tussen locaties. Iemand in het team riep "Oh da's simpel: gewoon Pythagoras er op los laten. We hebben de coördinaten toch?" Dat is wel zo, maar hij ging volledig voorbij aan het feit dat de stelling van Pythagoras werkt in 2 dimensies, maar sinds Columbus weten we allemaal dat de aarde niet plat is maar een bolletje. Met andere woorden: a² = b² + c² werkt hier niet. (voor de twijfelaars: kijk eens op een kaart hoe de kortste weg is van Amsterdam naar Tokio, en vergelijk die lijn dan eens met de route die een vliegtuig neemt: die is echt anders).

Na even zoeken kwam ik de volgende code tegen:

    1 private static double GetDistance(double longOne, double latOne, double longTwo, double latTwo)

    2 {

    3     double theta = latOne - latTwo;

    4     double dist = Math.Sin(ConvertDegreesToRad(longOne)) *

    5         Math.Sin(ConvertDegreesToRad(longTwo)) +

    6         Math.Cos(ConvertDegreesToRad(longOne)) *

    7         Math.Cos(ConvertDegreesToRad(longTwo)) *

    8         Math.Cos(ConvertDegreesToRad(theta));

    9 

   10     dist = Math.Acos(dist);

   11     dist = ConvertRadToDegrees(dist);

   12     dist = dist * 60 * 1.1515;

   13     dist = dist * 1.609344;

   14     return (dist);

   15 }

   16 

   17 private static double ConvertDegreesToRad(double deg)

   18 {

   19     return (deg * Math.PI / 180.0);

   20 }

   21 

   22 private static double ConvertRadToDegrees(double rad)

   23 {

   24     return (rad / Math.PI * 180.0);

   25 }

Dit is even iets anders dan Pythagoras, maar het werkt wel. Ik ga niet eens proberen het te begrijpen.

De oplossing lag voor de hand. Denk aan het volgende stukje logica:

1. Haal de longitude en lattitude op van de gekozen bestemming (locatie A)
2. Haal de longitude en de lattitude op van de andere bestemmingen (locatie B)
3. Bereken de afstand tussen A en B
4. Geef de top 20 van de gevonden afstanden terug (nou ja, de onderste top 20 anders krijg je de locaties die het meest ver weg liggen)

Dat gaat dus ook niet werken. De database bevat duizenden bestemmingen, om die allemaal in de website binnen te halen en uit te rekenen zou het hele systeem plat liggen. Dat moet slimmer kunnen. En toen ging me een lichtje op. Had ik niet eens een demo gezien van C# code in SQL Server? Op die manier zou ik de berekening in de database kunnen laten plaatsvinden. Dan hoeft die data niet vanuit de database server overgehaald te worden naar de webserver, maar alleen de relevante informatie komt dan over! Briljant! Ga ik doen! Maarre.. hoe werkte dat ook al weer?

Om je het denkwerk te besparen geef ik je hier mijn oplossing:

Begin in Visual Studio een nieuw Database Project. Geef hem een referentie naar een database, met daarin een tabel met de info die we nodig hebben. Ik heb even als voorbeeld een tabel met de naam Places die er als volgt uitziet:

Column    Type
Id        Int (Identity, PK)
Name        NVarchar(50)
Longitude    double
Lattitude    double

(de locatie is opgenomen als 2 doubles: ten eerste vergemakkelijkt dat het rekenen, ten tweede verlangt Google Maps ze op die manier.)

Ik heb daar even wat test data ingezet: het adres van Detrio (mijn werkgever) en het adres van het Witte Huis in Washington:

We hebben nu dus test data.

In ons database project (die ik even dotNedSQL heb genoemd) voegen we een nieuw item toe: een User-Defined Function met de naam CalcDistance(). Visual Studio reageert met de volgende code in CalcDistance.cs

    1 using System;

    2 using System.Data;

    3 using System.Data.SqlClient;

    4 using System.Data.SqlTypes;

    5 using Microsoft.SqlServer.Server;

    6 

    7 public partial class UserDefinedFunctions

    8 {

    9     [Microsoft.SqlServer.Server.SqlFunction]

   10     public static SqlString CalcDistance()

   11     {

   12         // Put your code here

   13         return new SqlString("Hello");

   14     }

   15 };

Mooi. Dat lijkt er al op. We kunnen dit compilen, deployen (rechtsklik op het project en kies 'deploy') en runnen! Uhm.. runnen? Hoe dan? Nou, in het project dat je aangemaakt hebt, zit een file genaamd Test.Sql (in de folder Test Scripts). Hierin kun je SQL statements plaatsen die je functie, storedprocs en wat niet meer testen. Standaard staan er een aantal voorbeelden in die file, dus daar kom je wel uit. In ons voorbeeld passen we de Test.Sql even aan (en met rechtsklikken op die file zorgen we er voor dat we "Set as default debug script" gekozen hebben….). Onze test.sql ziet er nu als volgt uit:

select dbo.CalcDistance();

Als we nu op F5 drukken, wordt dit script uitgevoerd, met het volgende resultaat:

Debugging script from project script file.
The thread 'wendy\sqlexpress [53]' (0x5c68) has exited with code 0 (0x0).
The thread 'wendy\sqlexpress [53]' (0x5c68) has exited with code 0 (0x0).
The thread 'wendy\sqlexpress [53]' (0x5c68) has exited with code 0 (0x0).
Auto-attach to process '[1388] [SQL] wendy' on machine 'wendy' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32
mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols.
Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Column1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hello
No rows affected.
(1 row(s) returned)
Finished running sp_executesql.
'sqlservr.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'dotNedSQL', No symbols loaded.
Auto-attach to process '[1388] sqlservr.exe' on machine 'wendy' succeeded.
The thread 'wendy\sqlexpress [53]' (0x5c68) has exited with code 0 (0x0).
The program '[1388] [SQL] wendy: wendy\sqlexpress' has exited with code 0 (0x0).
The program '[1388] sqlservr.exe: Managed' has exited with code 259 (0x103).

Ergens midden in zien we "Hello" staan. Het werkt! Eureka! Ok, nou nog even iets nuttiger maken.

We passen de CalcDistance even aan. Hij geeft een double terug, en krijgt twee parameters, namelijk de unieke sleutels van de te vergelijken bestemmingen.

    9 [Microsoft.SqlServer.Server.SqlFunction]

   10 public static double CalcDistance(int firstId, int secondId)

   11 {

   12     if ((firstId == 0) || (secondId == 0))

   13         throw new ArgumentException("Ids kunnen niet 0 zijn.");

   14 

   15     // voor testen... 

   16     return 0d;

   17 } 

Vergeet niet je test code aan te passen:

select dbo.CalcDistance(1,2);

Zodat deze de afstand tussen punt 1 en punt 2 aangeeft. (als je dit nu runt, is de afstand… 0)

De volgende stap is het ophalen van de coordinaten van de plaatsen behorende bij de twee punten. De code wordt als volgt:

   10 public static double CalcDistance(int firstId, int secondId)

   11 {

   12     if ((firstId == 0) || (secondId == 0))

   13         throw new ArgumentException("Ids kunnen niet 0 zijn.");

   14 

   15     using (SqlConnection connection = new SqlConnection("context connection=true"))

   16     {

   17         connection.Open();

   18         try

   19         {

   20             using (SqlCommand cmd = new SqlCommand("SELECT Longitude, Lattitude FROM Places WHERE Id=@Id", connection))

   21             {

   22                 cmd.Parameters.Add("@Id", SqlDbType.Int);

   23 

   24                 double longOne = 0d;

   25                 double lattOne = 0d;

   26                 string paramName = "idOne";

   27                 ReadCoordinates(firstId, cmd, ref longOne, ref lattOne, paramName);

   28 

   29                 double longTwo = 0d;

   30                 double lattTwo = 0d;

   31                 paramName = "idTwo";

   32                 ReadCoordinates(secondId, cmd, ref longTwo, ref lattTwo, paramName);

   33 

   34                 return GetDistance(longOne, lattOne, longTwo, lattTwo);

   35             }

   36         }

   37         finally

   38         {

   39             connection.Close();

   40         }

   41     }

   42 }

   43 

   44 private static void ReadCoordinates(int locationId, SqlCommand cmd, ref double longOne, ref double longTwo, string paramName)

   45 {

   46     // Read the coordinates 

   47     cmd.Parameters["@Id"].Value = locationId;

   48     SqlDataReader reader = cmd.ExecuteReader();

   49     try

   50     {

   51         if (!reader.Read())

   52             throw new ArgumentOutOfRangeException(paramName, "Invalid ID");

   53 

   54         longOne = reader.GetDouble(0);

   55         longTwo = reader.GetDouble(1);

   56     }

   57     finally

   58     {

   59         reader.Close();

   60     }

   61 }