[.NET] Access db, vermijden van boilerplate code

[NickThissen]

Hey,

Ik maak regelmatig kleine applicaties voor mensen die ik ken, vrienden en af en toe 'klanten', en voor de meeste applicaties gebruik ik een MS Access database (2003 formaat). Waarom Access? Nou, omdat dat makkelijk is (SQL server installeren ga ik echt niet aan beginnen bij elke klant, ik zou niet eens weten hoe) en omdat ik niet meer nodig heb dan een paar tables.

Hoewel alles prima werkt loop ik toch steeds tegen hetzelfde 'probleem' aan: elke keer als ik een nieuwe applicatie begin komt er een heel stuk 'boilerplate' code aan te pas om de database te kunnen gebruiken. Het is steeds dezelfde code, enkel met andere tabellen en klassen.


De aanpak die ik gebruik is langzaam wat beter en sneller (minder boilerplate) geworden, maar het komt steeds weer neer op dit:

Voor elke tabel maakte ik een 'Manager' klasse en een 'Entity' klasse. Een Entity stelt dan een rij voor in die tabel, en de Manager klasse heeft methodes om de rijen te laden, op te slaan, te wijzigen, etc. Vrij standaard allemaal lijkt me. De Entity klassen hebben gewoon een property voor elke kolom in de database.
De Manager klasse roept dan methodes op een Database klasse aan zoals ExecuteNonQuery, ExecuteReader, etc.

In het begin was elke manager klasse een los staand object en moest ik dus elke keer opnieuw de SQL query handmatig typen, parameters toevoegen, etc. Na een tijdje ben ik overgestapt op TableAdapters (ik weet niet precies hoe dit genoemd wordt) waarbij ik in Visual Studio de table adapters kan maken en daarop queries kan maken, die ik dan in mijn manager klasse gewoon aanroep. Maar nog steeds moet ik voor elke tabel zo'n adapter maken en de queries erop plaatsen, ook al zijn die queries 9/10 keer precies hetzelfde enkel met andere tabel en kolom namen.


Ik zoek nu eigenlijk iets dat dit proces kan versnellen. 90% van de code (inclusief queries) die ik nu handmatig krijg kan ik ook automatisch laten genereren omdat het steeds hetzelfde is. Ik zoek nu enkel nog iets dat me dit laat doen...
Bijvoorbeeld het Entity Framework is iets wat ik vaak tegenkom, maar voor zover ik weet werkt dit niet met Access. NHibernate lijkt ook te kunnen doen wat ik wil, en hoewel dat wel Access zou moeten kunnen ondersteunen is het me tot nu toe niet gelukt, na een week of wat experimenteren... Steeds weer nieuwe errors hier en daar, zelfs op voorbeelden die specifiek moeten tonen hoe je met Access verbind (die voorbeelden zijn wel al oud, gebruiken versie 2 van NH en ze zitten nu op 3 geloof ik?).

Ik heb zelfs al zelf een 'framework' geschreven dat precies doet wat ik wil. Ik heb een Entity klasse gemaakt (met properties die ik per conventie elke tabel geef: Id, Deleted, CreatedTime en UpdatedTime), en een EntityManager(Of T As Entity) die een Load, LoadById en Save methode heeft (en nog wat andere dingen).

Voor een nieuwe tabel maak ik nu een nieuwe klasse aan die van Entity overerft, geef hem de nieuwe properties, en zet nu bij elke property er een eigen attribuut omheen die de naam van de kolom in de database geeft. De klasse zelf krijgt ook een attribuut wat de tabelnaam geeft. Met die informatie kan ik, via veelvuldig gebruik van Reflection, in de EntityManager klasse de Load en Save methodes automatisch genereren.

Dat werkt ongeveer zo:

