.NET 2.0: Werken met data in ADO.NET 2.0

Door Michiel van Otegem
1 augustus 2008

ADO.NET is het onderdeel van het .NET Framework voor het benaderen van databases en andere gegevensbronnen. Voor versie 2.0 is veel werk verricht om de snelheid en efficiency te verbeteren en ontwikkelaars minder code te laten schrijven.

ADO.NET 1.x heeft zich in de afgelopen jaren bewezen als een goede technologie voor het werken met gegevensbronnen. De stap van ADO naar ADO.NET (zie kader “Van ADO naar ADO.NET”) was weliswaar een grote, maar de voordelen zijn enorm. Dat wil echter niet zeggen dat ADO.NET 1.x perfect is. Vooral op het gebied van snelheid en efficiency viel nog wel wat winst te behalen, te beginnen bij situaties waarin een DataSet een rol speelt (en dat zijn er veel).

De meest gebruikte manier om met gegevens te werken in ADO.NET is door een DataSet te laden met een DataAdapter, waarna de verbinding met de gegevensbron gesloten worden. Wijzigingen aan de DataSet worden later weer doorgevoerd naar de gegevensbron via (dezelfde) DataAdapter. De DataAdapter zorgt daarbij zelf dat de verbinding met de database geopend en gesloten wordt, tenzij je dit zelf doet. In feite is de DataSet een representatie van (een gedeelte) van de gegevensbron die in het geheugen bewaard wordt, en eventueel zelfs over een netwerk verstuurd kan worden of in een bestand opgeslagen kan worden. De DataSet bevat tabellen en daartussen kunnen zonodig relaties en andere constraints gedefinieerd worden, die al dan niet gelijk zijn aan hoe deze in de gegevensbron gedefinieerd zijn. Per tabel gebruik je een DataAdapter voor het laden en weer wegschrijven van gegevens. Omdat er geen constante verbinding met de gegevensbron is komt hierbij wel de volgende vraag naar voren: “Wat gebeurt er als iemand anders gegevens wijzigt tussen het lezen en schrijven van gegevens?”. Dat probleem is op meerdere manieren op te lossen en hangt eigenlijk af van de functionaliteit die men wil implementeren. Zo is het mogelijk om de wijzigingen allemaal door te voeren, of allemaal niet. Ook is het mogelijk om alle wijzigingen die goed gaan door te voeren. In al die gevallen kan de gebruiker op de hoogte gesteld worden, maar is het ook mogelijk om door te gaan alsof er niets aan de hand is. Een nadeel aan de manier waarop de DataAdapter in ADO.NET 1.x werkt is dat alle wijzigingen aan een DataSet één voor éen verwerkt worden, niet in batch. Vooral met veel wijzigingen kan dat de prestatie aardig beïnvloeden. In ADO.NET 2.0 is de DataAdapter uitgebreid met de UpdateBatchSize eigenschap, waarmee ingesteld kan worden hoeveel rijen tegelijk bijgewerkt moeten worden. Het bijwerken van een gegevensbron met 10 rijen tegelijk zou dan kunnen zoals in de code in afbeelding 1.

   1:  'benodigde objecten aanmaken
   2:  Dim connection As New SqlConnection(connString)
   3:  Dim adapter As New SqlDataAdapter(sql, connection)
   4:  Dim ds As New DataSet()
   5:   
   6:  'DataSet vullen
   7:  adapter.Fill(ds)
   8:   
   9:  'DataSet wijzigen (code weggelaten)
  10:  …
  11:   
  12:  'Wijzig operaties aanmaken op basis van sql string
  13:  Dim commandBuilder As New SqlCommandBuilder(adapter)
  14:   
  15:  'Gegevensbron bijwerken (in batch)
  16:  adapter.UpdateBatchSize = 10
  17:  adapter.Update(ds)

