FastMember and nullable properties

c# fastmember reflection sqlbulkcopy

Question

i'm having an issue using fastmember, where by i have a public nullable property (datetime) for my class, but when i try and insert it into the db the column value is an empty string an not a null, which causes an exception to be thrown, any ideas on how to make the column value null?

public class MyClass
{
    public DateTime? MyDate {get;set;}
}

DataTable testTable = new DataTable();
using (var reader = ObjectReader.Create(myClassList))
{
    testTable.Load(reader);
}

 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString))
 {
     SqlTransaction transaction = null;
     connection.Open();
     try
     {
         transaction = connection.BeginTransaction();
         using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
         {
              sqlBulkCopy.BulkCopyTimeout = 0;
              sqlBulkCopy.BatchSize = batchSize;
              sqlBulkCopy.DestinationTableName = "myTable";
              for (var cols = 0; cols < testTable.Columns.Count; cols++)
              {
                   sqlBulkCopy.ColumnMappings.Add(cols, cols);
              }

              sqlBulkCopy.WriteToServer(testTable);
          }
          transaction.Commit();
      }
      catch (Exception ex)
      {
          transaction.Rollback();
          throw ex;
      }
  }

if releasedate is null then it will always put it into the table as a blank string rather than a null, is there a way to force it to be null? or to interpret the empty string as a null in the sql bulk copy?

Popular Answer

Sorry to hash up a somewhat old question. But a few things stand out here. The use of a DataTable (when the FastMember reader was basically designed to be shoved directly into SqlBulkCopy), the 0 value for timeout (generally not wise) and also the odd use of transactions here (BCP will use it's own internal transaction by default, if it fails at any point it rolls itself back).

Regarding the null issue, this can happen if the ordering of the object properties do not match the ordering of the properties in the database. The solution? FastMember! Use it to iterate your class properties and build the mapping. Below is a cleaned up version of your code that should work for you:

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString))
using (var bcp = new SqlBulkCopy(conn))
using (var rd = ObjectReader.Create(myClassList))))
{
    Type type = typeof(T);
    var accessor = TypeAccessor.Create(type);
    var members = accessor.GetMembers();

    foreach (var member in members)
    {
        bcp.ColumnMappings.Add(new SqlBulkCopyColumnMapping(member.Name, member.Name));
    }

    conn.Open();

    bcp.BatchSize = 10000;
    bcp.DestinationTableName = "myTable";

    bcp.WriteToServer(rd);
}

Note: this is .NET Compliant code, so you may need to tweak the Type type code for .NET Framework compliance. But the FastMember code will be the same.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow