Data Provider Factories in .Net 2.0

Een van de nieuwe eigenschappen van ADO.NET 2.0 zijn de DbProviderFactory classes en de Db. abstract base classes. Met deze nieuwe objecten wordt het een stuk makkelijker om database onafhankelijke code te schrijven. In dit artikel wil ik laten zien hoe dit kan worden aangepakt en wat voor problemen er over blijven om op te lossen.

Wat zijn Data Provider Factories?

Een data provider class is een object factory die wordt gebruikt om alle bij elkaar horende database objecten zoals SqlConnection’s en SqlCommand’s te maken. Zodra bekend is welk data provider factory object moet worden gebruikt, kan de rest van de applicatie dit object hierna gebruiken om alle andere bijbehorende database objecten te maken zonder precies te weten welke objecten worden gemaakt.

Omdat bij het gebruik van een factory object niet bekend is wat het exacte object type is dat zal worden gemaakt, zijn de return types altijd van een abstract type zoals DbConnection en DbCommand in plaats van SqlConnection en SqlCommand. De nieuwe data provider factory classes zijn overigens zelf ook allemaal afgeleid van de abstract class System.Data.Common.DbProviderFactory. Hiervan is per categorie, zoals SQL, OleDB, ODBC en Oracle, een subclass gemaakt. De DbDataProvider class zelf definieert een aantal abstracte functies om de verschillende objecten die nodig zijn te maken.

Tabel 1: De DbDataProvider functies.

Zodra de juiste DbProviderFactory is gemaakt, kunnen bovenstaande functies gebruikt worden om de verschillende objecten te maken in plaats van de New operator. Om te bepalen welke DbProviderFactory classes bekend zijn en om dit object zelf te maken is de System.Data.Common.DbProviderFactories class gemaakt.

Tabel 2: De DbProviderFactories functies.

Om te bepalen welke DbProviderFactories er voor een applicatie bekend zijn, wordt er in de machine.config en in de app.config van de applicatie zelf gezocht naar de sectie. Deze twee configuratie bestanden worden gecombineerd tot de DataTable die GetConfigTable() teruggeeft.    

In de config.app is het mogelijk om met “add”, “remove” of “clear” elementen de DbProviderFactories uit de machine.config aan te passen.

Een element verwijdert alle bekende DbProviderFactory registraties.

Een element verwijdert een specifieke factory, in dit geval de SQL Server Ce client.

Het invariant attribuut is verplicht en is de sleutel in de collectie.

Naast een “clear” en “remove” kan men met een “add” element nieuwe factory classes toevoegen.

  name="The Problem Solver Data Provider"
  invariant="TheProblemSolver.Data.SqlClient"
  description=
    "The Problem Solver.Net Framework Data Provider
      for SqlServer"
  type="TheProblemSolver.Data.SqlClient.SqlClientFactory,
        TheProblemSolver.Data,
        Version=1.0.0.0, Culture=neutral,
        PublicKeyToken=b77a5c561934e089"
/>

Listing 1: Het toevoegen van een eigen DbProviderFactory.

Het ShowProviders formulier in de voorbeeldcode toont de voor mijn applicatie bekende DbProviderFactory objecten. Hierbij is de config.app gebruikt om de Microsoft.SqlServerCe.Client te verwijderen en de TheProblemSolver.Data.SqlClient toe te voegen. Deze laatste factory is alleen een voorbeeld en is niet in de voorbeeldcode opgenomen.

Fig. 1: Een lijst van de DbProviderFactories op mijn machine.

Waarom zijn er nieuwe abstract data classes gekomen?

Met versie 1 van het .Net framework hadden de ADO.NET classes geen gemeenschappelijke base classes, maar waren er een aantal interfaces gedefinieerd. Zo was de class hiërarchie voor een SQLDataReader class:

• System.Data.SqlClient.SqlDataReader
• System.MarshalByRefObject
• System.Object

Om generieke code mogelijk te maken implementeerde de SQLDataReader class ook het System.Data.IDataReader interface. Deze aanpak kent het fundamentele probleem dat een interface niet meer mag worden aangepast als dat eenmaal gepubliceerd is. Het gevolg hiervan is dat eventuele uitbreidingen op de classes zelf niet in de interfaces opgenomen kunnen zijn en dat deze dus ook niet kunnen worden gebruikt in generieke code. Een voorbeeld hiervan is de HasRows property die in versie 1.1 is toegevoegd aan de SQLDataReader en alle vergelijkbare DataReader classes. Deze is niet beschikbaar in de IDataReader interface en daarmee eveneens niet generiek bruikbaar. Om dit probleem op te lossen is besloten om in versie 2.0 een extra abstract class toe te voegen in de class hiërarchie, de DbDataReader. Dit gegeven resulteert in de onderstaande nieuwe class hiërarchie voor een SQLDataReader class in 2.0:

