In dit tweede en laatste deel over de migratie van DBF naar ADO.NET wordt met code geïllustreerd hoe je vanuit VB.NET een Visual FoxPro Database Container (en bijbehorende tabellen) kan benaderen.
Gebruik van Visual FoxPro data via OLEDB
Het vanuit .NET rechtstreeks benaderen van Visual FoxPro DBF bestanden is niet mogelijk. Vanuit .NET wordt OLEDB wel goed ondersteund. Dit opent de mogelijkheid om te communiceren met elke OLEDB compliant database. In dit voorbeeld is er voor gekozen om eerst een classlibrary te ontwikkelen met daarin alle functies voor het opvragen en muteren van data. Voor elke VFP tabel zullen de volgende 5 functies moeten worden ontwikkeld:
- GetTable
- GetSingleTable(nIdItem)
- UpdateTable(nIdItem)<
- AddTable(nIdItem)
- DeleteTable(nIdItem)
In geval van een tabel ‘Customers’ zullen de functies respectievelijk GetCustomers, GetSingleCustomer, UpdateCustomer, DeleteCustomer en InsertCustomer gaan heten. Deze aanpak zie je ook vaak terug in SQL Server Stored Procedures. Een groot voordeel van deze aanpak met een aparte layer is dat in deze library direct de Business Rules kunnen worden verwerkt. Het maakt dan niet meer uit vanuit welke omgeving er ontwikkeld wordt op basis van de Visual FoxPro data, elke omgeving maakt gebruik van deze interface om te kunnen communiceren
.
Fig. 1: De Visual FoxPro gegevens in een .NET Windows applicatie
.NET Project
In .NET kan gekozen worden voor aanmaak van een classlibrary. Dit levert uiteindelijk een DLL op in de BIN map met daarin alle gedefinieerde public functions. Vervolgens wordt alleen de DLL vrijgeven aan een software ontwikkelaar. Die kan dan gebruik maken van de aangeboden functies en hoeft zich niet meer druk te maken over de toe te passen Business Rules en hoe de achterliggende database benaderd moet worden.
Functie GetTable (GetCustomers)
Deze functie retourneert een .NET dataset met daarin alle klanten uit de tabel Customers.dbf. Deze dataset kan vervolgens eenvoudig aan een grid gekoppeld worden.
Public Function GetCustomers() As DataSet
' Make connection to VFP database
Dim conPOS4All As New OleDbConnection
conPOS4All.ConnectionString = strConnection
conPOS4All.Open()
Dim oCommand As New OleDbCommand("SET NULL OFF", conPOS4All)
oCommand.ExecuteNonQuery()
oCommand.CommandText = "SET DELE ON"
oCommand.ExecuteNonQuery()
' Create a Command object with select statement
Dim strSelect As [String] = "SELECT * FROM customers"
oCommand.CommandText = strSelect
' Create a DataAdapter
Dim oDataAdapter As New OleDbDataAdapter
oDataAdapter.SelectCommand = oCommand
' Create a DataSet
Dim dsRelaties As New DataSet
' Fill the DataSet with table information
oDataAdapter.Fill(dsCustomers, "customers")
'Close connection
oDataAdapter.Dispose()
conPOS4All.Close()
Return dsCustomers
End Function
De strConnection string wordt als constante in de class geregistreerd:
Protected strConnection As String =_
"provider=VFPOLEDB.1;data source=_
'C:\SDGN ADO.NET\data.dbc';password='';userid='';_
Exclusive=false;BackGroundFetch=No;Collate=Machine"
In de hierboven afgebeelde code wordt eerst een verbinding gemaakt met de DBC. In Visual FoxPro hebben de settings een standaard waarde. De belangrijkste settings worden vervolgens aangepast door middel van een oleDbCommand, waaronder SET NULL OFF en SET DELETED ON. Vervolgens wordt een oleDbCommand samengesteld waarin alle klanten geselecteerd worden waarna een DataAdapter wordt aangemaakt die als bridge zal fungeren tussen de database en een dataset. De DataAdapter krijgt hierna instructie om de gegevens te plaatsen in de dataset dsRelaties. Hierna zal de DataAdapter afgesloten worden en wordt de verbinding met de database beëindigd.
Functie GetSingleTable(nIdItem) (GetSingleCustomer)
Met deze functie kan een enkel record worden opgevraagd uit een tabel. Ook deze functie zal een .NET dataset retourneren, maar deze dataset zal slechts 1 rij bevatten in de enige tabel in de dataset. De VB.NET code is nagenoeg uniek, alleen de functie aanroep en de SQL selectie is anders:
Public Function GetSingleCustomer(ByVal nIdItem as Integer)_
As DataSet
' Create a Command object with select statement
Dim strSelect As [String] = "SELECT * FROM customers_
WHERE customers.id=" & nIdItem
In de aanroep van deze functie moet de primary key worden doorgegeven van de klant waarvan de gegevens opgevraagd worden.
Functie UpdateTable(nIdItem) (UpdateCustomer)
Deze functie is bedoeld om gegevens op te slaan vanuit .NET in een Visual FoxPro tabel. Als parameter wordt opnieuw de primary key gegeven van de klant die aangepast moet worden.
Public Function UpdateCustomer(_
ByVal nIdCustomer As Integer,_
ByVal cName As String)
' Make connection to VFP database
Dim conPOS4All As New OleDbConnection
conPOS4All.ConnectionString = strConnection
conPOS4All.Open()
Dim oCommand As New OleDbCommand("SET NULL OFF", conPOS4All)
oCommand.ExecuteNonQuery()
oCommand.CommandText = "SET DELE ON"
oCommand.ExecuteNonQuery()
' Create update select statement
Dim strUpdateSelect As String
strUpdateSelect = "UPDATE Customers SET name='" +_
cName + "' WHERE Id=" + nIdCustomer.ToString
' Create a Command object with select statement
oCommand.CommandText = strUpdateSelect
' Execute the command
oCommand.ExecuteNonQuery()
'Close the connection
conPOS4All.Close()
End Function
“Field ID does not accept null values”
Een soortgelijke melding zul je te zien krijgen bij het opslaan van gegevens via OLEDB in een Visual FoxPro tabel. Dit gebeurt wanneer een INSERT INTO commando gegeven wordt waarbij niet elk veld expliciet vermeld wordt. Visual FoxPro plaatst in de niet opgegeven velden dan automatisch de waarde .NULL. Elk veld in de tabel waarvoor is ingesteld dat .NULL. values niet geaccepteerd worden (default in Visual FoxPro bij het aanmaken van een nieuw veld) zal zorgen voor deze melding. Om dit te voorkomen moet het commando SET NULL OFF gegeven worden.
Functie AddTable() (AddCustomer)
Deze functie is bedoeld om gegevens toe te kunnen voegen aan een tabel. De toe te voegen gegevens moeten als parameters worden meegegeven. De functie retourneert na afloop de primary key van het record dat aangemaakt is. Wanneer de aanmaak mislukt, kan een waarde kleiner dan of gelijk aan 0 worden teruggestuurd, waarmee vervolgens na te gaan is waarom het toevoegen mislukt is.
Public Function AddCustomer(ByVal cName As String,_
ByVal cCode as String) As Integer
' Make connection to VFP database
Dim conPOS4All As New OleDbConnection
conPOS4All.ConnectionString = strConnection
conPOS4All.Open()
Dim oCommand As New OleDbCommand(_
"SET NULL OFF", conPOS4All)
oCommand.ExecuteNonQuery()
oCommand.CommandText = "SET DELE ON"
oCommand.ExecuteNonQuery()
* Request a new free primary key value
oCommand.CommandText = "NewId('id','customers')"
Dim oWorkParam As OleDbParameter
oWorkParam = oCommand.Parameters.Add(_
New OleDbParameter_
("@nId", System.Data.OleDb.OleDbType.Integer))
oWorkParam.Direction = ParameterDirection.ReturnValue
oCommand.ExecuteScalar()
Dim nId As Integer
nId = oCommand.Parameters("@nId").Value.ToString
' Create update select statement
Dim strUpdateSelect As String
strUpdateSelect = "INSERT INTO Customers_
(id, code, name) VALUES (_
" & nId & ",'" + cCode + "','" + cName + "')"
' Create a Command object with select statement
oCommand.CommandText = strUpdateSelect
' Execute the command
Try
oCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox("fout " & ex.Message)
nId=-1
End Try
'Close the connection
conPOS4All.Close()
Return nId
End Function
Bijzonder in deze functie is dat er eerst een call wordt uitgevoerd naar de VFP Stored Procedure NewId(). Deze functie is te vinden in de software bij dit artikel en bevat een routine om een nieuwe primary key van een nog aan te maken record te bepalen. Deze functie wordt ook aangeroepen in de Default Value method van het primary key veld. Het probleem bij het gebruik van OLEDB is echter dat het bijzonder moeilijk is om nadat een record toegevoegd is, te achterhalen wat de waarde van de toegepaste primary key is. In SQL Server bestaat hiervoor de @@Identity expressie, maar binnen Visual FoxPro is iets dergelijks niet aanwezig.
Door echter eerst de NewId() functie rechtstreeks aan te roepen wordt er een primary key gereserveerd in de (free) table ID.DBF. Vervolgens kan in het INSERT INTO commando deze waarde worden meegegeven; het primary key veld wordt dan gevuld met een waarde die uniek is. Andere clients requesten immers ook primary keys via NewId() en krijgen dus daarop volgende unieke waardes.
Een oplettende lezer zal opmerken dat de NewId() functie zich ook bevindt in de Default Value van een tabel
Een oplettende lezer zal opmerken dat de NewId() functie zich ook bevindt in de Default Value van een tabel (in dit voorbeeld CUSTOMERS.DBF). Dit zou kunnen leiden tot het uitgeven van een tweede primary key, zodat je vanuit .NET bij het aanmaken van een record telkens twee primary keys kwijt bent. Er is echter gebleken dat wanneer een veld meegegeven wordt in een INSERT SQL commando, de Default Value method niet meer uitgevoerd wordt. Er wordt dus niet nogmaals een primary key aangevraagd!
In Visual FoxPro 8 bestaat inmiddels een Auto-Increment mogelijkheid in een tabel, zodat Visual FoxPro zelf een unieke key bepaald. Ik heb dat echter niet gebruikt om twee redenen:
1. Onze software staat al uit in het veld. Als we nu willen gaan werken met Auto-Increment moeten we wel de beginwaarde van die AutoIncrement routine gaan opgeven. Dat wordt een flinke conversieslag die uitgevoerd zou moeten worden.
2. Door gebruik te maken van de AutoIncrement functie kan vanuit .NET niet een vrije primary key worden gevraagd. Het detecteren van de primary key van een aangemaakt record (waar het nou juist om ging) wordt dan weer bijzonder moeilijk.
Functie DeleteTable(nId As Integer) (DeleteCustomer)
Deze functie is bedoeld om een record te verwijderen. Als parameter moet de primary key van het te verwijderen record worden meegestuurd. De functie retourneert True of False, afhankelijk van of het verwijderen van het record geslaagd is.
Public Function DeleteCustomer(_
ByVal nIdCustomer As Integer) As Boolean
' Make connection to VFP database
Dim bDelete as Boolean = True
Dim conPOS4All As New OleDbConnection
conPOS4All.ConnectionString = strConnection
conPOS4All.Open()
Dim oCommand As New OleDbCommand(_
"SET NULL OFF", conPOS4All)
oCommand.ExecuteNonQuery()
oCommand.CommandText = "SET DELE ON"
oCommand.ExecuteNonQuery()
' Create delete select statement
Dim strDeleteSelect As String
strDeleteSelect = "DELETE FROM Customers WHERE Id="_
+ nIdCustomer.ToString
' Create a Command object with select statement
oCommand.CommandText = strDeleteSelect
' Execute the command
Try
oCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox("fout " & ex.Message)
bDelete = False
End Try
'Close the connection
conPOS4All.Close()
Return bDelete
End Function
In deze functie wordt een DELETE SQL commando gegeven die via de OLEDB driver de data zal verwijderen. De mogelijkheid bestaat dat Visual FoxPro het verwijderen niet zal toestaan vanwege referentiele integriteit. Dergelijke foutmeldingen worden afgevangen via het Try-Catch statement waarna de functie False zal retourneren.
Unsupported Visual FoxPro commands
Ik ben een flink aantal uren kwijt geweest met het debuggen van de gebruikte NewId() functie in de Stored Procedure van de DBC. Uiteindelijk bleek dat er een commando stond in deze functie dat niet ondersteund werd, wanneer de functie aangeroepen werd vanuit OLEDB. In de help van Visual FoxPro is een lijst te vinden van supported en not-supported commando’s en functies wanneer Visual FoxPro gebruikt wordt vanuit OLEDB. Het commando dat voor problemen zorgde was SET TALK. Wij gebruiken dat commando bij het bepalen van een primary key wanneer die niet voorkomt in de ID.DBF tabel. In dat geval moet de NewId() functie uit de tabel waarvoor een primary key aangevraagd wordt, gaan berekenen wat de hoogste waarde is van bestaande ID’s. Omdat dat bij grote tabellen even kan duren is een SET TALK ON commando ingebouwd, zodat er op dat moment een thermometer ontstaat die aangeeft wat de voortgang is van het bepalen van de primary key.
Het commando wat voor problemen zorgde was SET TALK
Ik ben op zoek gegaan naar een mogelijkheid om te detecteren hoe Visual FoxPro gestart is zodat een unsupported commando niet uitgevoerd wordt wanneer Visual FoxPro gestart is door OLEDB. De functie _vfp.StartMode is hiervoor te gebruiken, maar helaas is dit commando niet beschikbaar (!) in OLEDB. Al onze applicaties beschikken echter over een global variabele oApp. Door op het bestaan daarvan te testen, is te achterhalen of Visual FoxPro ‘normaal’ gestart is of via OLEDB. Deze controle is ingebouwd in NewId().
Een ander belangrijk commando dat helaas niet ondersteund wordt in OLEDB, is STRTOFILE of anders FCREATE/FWRITE. Het probleem is dat fouten die ontstaan tijdens de uitvoering van een Stored Procedure vanuit OLEDB niet worden doorgegeven aan de OLEDB driver. Het enige dat opvalt is dat de NewId() routine geen waarde terug geeft, maar het is bijna ondoenlijk om na te gaan op welke regel een fout ontstaan is. Eigenlijk wel vreemd: Het ON ERROR commando wordt wel ondersteund, maar het loggen van de fout naar een Ascii bestand is niet mogelijk. Ik heb hier geen workaround voor kunnen vinden, behalve het loggen van de fout naar een tabel. Dat laatste kan echter ook weer de nodige fouten opleveren, het maken van een Ascii bestand is veel robuuster.
Performance
De performance van OLEDB is aanzienlijk trager dan het direct benaderen van gegevens in Visual FoxPro. Dat komt omdat er een complete layer tussen de user interface en de database geplaatst wordt. OLEDB is echter veel sneller dan ODBC en bevat bovendien minder problemen. Onderstaande tabel toont het verschil bij het vullen van 10.000 records in de relaties tabel vanuit Visual FoxPro en vanuit VB.NET.
|
OLEDB |
SQL 2000 |
Visual FoxPro |
|
1000 records aanmaken |
1000 records aanmaken |
1000 records aanmaken |
|
35 seconden |
3 seconden |
3 seconden |
Tabel 1: Performance vergelijking OLEDB, Visual FoxPro en SQL 2000 server (test is uitgevoerd op een laptop met Windows 2000, 512 Mb Ram, 2 GHz)
Tegenover de slechte performance kan wel opgemerkt worden, dat er tijd gewonnen kan worden wanneer deze interface op dezelfde server wordt geïnstalleerd als waar zich ook de database bevindt. In dat geval wordt er geen gebruik gemaakt van een belangrijke bottleneck, de netwerk infrastructuur. Met Visual FoxPro heb je deze mogelijkheid in principe niet hebt (je werkt altijd met een fat-client), zodat je beperkt wordt door de snelheid van je netwerk. Wanneer er via dit interface met ASP.NET ontwikkeld wordt, bestaat de mogelijkheid om gegevens op dezelfde server te verzamelen.
Opvallend is de hoge performance vanuit .NET naar SQL Server
Opvallend is de hoge performance vanuit .NET naar SQL Server. De reden hiervoor is de apart voor SQL Server ontwikkelde OLEDB SQL Namespace in .NET waar alle universele routines uitgehaald zijn. Deze namespace is volledig geoptimaliseerd om SQL Server aan te sturen. De namespace is dan ook uitsluitend te gebruiken voor SQL Server.
Er is geen test gedaan met MSDE (MicroSoft Data Environment). Deze gratis te downloaden database is op dezelfde wijze te benaderen als SQL Server, met als een van de beperkingen de maximale database omvang van 2 Gb.
Updateable datasets
Datasets zijn ‘updateable’. Wijzigingen die in de dataset worden aangebracht, worden - zoals in deel 1 al beschreven - niet direct weggeschreven in de dataset. Pas na het uitvoeren van de AcceptChanges() method worden de wijzigingen door de OLEDB driver opgeslagen in de achterliggende database. De dataset zorgt zelf vervolgens voor het uitvoeren van de benodigde Update, Insert en Delete methods. Dit geldt echter niet voor de Visual FoxPro OLEDB driver. Mutaties moeten ‘handmatig’ worden weggeschreven met de functies zoals die in dit artikel beschreven zijn. Wanneer gebruik gemaakt wordt van SQL Server, dan zorgt OLEDB zelf voor het uitvoeren van deze functies na een AcceptChanges() commando.
Een WinForms applicatie
Met behulp van de hier beschreven functies is het nu erg eenvoudig om elk soort .NET applicatie te schrijven. Als voorbeeld volgt nu de opzet van een WinForms applicatie waarin klant informatie getoond wordt en kan worden gemuteerd.
Alle functies zijn ondergebracht in het project DataInterface.vbProj. Dit project is opgenomen in de .NET solution NedFoxInterface.Sln. Binnen dezelfde solution bestaat project InterfaceDemo.vbProj. In dit project bevindt zich 1 WinForm met daarin de bekende buttons voor het muteren van data.
In de Load() method van het form wordt de dataset dsRelaties voorzien van alle klanten.
Private Sub frmRelaties_Load(_
ByVal sender As System.Object,_
ByVal e As System.EventArgs)_
Handles MyBase.Load
Dim CustomersDb As New Interface.CustomersDb
dsCustomers = CustomersDb.GetCustomers()
RefreshForm()
End Sub
In het project InterfaceDemo.vbProj is een referentie gemaakt naar Interface.Dll, de gecompileerde interface met alle functies om data te kunnen muteren. Via de variabele CustomersDb kunnen alle functies gebruikt worden in dit form. Vervolgens wordt de dataset dsCustomers gevuld met alle klanten uit Visual FoxPro CUSTOMERS.DBF.
Dit artikel biedt onvoldoende ruimte om de volledige code af te drukken die zich in het form bevindt. In de source code bij dit artikel is de volledige inhoud te raadplegen.
Een belangrijke method die wel de moeite waard is om af te beelden is de method ‘RefreshForm’. Zodra deze method aangeroepen wordt, worden de controls op het form voorzien van de meest recente data uit de actieve rij in de dataset dsCustomers.
Private Sub RefreshForm()
txtCode.Text = dsCustomers.Tables("Customers").Rows_
(ActiveRow - 1).Item("code").ToString
txtNaam.Text = dsCustomers.Tables("Customers").Rows_
(ActiveRow - 1).Item("name").ToString
btnFirst.Enabled = (ActiveRow > 1)
btnPrevious.Enabled = (ActiveRow > 1)
btnNext.Enabled = (ActiveRow < _
dsRelaties.Tables("Relaties").Rows.Count)
btnLast.Enabled = (ActiveRow < _
dsRelaties.Tables("Relaties").Rows.Count)
btnEdit.Enabled = Not bInEditMode
btnRevert.Enabled = bInEditMode
btnSave.Enabled = bInEditMode
btnNew.Enabled = Not bInEditMode
btnDelete.Enabled = Not bInEditMode
txtNaam.ReadOnly = Not bInEditMode
txtCode.ReadOnly = Not bInEditMode
End Sub
In het Load event van het form (en nog op verschillende andere plekken) wordt de method RefreshForm aangeroepen. In deze method wordt gebruik gemaakt van de property ActiveRow waarin het nummer van de rij uit de dataset wordt bijgehouden van de data die op dat moment op het form getoond wordt. De andere property bInEditMode is True wanneer de gebruiker aan het muteren is. Op dat moment wordt een aantal controls gedisabled.
Afhankelijk van de waarde van ActiveRow worden de navigatie buttons vrijgegeven of geblokkeerd, zodat voor de gebruiker duidelijk is of hij zich aan het begin of aan het einde van het bestand bevindt.
Ten slotte
Nu de database echt losgemaakt is van de applicatie, wordt het ook eenvoudig om uiteindelijk te kiezen voor een andere database. Wanneer de front applicatie uiteindelijk volledig gereed is onder .NET, kan als laatste worden besloten om de gebruikte Visual FoxPro database te migreren naar MSDE of SQL Server. Er zal dan een conversie ontwikkeld moeten worden, en het interface moet worden aangepast, zodat SQL Server tabellen benaderd gaan worden. Aan de voorzijde hoeven echter geen wijzigingen meer uitgevoerd te worden. Zeker qua performance is deze stap nog wel serieus te overwegen.
Dit voorbeeld voorziet nog niet in het gebruik van Business Rules, hoewel die nu natuurlijk eenvoudig toe te voegen zijn. Het interface leent zich daar uitstekend voor, omdat alle interactie met de Visual FoxPro database hierlangs moet verlopen.
Een kritische noot tenslotte: Binnen het .NET platform wordt SQL server zeer intensief gepromoot. Ook voor PDA’s wordt uitsluitend SQL Server ondersteund, in de vorm van SQL Server 2.0 voor Windows CE. De performance van de OLEDB driver is bijzonder teleurstellend te noemen. Ik kan me haast niet aan de indruk onttrekken dat deze driver vertraagd is om SQL server significant beter uit de verf te laten komen. Wellicht vormt MSDE een acceptabel alternatief, maar in dat geval zou ik meer keuzemogelijkheden willen onderzoeken. De installatie van Small Business Server waarop SQL Server tegen een aantrekkelijke prijs wordt meegeleverd, is gezien de performance zeker het overwegen waard.
Conclusie
Met de in dit artikel geschetste aanpak kan een migratie gefaseerd uitgevoerd worden. Het is mogelijk om aan een bestaande Visual FoxPro applicatie een module toe te voegen die in .NET ontwikkeld is en die gebruikt maakt van bestaande data. Ook kan besloten worden om de gehele front applicatie eerst over te zetten in .NET en dan vrij te geven, in plaats van per module te gaan uitleveren. Als afsluiting van de migratie wordt de database vervangen, maar dat is geen noodzakelijke stap. De learning curve, die zeker bestaat bij .NET, kan met deze aanpak gespreid worden over een langere periode, waarbij ontwikkelaars zich in eerste instantie niet ook nog eens bezig moeten houden met migratie van de data.
De software bij dit artikel is te downloaden van de SDGN website www.sdgn.nl of via www.nedfox.nl
Vragen over dit artikel? Reacties? Mail mij: mark.vroom@sdgn.nl!