MS Access + N2

Topics: User Forum
Jun 2, 2008 at 8:55 AM
Edited Jun 2, 2008 at 9:08 AM
This is a post to hopefully help others going down the path I am.  SQL is superior, but some clients just demands Access.  So far, with my minimal testing, things seem to be working!  Here's how to do it:

1. Acquire NHibernate.JetDriver.  I got it and compiled it straight from NHibernate contrib SVN, located here:

https://nhcontrib.svn.sourceforge.net/svnroot/nhcontrib/trunk/

2. With NHibernate.JetDriver.dll in hand (should have version stamp 2.0.0.1001 as of this writing), pop it into your bin directory alongside all your other NHibernate & N2 DLL's

3. Create an MDB file with Access which mimics the 4-table structure used by N2.  It's not so bad to do.  Getting the nullables right was the hardest part, for me (required field vs. allow nulls terminology hurt my brain the whole time).  I'd love to upload the MDB I put together, if there's a place for me to contribute it. 

Name this MDB file N2.MDB and put it where the N2.MDF normally likes to live (App_Data)

4.  Modify your web.config file in two places:

 <connectionStrings>
  <add name="N2CMS_old" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\N2.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
  <add name="N2CMS" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\n2.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/>
  <!-- This is an example of a connection string for local sql database using windows authentication
  <add name="N2CMS" connectionString="Server=(local);Database=N2;Integrated Security=SSPI"/>
  -->
 </connectionStrings>
 <castle>
  <properties>
   <rootItemID>12</rootItemID>
   <nhSettings>
    <dictionary>
     <entry key="connection.provider">NHibernate.Connection.DriverConnectionProvider</entry>
     <entry key="connection.connection_string_name">N2CMS</entry>
     <entry key="cache.use_second_level_cache">false</entry>
     <entry key="_old.connection.driver_class">NHibernate.Driver.SqlClientDriver</entry>
     <entry key="_old.dialect">NHibernate.Dialect.MsSql2005Dialect</entry>
     <entry key="dialect">NHibernate.JetDriver.JetDialect,NHibernate.JetDriver</entry>
     <entry key="connection.driver_class">NHibernate.JetDriver.JetDriver,NHibernate.JetDriver</entry>
    </dictionary>
   </nhSettings>
  </properties>
  <include uri="assembly://N2/Engine/n2.configuration.xml"/>
  <!-- Configuration goes here -->
 </castle>


That's it!  You should now be rockin with the ultra-lowest-common-denomanator Jet database!

 




Notes/Thoughts:  Keep in mind the membership/role stuff is going to be nerfed when using Access.  That could mean limited/no use of page role access features.  I've heard rumors of an Access version of the MembershipProvider stuff, but haven't seen it in action - I conjecture one would have to go down that road, or try to monkey up a custom membership provider.

Coordinator
Jun 2, 2008 at 9:41 PM
Great! I see this question from time to time so I added link from the faq.
Jul 12, 2008 at 4:53 AM

Hey I am trying to MS Access to work for 1.4.  It looks like you've simplified the database configuration, which is great, but also removes the fine grain control I took advantage of to make this little trick work.  I am digging through ConfigurationBuilder and it appears these values aren't mutable any longer.  Could you steer me how to configure a new flavor of web.config to achieve the previous result?   Thank you!

 


libardo wrote:
Great! I see this question from time to time so I added link from the faq.


Jul 14, 2008 at 9:21 AM
Ok, I've had some success - though it's kind of ugly.  First, the not-so-ugly part.  The web.config changes are:

<database connectionStringName="N2CMS" flavour="Generic">
   <hibernateProperties>
     <add name="dialect" value="NHibernate.JetDriver.JetDialect,NHibernate.JetDriver" />
     <add name="connection.driver_class" value="NHibernate.JetDriver.JetDriver,NHibernate.JetDriver" />
   </hibernateProperties>
</database>

That's not bad.  In fact, an overall improvement.  Well done.  Note that flavour doesn't matter here.  I just chose Generic since it was the closest possible fit, but you could use any of the other flavours here since you are overriding the DB behavior anyway.

Now for the ugly part.

Libardo, I couldn't find NHibernate .1002 anywhere in the SVN tree.  Funny, huh?  So, what I did something frightning instead:

#1 I grabbed the latest JetDriver from within the NHibernate SVN tree (version 411 as of this writing - it hasn't changed in a while)
#2 Break it off SVN (optional step)
#3 Rewire the references of NHibernate.dll and log4net.dll to the versions shipping with N2 (i made a lib directory copied em in there and referenced it). In other words, add references in the project to your own copies of NHibernate and log4net, instead of the ones within the NHibernate SVN tree.
#4 Compile this beast.  The unit test compile fails, if you feel like rewiring those references be my guest.  The JetDriver however DOES compile. 
#5 Copy the updated JetDriver DLL as you did originally alongside your other N2 DLL's

Presto.  N2 1.4 + MS Access

Again, if there's an FTP available somewhere I can post up the JetDriver dll.  Happy el-cheapo databasing!
Coordinator
Jul 16, 2008 at 11:30 PM
Hi I'd like to include your el-cheapo solution to the libs =) I'll add another flavour too. Can you zip and mail to cristian-dot~libardo[at[gmail.dot,com? Or attach to an issue in the tracker. Thank you.