• System.Data.SqlClient.SqlDataReader
• System.Data.Common.DbDataReader
• System.MarshalByRefObject
• System.Object

Als een reeds gepubliceerd interface wordt gewijzigd, hebben alle leveranciers van componenten die de interface implementeren een probleem

Waarom mag een gepubliceerd interface niet gewijzigd worden?

Als een reeds gepubliceerd interface wordt gewijzigd, hebben alle leveranciers van componenten die de interface implementeren een probleem. Ze moeten nu per versie van de interface een nieuwe assembly compileren en verspreiden. Als je namelijk de assembly compileert met de oude versie van het framework maar deze gebruikt in de nieuwe versie, kan men alle functies van de interface aanroepen, ook de functies die nog niet bestonden, zonder dat de compiler je hiervoor kan waarschuwen. Bij een base class kan men de assembly in de oude versie van het framework compileren en in de nieuwe versie gebruiken omdat alle functies er niet zijn. De nieuwe functie kan gewoon niet zijn overruled waardoor altijd die van de base class wordt aangeroepen.

Database-onafhankelijke code schrijven, een eenvoudig voorbeeld

Nu we de nieuwe classes hebben gezien, kunnen we deze ook in de praktijk gebruiken. Als eerste heb ik een eenvoudig voorbeeld gemaakt waarin met een database connectie-string en een SQL select statement wat data wordt opgehaald. Van de connectie-string zijn drie varianten aanwezig. De eerste is een native SQL Server connectie-string voor de Northwind database, de tweede een OleDb connectie-string naar dezelfde SQL server database, terwijl de derde een OleDb connectie-string is voor de Northwind database die met Visual FoxPro 8 wordt meegeleverd. Het SQL select statement dat wordt uitgevoerd is een simpele “Select * From Customers”.

Fig. 2: De Klanten-tabel uit SQL Server

De code gaat nu als eerste met alle DbProviderFactory classes proberen om een database-connectie te openen. Hiertoe wordt onderstaande code gebruikt:

Dim factory As DbProviderFactory = Nothing
Dim conn As DbConnection = Nothing
Dim cmd As DbCommand = Nothing
Dim dtFactories As DataTable =_
  DbProviderFactories.GetFactoryClasses()

For Each drFactory As DataRow In dtFactories.Rows
  Try
    factory = DbProviderFactories.GetFactory(drFactory)
    conn = factory.CreateConnection()
    conn.ConnectionString = cboConn.Text
    conn.Open()
    conn.Close()
    Exit For
  Catch ex As Exception
    conn = Nothing
    factory = Nothing
  End Try
Next

Listing 2: Het bepalen van de juiste DbProviderFactory.

De code gebruikt als eerste de DbProviderFactories.GetFactoryClasses() om een tabel op te halen met alle mogelijke data-providers. Vervolgens gaan we daarmee één voor één proberen de opgegeven connectie te openen. Indien dit faalt, treedt een exceptie op. Ik test hier op een generieke exceptie in plaats van de specifieke DbException, omdat niet iedere exceptie die hierbij kan optreden, is afgeleid van DbException. Indien deze operatie slaagt, wordt de lus beëindigd met een variabele “factory” die wijst naar een geschikt DbProviderFactory object om verder mee te werken.

Fig. 3: De zelfde klantentabel maar nu via OleDb uit Visual FoxPro

Nu we de juiste data-provider hebben bepaald, kunnen we een DbCommand- en een DbDataAdapter-object aanmaken en daarmee een DataTable vullen. Onderstaande code voert dit uit. Als bonus wordt er op het scherm nog een TextBox gevuld met de class naam van de gebruikte data provider.

If conn IsNot Nothing AndAlso factory IsNot Nothing Then
  txtProvider.Text = factory.ToString()
  cmd = factory.CreateCommand()
  cmd.CommandText = txtSQL.Text
  cmd.Connection = conn
  Dim da As DbDataAdapter
  da = factory.CreateDataAdapter
  da.SelectCommand = cmd
  da.Fill(dtData)
Else
  txtProvider.Text = "Geen provider gevonden"
End If

dgvData.DataSource = dtData

Listing 3: Gebruik van het bepaalde DbProviderFactory object

