Navigatie

Contact

Send mail to the author(s) E-mail

View Richard Soeteman's profile on LinkedIn

RSS 2.0 | Atom 1.0 | CDF

Archief

Categorieën

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Sign In

Zoeken

# Tuesday, 19 December 2006
Tuesday, 19 December 2006 20:13:45 (GMT Standard Time, UTC+00:00) ( )

This Blog is not active anymore, further posts will be available on my company website. Read new posts here

Ik heb met tot afgelopen week vaak afgevraagd waarom ik ooit gebruik zou maken van CLR integratie in SQL Server 2005. Totdat ik een query zag waar een Regular Expression meer op zijn plaats was geweest. Regular Expressions kent T-SQL niet, maar middels CLR integratie is dit op te lossen.

In onderstaand voorbeeld is men vergeten emailadressen te valideren. Daardoor staan er foutief geformateerde emailadressen in de database. Middels het gebruik van een Regular Expression gaan we deze in de database opzoeken. Onderstaande afbeelding toont alle data in de contacts tabel.

Code

Onderstaande code bevat de functie IsValidEmailAdress. Deze functie wordt straks ook binnen SQL Server gebruikt om te controlleren of het een geldig emailadres betreft.  De code zelf maakt gebruik van een regular expression om te bepalen of het emailadres geldig is.

using System;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public class DatabaseFunctions
{
    [SqlFunction( DataAccess = DataAccessKind.None , IsDeterministic=true) ]
    public static bool IsValidEmailAddress(string emailAddress)
    {
        Regex regex = new Regex(@"^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$");
        return regex.IsMatch(emailAddress);
    }
}

Drie zaken die opvallen:

  1. We maken gebruik van de Microsoft.SqlServer.Server namespace. De Microsoft.SqlServer.Server bevat alle onderdelen die we nodig hebben voor CLR integratie. 
  2. We maken gebruik van het Attribuut SqlFunction. Aan dit attribuut geven we de volgende parameters mee :
    • DataAccess. Deze kan de waarde DataAccessKind.None, of DataAccessKind.Read bevatten. We voeren geen database acties uit, dus gebruiken we DataAccessKind.None 
    • IsDeterministic. Indien True wordt de output gecached op basis van de inputparameters en database state. We willen performance dus zetten we deze op true
  3. IsValidEmailAddress is static. Functies die binnen SQL Server worden gebruikt moeten static zijn.

Gebruik van de code in SQL Server

CLR integratie staat default uit. Middels onderstaande statements zetten we dit aan. Mocht je dit trouwens vergeten, geeft SQL Server zelf aan dat CLR integratie aangezet moet worden en ook hoe je dat moet doen.

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;

Vervolgens registreren we onze Assembly.

CREATE ASSEMBLY CLRDatabaseFunctions FROM 'D:\CLRDatabaseFunctions.dll' WITH PERMISSION_SET = SAFE

We kunnen de functie IsValidEmailAddress niet direct aanroepen, daarom maken we gebruik van onderstaande functie(SQL) welke de CLR functie IsValidEmailAddress  wel aan kan roepen.

CREATE FUNCTION IsValidEmailAddress(@emailAddress nVarchar(255)) RETURNS bit
AS EXTERNAL NAME CLRDatabaseFunctions.DatabaseFunctions.IsValidEmailAddress

Nu kunnen we de foutieve emailadressen opvragen:

Select Contacts.* from Contacts WHERE  dbo.IsValidEmailAddress(Contacts.EmailAddress) = 0 

Dit geeft als resultaat het emailadres wat niet correct was ingevoerd.

 

Downloads en meer info

Via onderstaande links zijn de gebruikte voorbeelden te downloaden:

Verder kan je via onderstaande links meer informatie over dit onderwerp vinden:

Comments [0] | | #