De code in Afbeelding 1 begint met het initialiseren (niet openen) van de database verbinding en het aanmaken van de DataAdapter. Merk op dat er gebruik gemaakt wordt van connString en sql, waarvan aangenomen wordt dat deze al gedefinieerd zijn. De enige regel code die in Afbeelding 1 anders is dan in ADO.NET 1.x is regel 16, want daar wordt de batch grootte ingesteld. Die ene regel maakt behoorlijk wat verschil in prestatie. Afbeelding 1 is overigens specifiek voor SQL Server. Voor andere databases gebruik je andere objecten, bijvoorbeeld OracleConnection voor Oracle en OleDbConnection voor alle OleDb ondersteunende gegevensbronnen. Omdat de verschillende classes niet erven van een gemeenschappelijke Connection class is het lastig om code te schrijven die onafhankelijk is van de gegevensbron. Om dit verhelpen is in ADO.NET 2.0 het principe van de Provder Factory toegevoegd. Hiermee is het wel mogelijk om gegevenbron onafhankelijke code te schrijven, en die is eventueel configureerbaar, zodat je niet opnieuw hoeft te compileren wanneer je een ander type gegevensbron wilt gebruiken. Afbeelding 2laat zien hoe dit werkt. Op regel 2 wordt aangegeven welke provider gebruikt moet worden (in dit geval SQL Server). Voor OleDb zou dit System.Data.OleDb zijn, en voor Oracle System.Data.OracleClient.

   1:  'factory aanmaken
   2:  Dim providerName As String = "System.Data.SqlClient"
   3:  Dim provider As DbProviderFactory = _
   4:     DbProviderFactories.GetFactory(providerName)
   5:   
   6:  'generieke objecten definieren
   7:  Dim connection As DbConnection
   8:  Dim adapter As DbDataAdapter
   9:   
  10:  'Objecten maken met provider
  11:  connection = provider.CreateConnection()
  12:  adapter = provider.CreateAdapter()

Gegevens kopiëren

Hoewel de mogelijkheid tot batch updates van een DataSet erg handig zijn, dekken ze niet de situatie waarin je grote hoeveelheden gegevens wilt kopiëren van één tabel naar een andere (mogelijk in verschillende gegevensbronnen). Op zo’n moment kun je eigenlijk beter gebruik maken van een DataReader waarmee je, zolang de verbinding met de gegevensbron geopend is, rijen één voor één kunt uitlezen. In ADO.NET 1.x moet je dan vervolgens voor elke rij een INSERT operatie uitvoeren om de gegevens in de andere gegevensbron te krijgen. Dat vergt ten eerst wat programmeerwerk, en ten tweede is ook dit niet bepaald efficiënt. In het geval dat je met SQL Server werkt kun je in ADO.NET 2.0 echter gebruik maken van het SqlBulkCopy object dat de uitgelezen gegevens kopieert naar de opgegeven tabel. Eventueel kun je daarbij een batch grootte opgeven. Een simpel voorbeeld zie je in Afbeelding 3.

   1:  'Database verbinding met bron maken en openen
   2:  Dim source As New SqlConnection(sourceString)
   3:  source.Open()
   4:   
   5:  'Database verbinding met bestemming maken en openen
   6:  Dim destination As New SqlConnection(destinationString)
   7:  destination.Open()
   8:  'DataReader maken
   9:  Dim command As New SqlCommand(sql, source)
  10:  Dim reader As SqlDataReader = command.ExecuteReader()
  11:  'Kopieer actie instellen en uitvoeren
  12:  Dim bulkCopy As New SqlBulkCopy(destination)
  13:  bulkCopy.DestinationTableName = "MijnTabel"
  14:  bulkCopy.WriteToServer(reader)
  15:   
  16:  'Verbindingen sluiten
  17:  reader.Close()
  18:  bulkCopy.Close()
  19:  source.Close()
  20:  destination.Close()

De bulk copy operatie voor SQL Server is bijna net zo snel als via Data Transfomation Services (DTS), en behoorlijk veel sneller dan dezelfde soort operatie via een batch update. Dat dit in code kan betekent echter dat dit soort operaties ook toegankelijk gemaakt kunnen worden voor gebruikers via een applicatie. Van reguliere gebruikers kun je niet verwachten dat ze even een DTS package maken, als dat überhaupt al toegestaan zou worden door de systeembeheerder.

Asynchrone operaties