• Zodra een Entity object aangemaakt wordt zoekt hij z'n eigen properties op via Reflection en slaat deze op in een property (zodat dit niet elke keer hoeft te gebeuren)
• Voor een Load methode wordt een SQL Select query gemaakt (altijd SELECT * voor enkele tabellen, of wat simpele joins voor gekoppelde tabellen, meer heb ik toch niet nodig) en via ExecuteReader wordt er door een IDataReader gelooped
• Voor elk result in de IDataReader zoek ik op welke property daar bij hoort in de Entity klasse, en via Reflection zet ik dan die property.
• Voor een Save methode gebeurt hetzelfde maar dan andersom. Een SQL Update/Insert query wordt gegenereerd en met een loopje door de properties van de Entity klasse in kwestie kan ik de parameters en waarden in de query stoppen.

Hoewel dit allemaal prima werkt en het me enorm veel tijd bespaart (het enige wat ik nu hoef te doen is de klassen aanmaken en bij elke property een attribuut toe te voegen, en aangezien ik die klassen sowieso altijd al gebruik is eigenlijk alleen het toevoegen van attributen extra tijd), is het ook echt enorm traaaaaaag! Dat had ik al verwacht omdat ik veel gebruik maak van Reflection, maar ik had stiekem toch gehoopt dat het nog wel mee zou vallen. Sowieso heb ik nooit enorme tabellen en zelden meer dan een of twee joins in de queries, dus als het 10 keer langzamer zou zijn dan mijn oude 'rechtstreekse' methode dan zou ik nog tevreden zijn. Maar bij iets grotere tabellen (> 100 rijen) duurt het gewoon minuten... Dat is dus echt niet te doen.


Dus ja, ik zoek echt alternatieven die me helpen om de boilerplate code te vermijden. Ik zal zelf nog doorzoeken met NHibernate (heeft iemand toevallig nog tutorials ofzo hoe ik met Access verbind?) maar wellicht zijn er wel makkelijkere alternatieven, vooral omdat het allemaal vrij simpel is wat ik nodig heb.

Bedankt!

[Sebazzz]

Waarom gebruik je geen SQL Server Compact? Lijkt mij een stuk betrouwbaarder, biedt ook encryptie en reparatieopties, integreert geweldig in SQL Server Management Studio en Visual Studio en je kan zonder problemen O/R mappers zoals Entity Framework erop gebruiken. SQL Server Compact volgt hetzelfde idee als SQLite en Access behalve dat je geen dependency van Access hebt. Daarnaast biedt het je bepaalde mogelijkheden die ook met een volledige SQL Server kunnen, zoals het bekijken van een execution plan.

[beany]

Wat Sebazzz zegt. Sql Server Compact is het geen wat je wil hebben als je SQL Server niet wil gebruiken.

En Entity Framework 4 is volgens mij erg goed te gebruiken, maar ik heb er alleen wat mee zitten spelen.

[BertS]

Ik zou dan nog kiezen voor SQLite, hoef je niets te installeren.
Maar volgens mij lost dat uiteindelijk de vraag van de TS niet op voor de boilerplate code. Dat heeft niet zoveel met de connectionstring/provider te maken.
Zelf gebruik ik daarvoor XPO van DevExpress. Samen met CodeRush werkt dat fantastisch, zeker voor dit soort kleine toepassingen. Nadeeltje in jouw geval wellicht dat het geen gratis tool is, maar abstraheert wel veel van die ballast weg.

[urk_forever]

Wij werken op kantoor ook met XPO van DevExpress, maar dat is geen gratis tool. Je zou naar NHibernate kunnen kijken eventueel in combinatie met Castle ActiveRecord.

Hier staat bijvoorbeeld een uitleg hoe je NHibernate met Access kan laten werken.

[beany]

BertS schreef op maandag 10 januari 2011 @ 08:36:
Ik zou dan nog kiezen voor SQLite, hoef je niets te installeren.
Maar volgens mij lost dat uiteindelijk de vraag van de TS niet op voor de boilerplate code. Dat heeft niet zoveel met de connectionstring/provider te maken.
Zelf gebruik ik daarvoor XPO van DevExpress. Samen met CodeRush werkt dat fantastisch, zeker voor dit soort kleine toepassingen. Nadeeltje in jouw geval wellicht dat het geen gratis tool is, maar abstraheert wel veel van die ballast weg.

Applicatie moet sowieso geinstalleerd worden. En Sql Server Compact is geen server of zo dat geinstalleerd moet worden.