Bovenstaande code geeft aan hoe makkelijk het is om op basis van een connectie-string van database-provider te wisselen.

Database onafhankelijke code schrijven, het complete verhaal

Het vorige voorbeeld laat zien hoe eenvoudig het kan zijn, maar helaas is het in de praktijk niet helemaal zo simpel. Het eerste probleem zit in de SQL SELECT die moet worden uitgevoerd en het feit dat elke database-server zijn eigen specifieke syntax kent. Om dit probleem op te lossen zijn er in principe drie mogelijkheden:

1. Gebruik alleen standaard ANSI SQL die door iedere database-provider wordt ondersteund en verwijder alle andere providers met behulp van de sectie in de config.app.
2. Maak een class die de SQL SELECT kan ontleden en de database specifieke delen kan ombouwen voor alle andere databases.
3. Voeg een systeem toe waar ontwikkelaars zelf implementatie specifieke SQL toe kunnen voegen per database server.

Optie 1 is veruit de meest eenvoudige oplossing maar beperkt wat men kan doen, aangezien men slechts de grootste gemene deler qua syntax kan gebruiken. Optie 2 is zeer arbeidsintensief maar is ook een heel mooie class om te hebben, en eventueel te verkopen. Optie 3 is in de meeste gevallen de meest praktische oplossing. In dit geval kunnen de ontwikkelaars gewoon extra SQL SELECT ‘s opgeven voor de verschillende SQL-dialecten indien nodig.

Welke optie men ook kiest, er blijft een tweede probleem over, en wel dat van de SQL-parameters. Bij sommige providers, zoals OleDb, worden parameters opgegeven door middel van een “?“ en de volgorde in de parameters-collectie. Andere providers gebruiken juist een naam zoals @CustomerId in het geval van SQL Server of :CustomerId in het geval van Oracle. In deze gevallen doet de volgorde in de parameters-collectie er niet toe.

In de codevoorbeelden heb ik een begin gemaakt van een class die problemen met parameters oplost. Deze class heb ik CommandBuilder genoemd en gebruikt in het voorbeeld formulier ShowDataParameters. Om met de class te werken wordt er eerst een object van gemaakt waarbij de constructor de connectie-string verwacht die wordt gebruikt om de database te benaderen. Vanuit de constructor wordt de functie GetFactory() aangeroepen die op de eerder beschreven manier een DbProviderFactory object maakt door de opgegeven connectie-string te testen. Nadat dit heeft plaatsgevonden kan het CommandBuilder-object worden gebruikt om een SQL-commando uit te voeren.

Fig. 4: Een SQL select met parameters.

In het voorbeeld formulier wordt in de cmdLoad_Click() functie de data geladen. Zoals eerder beschreven wordt eerst een CommandBuilder-object gemaakt met behulp van de connectie string. Vervolgens wordt een parameter-collectie gebouwd. Op dit moment weten we nog niet wat voor provider en dus wat voor parameter-collectie zal worden verwacht. Daarom vullen we een HashTable met de parameters. Op deze manier hebben we een generieke “sleutelwaarde”-collectie waar we snel in kunnen zoeken.

Private Sub cmdLoad_Click(_
  ByVal sender As System.Object,_
  ByVal e As System.EventArgs) Handles cmdLoad.Click

  Dim builder As New CommandBuilder(cboConn.Text)
  Dim cmd As DbCommand
  Dim da As DbDataAdapter
  Dim dt As New DataTable
  Dim params As New Hashtable
  params.Add("@Country", "USA")
  params.Add("@Region", "WA")

  cmd = builder.CreateCommand(txtSQL.Text, params)
  txtExecuted.Text = cmd.CommandText

  da = builder.CreateDataAdapter()
  da.SelectCommand = cmd

  da.Fill(dt)

  dgvData.DataSource = dt
End Sub

Listing 4: De cmdLoad_Click() functie

In het SQL commando zelf schrijven we de parameters alsof we SQL Server gebruiken en laten de CommandBuilder dit aanpassen indien nodig. Voor het testdoeleind heb ik bewust de volgorde van de parameters in de HashTable en in de SQL code omgedraaid.

Select * From Customers Where Region = @Region
  and Country = @Country

Listing 5: Het uit te voeren SQL commando.

Met het CommandBuilder-object en het SQL-commando wordt een DbCommand-object gemaakt door de CreateCommand functie aan te roepen met het SQL-commando en de HashTable gevuld met de parameters. De CreateCommand-functie roept eerst de CreateCommand functie van het factory object aan om een echt DbCommand-object aan te maken en de CreateConnection om de bijbehorende database connectie te vullen, tot zover niets nieuws. Vervolgens wordt de CommandBuilder FixCommandText functie aangeroepen om de syntax van het SQL-commando te corrigeren en de bijbehorende parameter collectie te vullen.

Public Function CreateCommand(_
  ByVal sql As String, _
  ByVal params As Hashtable) As DbCommand

  Dim cmd As DbCommand = Nothing

  cmd = m_Factory.CreateCommand()
  cmd.Connection = CreateConnection()
  cmd.CommandText =_
    FixCommandText(sql, cmd.Parameters, params)

  Return cmd
End Function

Listing 6: De CreateCommand functie.

De FixCommandText functie is waar het meeste werk plaatsvindt. Eerst wordt gekeken naar het type factory dat wordt gebruikt. Helaas zijn eigenschappen als de positionele plaatsing of een  prefix karakter geen properties van het factory object. Dit moet men dus eerst zelf bepalen. Zodra dit is vastgesteld, hebben we de keuze tussen parameters op basis van positie of op basis van naam. Als we de parameters positioneel door moeten geven, gebruiken we een regular expression om alle parameters in de originele SQL select te vinden. Als we een parameter hebben gevonden, vervangen we deze door een “?” en zoeken we de originele parameternaam in de HashTable met parameters op om de waarde aan de DbParameters collectie toe te voegen. In het geval van parameters met naam is dit iets eenvoudiger, alleen de parameter prefix dient te worden aangepast van de originele @ naar het juiste karakter voor de database server.

Private Function FixCommandText(_
  ByVal sql As String,_
  ByVal dbParams As DbParameterCollection,_
  ByVal params As Hashtable) As String

  Dim newSql As String = sql
  Dim paramPrefix As String = ""
  Dim positional As Boolean = True
  Dim paramName As String
  Dim param As DbParameter
  Dim re As New Regex("@\w+")
  Dim match As Match

  If TypeOf m_Factory Is _
       System.Data.SqlClient.SqlClientFactory Then
     positional = False
     paramPrefix = "@"
  ElseIf TypeOf m_Factory Is _
        System.Data.OracleClient.OracleClientFactory_
     Then
     positional = False
     paramPrefix = ":"
  Else
     positional = True
     paramPrefix = "?"
  End If

  If positional Then
    match = re.Match(newSql)
    Do While match.Success
      newSql = newSql.Substring(0, match.Index) + _
        paramPrefix + _
        newSql.Substring(match.Index + match.Length)

      If params.ContainsKey(match.Value) Then
        paramName = match.Value
        param = m_Factory.CreateParameter()
        param.Value = params(paramName)
        ' Is ignored but what the heck
        param.ParameterName = paramName
        dbParams.Add(param)
      End If
      match = re.Match(newSql)
    Loop
  Else
    Dim startAt As Integer = 0
    match = re.Match(newSql, startAt)
    Do While match.Success
      newSql = newSql.Substring(0, match.Index) + _
        paramPrefix + _
        newSql.Substring(match.Index + 1)
      startAt = match.Index + match.Length
      match = re.Match(newSql, startAt)
    Loop
    For Each paramName In params.Keys
      param = m_Factory.CreateParameter()
      param.Value = params(paramName)
      param.ParameterName = paramPrefix +_
        paramName.Substring(1)
      dbParams.Add(param)
    Next
  End If

  Return newSql
End Function

Listing 7: de FixCommandText functie.

Nadat een correct DbCommand-object is aangemaakt, dient alleen nog een DbDataAdapter-object te worden gecreëerd om de DataTable te vullen. Hiertoe dient de CreateDataAdapter-functie, die niets meer of minder doet dan het aanroepen van de data factory CreateDataAdapter en het resultaat teruggeeft.

Dit voorbeeld is niet bedoeld als productiecode, enkel om een oplossing te schetsen die kan worden gehanteerd bij het schrijven van databaseonafhankelijke code waarmee runtime de juiste database kan worden benaderd.

Conclusie

De nieuwe Data Provider Factories en base classes maken het eenvoudiger dan voorheen om generieke database-code te schrijven. Helaas zorgen verschillen per provider en database-server toch nog voor de nodige complexiteit die opgelost dient te worden.

NB: Dit artikel is gebaseerd op bèta 2 van het .NET framework, dus de mogelijkheid bestaat dat dit alsnog zal worden toegevoegd.

De sources die bij dit artikel horen kunt u downloaden via Beijer_DbProviderFactory_SRC.zip.