Het kan zijn dat operatie sop de database lang duren. In ADO.NET 1.x betekent dit dat de client moet wachten totdat er een resultaat is. In ADO.NET 2.0 kun je echter ook operaties asynchroon uitvoeren, zodat de client verder kan met andere zaken terwijl de database operaties uitgevoerd worden. Zolang verschillende opdrachten niet van elkaar afhankelijk zijn, kun je zelfs een hele serie opdrachten asynchroon opstarten, en vervolgens wachten op het resultaat. Dit alles kan met alle geneugten die reguliere aanroepen ook hebben. ADO.NET 2.0 bevat drie verschillende mogelijkheden voor asynchrone afhandeling: een "polling model" waarbij in de code periodiek gekeken wordt of de operatie al klaar is, een "callback model" waarbij er een functie is die wordt aangeroepen als de operatie klaar is, en een "wait model" waarbij er na het opstarten van meerdere asynchrone operaties gewoon gewacht wordt tot ze klaar zijn. Hoewel asynchrone operaties heel handig zijn, moet er wel op gelet worden dat foutafhandeling niet even triviaal is als bij synchrone operaties.

Serializeren van gegevens

Serializatie is een operatie waarbij een object omgezet wordt in een representatie die opgeslagen kan worden of verstuurd kan worden. In .NET kun je de meeste objecten op twee manieren serializeren, als XML of binair. Binair is veel efficiënter, maar is niet uitwisselbaar met niet .NET systemen. Hoewel de DataSet impliciet te serializeren is naar XML, kun je ook de WriteXml methode gebruiken (of ReadXml om weer in te lezen). Een probleem met ADO.NET 1.x is dat dit niet kan met een DataTable, hoewel een DataSet in principe één of meer DataTable objecten bevat. Als je maar één tabel nodig hebt, is een DataSet dus eigenlijk een beetje overbodig. In ADO.NET 2.0 kan een DataTable object daarom nu ook opgeslagen worden als XML. Een ander probleem is dat een DataSet niet binair geserializeerd kan worden, zodat je altijd aangewezen bent op XML, ook als aan de andere kant van de lijn ook een .NET applicatie staat. Gelukkig is ook dit in ADO.NET 2.0 een zaak van het verleden.

Transacties

Strikt genomen geen onderdeel van ADO.NET 2.0, maar iets wat hier niet mag ontbreken is een nieuwe manier om met transacties om te gaan. Het .NET Framework bevat in versie 2.0 de System.Transactions namespace, met eigen transactie manager. Dit betekent dat je voor transacties niet meer gebonden bent aan Enterprise Services (ook wel Component Services genoemd) met de Distributed Transaction Co-ordinator (DTC) of een verbindingsspecifieke transactie. Die twee zijn namelijk niet verenigbaar en hebben allebei voor- en nadelen. De DTC heeft een behoorlijke overhead, maar is onmisbaar als je werkt met meerdere databases, terwijl een verbindingsspecifieke transactie relatief lichtgewicht is, maar alleen werkt met de database waarvoor de transactie is opgezet. De transactie manager van System.Transactions gebruikt een lichtgewicht transactie wanneer er maar met één database gewerkt wordt, maar doet een “upgrade” naar de DTC als er meerdere database in het spel komen. Een klein addertje onder het gras is dat dit vooralsnog alleen werkt met SQL Server 2005, dus als je een andere database server gebruikt, stapt System.Transactions meteen over naar de DTC. Het is echter aannemelijk dat meer database servers deze mogelijkheid zullen gaan ondersteunen.

MARS

ADO.NET heeft nog een feature die vooralsnog alleen werkt met SQL Server 2005, genaamd MARS (Multiple Active Result Sets). SQL Server 2005 is in staat om over dezelfde database verbinding meerdere DataReaders open te hebben en om zonodig tegelijkertijd INSERT, DELETE, en andere operaties uit te voeren. In ADO.NET 1.x is het zo dat je een DataReader kunt openen om gegevens te lezen, maar als je op basis van die gegevens een operatie uit wilt voeren, moet je of de DataReader afsluiten, of een nieuwe databaseverbinding aanmaken naast de al bestaande verbinding. Het is evident dat hierdoor niet erg efficiënt omgegaan wordt met de beschikbare resources. In het geval dat er een connection pool is met een beperkt aantal database verbindingen betekent dit dat er snel niet genoeg beschikbare verbindingen zijn. Bovendien wordt de server er ook niet vrolijk van als er onnodig veel verbindingen open staan. Overigens is MARS niet de oplossing der oplossingen voor dit probleem, want MARS heeft zo z’n eigen nadelen. MARS kan redelijk wat (server) resources opslokken, afhankelijk van de situatie, en wanneer er gewerkt wordt met transacties is de kans op deadlock groter. Bovendien kunnen alle operaties binnen MARS alleen maar deel uitmaken van dezelfde transactie. Het is niet mogelijk om meerdere transacties te definiëren voor verschillende operaties die met MARS uitgevoerd worden.