En het lost wel degelijk de boilerplate code op omdat je met Sql Server Compact het Entity Framework (v4) kan gebruiken. Weg boilerplate code!

Met SQLite(wat een geweldige db is overigens) moet je ook een framework gebruiken(XPO). Die kost geld, Entity Framework is gratis!

[urk_forever]

beany schreef op maandag 10 januari 2011 @ 08:57:
[...]Met SQLite(wat een geweldige db is overigens) moet je ook een framework gebruiken(XPO). Die kost geld, Entity Framework is gratis!

NHibernate ( en Castle ActiveRecord) is ook gratis.

Overzicht van een aantal ORM tools voor .Net: \[VB.NET] Wat is de beste ORM methode? Enkele langs elkaar..

[ Voor 18% gewijzigd door urk_forever op 10-01-2011 09:15 ]

[NickThissen]

Sorry, ik had er inderdaad bij moeten zeggen dat het gratis moet zijn, ik ben niet in staat om zoveel uit te geven voor een paar simpele applicaties waar ik niet echt aan verdien

SQL Server Compact zal ik zeker eens naar kijken, dat klinkt wel interessant. Maar nu heb ik nog het probleem dat ik ook af en toe ASP.NET websites maak, en ook daar gebruik ik een Access database. Mijn webhost ondersteunt wel SQL Server, maar tegen een meerprijs dus dat doe ik liever niet. Voor zover ik kan zien is er geen ondersteuning voor SQL Server Compact of SQLite. Tenminste, ik kan geen databases aanmaken voor die providers (enkel SQL Server 2005, MySql 5.x en ODBC DSN). Maar aan de andere kant staat daar ook niets van Access tussen en die kan ik ook gewoon gebruiken (door m'n database mdb file gewoon in de App_Data folder van m'n web applicatie te zetten). Geldt wellicht iets vergelijkbaars voor SQL Server Compact of SQLite? Dat kan ik er zo snel niet uit halen...

Ik zit nu eens door die lijst te kijken met ORM providers, en het Rapid Entity Framework ziet er wel handig uit. Ik ga die eens proberen, iemand nog slechte ervaringen daar mee?

urk_forever schreef op maandag 10 januari 2011 @ 08:44:
Hier staat bijvoorbeeld een uitleg hoe je NHibernate met Access kan laten werken.

Dat voorbeeld was het eerste dat ik kon vinden, maar ik krijg het met geen mogelijkheid aan de praat. Het gaat steeds mis bij het instantieren van de JetDriver, en iets met het dialect ofzo... Daarop gegoogled, maar ik heb nog geen oplossing gevonden. Wel gevonden dat de JetDriver niet meer bij NHibernate zit standaard ofzo, en dat je die los moet gaan compileren... Heb een paar dll's gedownload die anderen gecompileerd zouden hebben waar JetDriver in zou moeten zitten, maar ook daarmee kom ik niets verder...

[beany]

Zo ver ik weet ondersteund een ASP.Net installatie SQL Server Compact out of the box. Dus inderdaad in de app_data folder zetten en dan zou het moeten werken. Tenzij de hoster dit heeft uitgeschakeld.

Maar ik ben niet zo heel erg thuis in asp.net applicaties.

wat info: http://weblogs.asp.net/sc...support-with-asp-net.aspx

[ Voor 17% gewijzigd door beany op 10-01-2011 10:50 ]

[urk_forever]

NickThissen schreef op maandag 10 januari 2011 @ 10:37:
Sorry, ik had er inderdaad bij moeten zeggen dat het gratis moet zijn, ik ben niet in staat om zoveel uit te geven voor een paar simpele applicaties waar ik niet echt aan verdien

