Turning automatic nulls off for inserts to FoxPro tables through NHibernate using JetDriver isn't working.

Question

I'm using NHibernate with Burrow and Fluent to shuttle data from SQLServer2005 into a directory full of FoxPro 7 dbf's.

When I try to save a new record through Burrow's GenericDAO, I get an exception that boils down to "Field XXX does not accept null values," where field XXX is a NOT NULL field that is not being mapped at all because it is unimportant and is to be left blank.

I ran into this before when directly using ADO.Net & OleDbConnection's, and found that adding ";NULL=OFF" to the connection string solved the problem.

I've tried "NULL=OFF", "NULL=NO" in the connection string as well as "VFPOLEDB" & "VFPOLEDB.1" for provider. It still tries to automatically insert NULLs into fields missing from the "INSERT (field1, field2, ...) VALUES (?, ?, ? ...)" statement (i.e., those fields I did not map).

Any ideas?

Here is my fluent config for the FoxPro tables:

string connString = "Provider=VFPOLEDB.1;Data Source="
  + @"D:\Documents\Work\Projects\OurProjects\Clients\Client1\Testing\Data"
  + ";NULL=NO;";

Fluently.Configure(nhCfg)
  .Database(JetDriverConfiguration.Standard.ConnectionString(connString)
  .Dialect<GenericDialect>().Driver<OleDbDriver>())
  .Mappings(m => m.FluentMappings.AddFromAssembly(
    Assembly.Load("OurProduct.Model, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null")))
  .BuildConfiguration();

And the mapping class, the actual table these are being inserted into has a couple dozen unmapped fields, all NOT NULL and they all should be blank on new records:

 public class ClientMap : ClassMap<Client>
 {
  public ClientMap() {
   Id(x => x.PersonId).GeneratedBy.Assigned();
   Map(x => x.LastName).Length(15);
   Map(x => x.FirstName).Length(15);
   Map(x => x.MiddleName).Column("midname").Length(1);
   Map(x => x.Address1).Length(40);
   Map(x => x.Address2).Length(40);
   Map(x => x.City).Length(20);
   Map(x => x.State).Length(2);
   Map(x => x.ZipCode).Length(9);
   Map(x => x.Gender).Length(30);
   Map(x => x.Ethnicity).Length(30);
   Map(x => x.MaritalStatus).Column("marital").Length(30);
   Map(x => x.LivingArrangement).Column("livarrange").Length(30);
   Map(x => x.PovertyLevel).Column("povlevel").Length(30);
   Map(x => x.BirthDate);
   Map(x => x.AssessmentNotes).Column("assnotes");
  }
 }

Answer 1

Doing the following appears to resolve the issue, but I believe would require doing for each new unit of work (BurrowFramework initialization).

  var sess = bf.GetSession(typeof(Client));

  var dbCmd = sess.Connection.CreateCommand();

  dbCmd.CommandType = System.Data.CommandType.Text;
  dbCmd.CommandText = "SET NULL OFF";

  sess.Transaction.Enlist(dbCmd);


  dbCmd.ExecuteNonQuery();

Answer 2

The OLE-DB property DBPROP_VFPOLEDB_NULL is the equivalent of SET NULL.