Statistieken

Een aardigheidje van ADO.NET 2.0 is de mogelijkheid om statistieken op te vragen van een database verbinding. Het nut ervan is beperkt tot monitoring en het debuggen van applicaties, en vanwege de overhead is het ook verstandig om het alleen in die situaties te gebruiken. Heb gebruik ervan is overigens wel heel gemakkelijk, zoals Afbeelding 4 laat zien.

   1:  'Statistieken aanzetten op bestaande verbinding
   2:  connection.StatisticsEnabled = True
   3:   
   4:  'Operaties uitvoeren (code weggelaten)
   5:  …
   6:   
   7:  'Statistieken opvragen
   8:  Dim ht As HashTable = connection.RetrieveStatistics()
   9:   
  10:  'Statistieken weergeven
  11:  Console.WriteLine("Bytes verstuurd " & ht("BytesSent"))
  12:  Console.WriteLine("Bytes ontvangen " & ht("BytesOntvangen"))

Afbeelding 4 laat slechts twee van de op te vragen statistieken zien. Er zijn er nog veel meer, zoals bijvoorbeeld de tijd die gespendeerd is aan het uitvoeren van opdrachten, hoeveel rijen er geselecteerd zijn, hoeveel transacties actief waren, enzovoorts.

Rijk aan features

ADO.NET 2.0 is rijk aan features. Dat was ADO.NET 1.x ook al, maar er zijn zeer veel uitbreidingen. Dit artikel beschrijft slechts de meest in het oog springende elementen. Vooral in combinatie met SQL Server 2005 is ADO.NET 2.0 zeer krachtig. Dit geldt ook omdat ADO.NET 2.0 aangepast is om binnen de context van SQL Server 2005 zelf te werken.

Van ADO naar ADO.NET

Voordat het .NET Framework er was, was ADO (ActiveX Data Objects) de methode om met gegevens uit een gegevensbron te werken. Met de komst van ADO.NET werd de manier waarop je met gegevens werkt radicaal anders, zo anders dat ADO.NET een hele andere naam had moeten hebben. Zeker als je bedenkt dat ActiveX synoniem is aan COM en ADO.NET niets met COM te maken heeft.. Het enige voordeel van de naam ADO.NET is dat ontwikkelaars die met ADO gewerkt hebben weten waar ADO.NET voor dient. Het centrale ontwikkelmodel van ADO is echter client-server, waarbij het gebruikelijk is dat er een verbinding met de database is. Pas in latere versies is er functionaliteit toegevoegd die het toestaat om (tijdelijk) de verbinding met de database te verbreken, maar client-server is altijd het centrale model geweest. In het wereldbeeld van ADO.NET is client-server slechts een van de mogelijkheden en is het centrale programmeermodel anders. Belangrijk is dat ADO.NET tracht de gegevensuitwisseling zo efficiënt mogelijk te maken. In de meeste gevallen wordt de database verbinding zo snel mogelijk verbroken en wordt data uit de gegevensbron in het geheugen van de client opgeslagen, meestal in een DataSet object. Wanneer records toegevoegd, gewijzigd of verwijderd worden, wordt dit eerst doorgevoerd op de DataSet. Pas in een later stadium worden de wijzigingen aan de DataSet doorgevoerd naar de gegevensbron. Dit biedt onder andere de mogelijkheid om de gegevens ergens tijdelijk op te slaan als er geen netwerk verbinding is, of om gegevens via het internet naar een andere locatie te sturen (waar geen database verbinding is), en later de wijzigingen terug te krijgen. Verder is het veel eenvoudiger om een schaalbare oplossing te maken, omdat het aantal open database verbindingen beperkt blijft.