SQL Server Compact zal ik zeker eens naar kijken, dat klinkt wel interessant. Maar nu heb ik nog het probleem dat ik ook af en toe ASP.NET websites maak, en ook daar gebruik ik een Access database. Mijn webhost ondersteunt wel SQL Server, maar tegen een meerprijs dus dat doe ik liever niet. Voor zover ik kan zien is er geen ondersteuning voor SQL Server Compact of SQLite. Tenminste, ik kan geen databases aanmaken voor die providers (enkel SQL Server 2005, MySql 5.x en ODBC DSN). Maar aan de andere kant staat daar ook niets van Access tussen en die kan ik ook gewoon gebruiken (door m'n database mdb file gewoon in de App_Data folder van m'n web applicatie te zetten). Geldt wellicht iets vergelijkbaars voor SQL Server Compact of SQLite? Dat kan ik er zo snel niet uit halen...

Ik zit nu eens door die lijst te kijken met ORM providers, en het Rapid Entity Framework ziet er wel handig uit. Ik ga die eens proberen, iemand nog slechte ervaringen daar mee?


[...]

Dat voorbeeld was het eerste dat ik kon vinden, maar ik krijg het met geen mogelijkheid aan de praat. Het gaat steeds mis bij het instantieren van de JetDriver, en iets met het dialect ofzo... Daarop gegoogled, maar ik heb nog geen oplossing gevonden. Wel gevonden dat de JetDriver niet meer bij NHibernate zit standaard ofzo, en dat je die los moet gaan compileren... Heb een paar dll's gedownload die anderen gecompileerd zouden hebben waar JetDriver in zou moeten zitten, maar ook daarmee kom ik niets verder...

Heb je toevallig x64 Windows draaien? Voor zover ik weet draait de Jet driver niet onder x64 en moet je dus je applicatie op x86 compilatie zetten in Visual Studio, misschien is dat het probleem?

Zo niet, moet je eens de foutmelding posten.

[NickThissen]

urk_forever schreef op maandag 10 januari 2011 @ 10:54:
[...]


Heb je toevallig x64 Windows draaien? Voor zover ik weet draait de Jet driver niet onder x64 en moet je dus je applicatie op x86 compilatie zetten in Visual Studio, misschien is dat het probleem?

Zo niet, moet je eens de foutmelding posten.

Ja x64, maar aangezien ik altijd met Access werk ken ik dat probleem wel, en compileer ik standaard voor x86.

De fout komt op deze regel:

code:

configuration.AddAssembly(System.Reflection.Assembly.GetExecutingAssembly());

en zegt:

Could not compile the mapping document: NHibernateWithAccess.User.hbm.xml

De mapping file zou gewoon goed moeten zijn:

XML:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="NHibernateWithAccess.User, NHibernateWithAccess" table="Users">

    <id name="Id" column="Id" type="int" unsaved-value="0">
      <generator class="identity" />
    </id>

    <property name="EmailAddress" length="50" />
    <property name="UserName" column="UserName"  type="String" length="50"/>
    <property name="Password" column="[Password]" type="String" length="50"/>

  </class>
</hibernate-mapping>

De inner exception zegt dan ook iets anders, namelijk:

{"Could not instantiate dialect class NHibernate.JetDriver.JetDialect, NHibernate.JetDriver"}

[NickThissen]

beany schreef op maandag 10 januari 2011 @ 10:48:
Zo ver ik weet ondersteund een ASP.Net installatie SQL Server Compact out of the box. Dus inderdaad in de app_data folder zetten en dan zou het moeten werken. Tenzij de hoster dit heeft uitgeschakeld.

Maar ik ben niet zo heel erg thuis in asp.net applicaties.

wat info: http://weblogs.asp.net/sc...support-with-asp-net.aspx

Bedankt, dat klinkt goed, zal het straks eens proberen. Het rapid entity framework heb ik zitten proberen en hoewel rijen ophalen prima werkt krijg ik geen nieuwe rijen opgeslagen... Krijg gewoon een null reference error zonder enige informatie, geen inner exception, stack trace laat niets zien (behalve de CreateNewEntity methode doe ik als laatste aanroep)... Daar heb ik dus ook niets aan.


EDIT
Het lijkt erop dat SQL CE met het entity framework geen autonumber kolommen ondersteunt..? Dat is wel een serieuze limitatie zeg... Ik kan toch niet zelf m'n Id's gaan genereren?

[ Voor 9% gewijzigd door NickThissen op 10-01-2011 11:45 ]

[Woy]

Voor zowel SQL Server Compact als SQLite heb je alleen file access nodig, en zul je dus niet speciaal ondersteuning van je host moeten hebben.

Het probleem met SQL Server Compact is dat het voor zover ik weet alleen een 32bits driver heeft, en dus niet compatible is met x64. Een windows applicatie kun je in ieder geval gewoon forceren in 32 bits mode, dus dan is 64 bits geen probleem. Ik weet niet of dat ook zo eenvoudig kan met een ASP.NET applicatie, maar ik neem aan dat daar ook mogelijkheden voor zijn.

Persoonlijk ben ik meer fan van SQLite, aangezien dat in mijn geval een betere performance had, maar dat kan erg afhangen van de manier waarop je de database gebruikt. Op zich voldoet SQL CE ook gewoon goed, en het zal waarschijnlijk wel beter integreren met het entity framework, al kan ik me niet voorstellen dat je daar ook niet met SQLite mee kan werken aangezien daar ook gewoon een ADO.NET provider voor is.

EDIT
Het lijkt erop dat SQL CE met het entity framework geen autonumber kolommen ondersteunt..? Dat is wel een serieuze limitatie zeg... Ik kan toch niet zelf m'n Id's gaan genereren?

Heb je daar een bron van? Dat lijkt me namelijk erg vreemd.

[beany]

Sql Server Compact versie 4 met EF4 schijnt wel Identity fields te ondersteunen

[NickThissen]

Ik heb even snel een database met een enkele tabel gemaakt in SQL Server Management Studio. In Visual Studio voeg ik nu een nieuwe ADO.NET Entity Data Model toe, waarbij ik deze database gebruik en als Data source 'Microsoft SQL Server Compact 3.5' gekozen heb.

De tabel heeft een Id kolom die als primary key, unique en 'identity' is aangegeven.

Ik kan de data nu wel laden, maar zodra ik een nieuwe rij probeer in te voegen krijg ik:

Server-generated keys and server-generated values are not supported by SQL Server Compact.

Met 3.5 werkt het dus inderdaad niet.
(Als ik de Id kolom niet op identity zet en zelf een Id meegeef werkt het wel, maar dat gaat uiteindelijk niet werken, ik kan toch niet eigen Ids gaan genereren, dat gaat toch niet goed met meerdere gebruikers op een website bijvoorbeeld?)


Daarna heb ik de CTP2 versie van SQL CE 4 geinstalleerd, maar ik heb geen idee hoe ik die nu moet gebruiken. De referenties in het VS project heb ik veranderd naar de DLLs van versie 4 (System.Data.SqlServerCe.dll en System.Data.SqlServerCe.Entity.dll, beide zijn nu versie 4.0.0.0), maar dan werkt het nog steeds niet. Ik kan bij het maken van de ADO.NET Entity Data Model ook niet kiezen voor versie 4 van SQL Compact edition, enkel 3.5 staat in de lijst...

Hoe kan ik nu 4 gebruiken? Ik moet toch echt gewoon identity kolommen hebben...

[ Voor 9% gewijzigd door NickThissen op 10-01-2011 13:35 ]

[raptorix]

Qua database zou je ook nog kunnen kijken naar VistaDB, naar mijn weten helemaal compatible met Sql Server, kijk wel even naar licentievoorwaarden.

[beany]

raptorix schreef op maandag 10 januari 2011 @ 13:40:
Qua database zou je ook nog kunnen kijken naar VistaDB, naar mijn weten helemaal compatible met Sql Server, kijk wel even naar licentievoorwaarden.

Dat is inderdaad ook een interessante optie, ter waarde van 1295 dollar.

[NickThissen]

Hm, ik heb zojuist de (beta) SP1 van VS2010 geinstalleerd, en daarna iets van 'Sql Server Compact Edition Visual Studio Tools', en daarna kan ik in een Web project gebruik maken van SQL CE 4, en dan werkt het invoegen van records wel met een identity kolom!

Maar voor een winforms project werkt het dus niet, en daar kan ik ook niet SQL CE 4 kiezen uit de datasources. Is dit gewoon nog niet mogelijk, moet ik wachten tot CE 4 of SP1 gereleased worden? Of kan ik nu ook al iets doen om dit te gebruiken?

[Sebazzz]

NickThissen schreef op maandag 10 januari 2011 @ 14:50:
Maar voor een winforms project werkt het dus niet, en daar kan ik ook niet SQL CE 4 kiezen uit de datasources. Is dit gewoon nog niet mogelijk, moet ik wachten tot CE 4 of SP1 gereleased worden? Of kan ik nu ook al iets doen om dit te gebruiken?

Ik ben op het moment bezig met een desktoptoepassing met SQL Server CE in .NET 4. Moet je niet eerst de juiste assemblies referencen?

[NickThissen]

Ik voeg een data model toe met deze stappen:

1. Rechtermuis op project in solution explorer
2. Add - New Item
3. Kies 'ADO.NET Entity Data Model' uit de lijst
4. Kies 'Generate from database'
5. Kies 'New Connection'
6. Bij Data source bovenaan klik ik op 'Change'
7. Nu krijg ik een lijstje waarin ik de data source (bijv SQL Server, SQL Server Compact, etc) kan kiezen.

In een Web application krijg ik dit keuze scherm in de laatste stap en kan ik dus Microsoft SQL Server Compact 4.0 kiezen:


In een winforms applicatie krijg ik echter een ander lijstje en kan ik alleen 3.5 kiezen (4.0 is vervangen door 3.5). De manier waarop ik werk is verder hetzelfde, ik kan tabellen toevoegen via de Server Explorer in visual studio en deze daarna in het model laden. Echter kan ik in een winforms project (met CE 3.5) geen identity kolom gebruiken, dan gaat het mis bij een nieuwe rij invoegen. In een web applicatie (met CE 4.0) werkt dit wel gewoon zoals verwacht en wordt automatisch een nieuw ID gegenereerd.

Ik heb SP1 voor visual studio geinstalleerd en de 'SQL Server Compact Edition Visual Studio Tools', en daarmee kreeg ik de support voor 4.0 in een web applicatie, maar het werkt dus nog steeds niet in een winforms applicatie...


EDIT
Als ik de referenties naar CE 4.0 toevoeg voordat ik hte model toevoeg (op de manier zoals hierboven beschreven) verandert er niets, ik kan nog steeds alleen 3.5 kiezen. Ik voeg deze referenties toe btw:

• C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Desktop\System.Data.SqlServerCe.dll
• C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Desktop\System.Data.SqlServerCe.Entity\System.Data.SqlServerCe.Entity.dll

Zijn dat niet de goeie?

[ Voor 16% gewijzigd door NickThissen op 10-01-2011 16:07 ]

[Guldan]

Om dan ook maar in de beta sfeer te blijven qua sqlce. Misschien is het ook handig om dan code first te gaan gebruiken om de boilerplate code nog meer te reduceren.

zie : http://weblogs.asp.net/sc...h-entity-framework-4.aspx

[urk_forever]

NickThissen schreef op maandag 10 januari 2011 @ 11:06:
[...]

Ja x64, maar aangezien ik altijd met Access werk ken ik dat probleem wel, en compileer ik standaard voor x86.

De fout komt op deze regel:

code:

configuration.AddAssembly(System.Reflection.Assembly.GetExecutingAssembly());

en zegt:

[...]

De mapping file zou gewoon goed moeten zijn:

XML:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="NHibernateWithAccess.User, NHibernateWithAccess" table="Users">

    <id name="Id" column="Id" type="int" unsaved-value="0">
      <generator class="identity" />
    </id>

    <property name="EmailAddress" length="50" />
    <property name="UserName" column="UserName"  type="String" length="50"/>
    <property name="Password" column="[Password]" type="String" length="50"/>

  </class>
</hibernate-mapping>

De inner exception zegt dan ook iets anders, namelijk:

[...]

Welke versie van NHibernate gebruik je dan? Ik heb het zojuist met versie 1.2.0-GA geprobeerd, maar dan werkt die sample zonder problemen.