CODEWITHSUNDEEP
c#sql-serversqlbulkcopy
Boris Callens
Boris Callens

Reputation: 93317

Pass DEFAULT keyword to non-nullable column with SqlBulkCopy

Is there a way to pass the DEFAULT keyword to SqlBulkCopy from an IDataReader?
Basically the equivalent of

INSERT INTO MyTable(NonNullableWithDefault)
VALUES (DEFAULT)

In my application the target tables have not-nullable columns with a default constraint on them.
Some of the data to upload has values for these columns, some does not.
For the rows where there is no value (DBNull) it should use the default constraint.

As noted in this answer the SqlBulkCopy either expects you to not pass in a ColumnMapping at all or send a value.

Alternatives I'm considering

I can't be the first person with this problem.
What do people use in such a scenario?

UPDATE

After much fiddling around I now fetch my entire database structure with one query and then pass that dataset to my datareaders.

SELECT
    columns.TABLE_SCHEMA                            schemaName
  , columns.TABLE_NAME                              tableName
  , columns.TABLE_SCHEMA + '.' + columns.TABLE_NAME fullTableName
  , columns.COLUMN_NAME                             columnName
  , columns.DATA_TYPE                               dataType
  , ISNULL(columns.CHARACTER_MAXIMUM_LENGTH, -1)    charlength
  , columns.COLUMN_DEFAULT                          defaultValue
  , columns.ORDINAL_POSITION                        ordinalPosition
FROM
  information_schema.columns columns

The parsing of the default value string isn't pretty and most likely not fitted for advanced cases. In our case however, it covers all bases.

private object ParseDefault(DataRow row)
{
    if(row.IsNull("defaultValue")) return null;

    var value = row.Field<string>("defaultValue");
    if (value == "(getdate())")
    {
        return DateTime.UtcNow;
    }
    var type = GetTypeForName(row.Field<string>("dataType"));
    return ParseCell(value.Trim('(', ')'), type);
}

private static object ParseCell(string value, Type info)
{
    if (value.Equals("NULL", StringComparison.OrdinalIgnoreCase))
    {
        return DBNull.Value;
    }

    int intValue;
    if (info == typeof(bool) && int.TryParse(value, out intValue))
    {
        return intValue == 1;
    }
    var foo = TypeDescriptor.GetConverter(info);
    return foo.ConvertFromInvariantString(value);
}

Advantages:

Disadvantages

Upvotes: 2

Views: 723

Answers (1)

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

Is there a way to pass the DEFAULT keyword to SqlBulkCopy from an IDataReader?

Unfortunately, no. :-( If a column is NOT NULL and your bulk insert maps to it, each row must have a value for that column.

Background: At the TDS level, a bulk insert is done by sending a SQL INSERT statement written using the external tools only syntax followed by TDS structures containing column metadata then row data. None of these provide a way to say "if a NULL value is in a non-NULLable column, treat the NULL as a DEFAULT."

What do people use in such a scenario?

Hum...it's frustrating when it feels like there should be built-in, simple solution and instead all the options are painful. :-/ Without knowing more about your situation, it's hard to know which option to recommend.

One additional option: the placeholder value approach. Prior to the insert, replace the "NULLs that should be defaulted" with a placeholder value. Then, post-insert, run an update(s) to replace the placeholder(s) with database-generated default values (e.g. UPDATE ... SET Col1 = DEFAULT WHERE Col1 = *placeholder*). Your application would have to know which non-nullable columns have defaults but wouldn't have to know how to compute the defaults. I don't care for it because it uses so-called magic numbers--but it is an option.

Upvotes: 2

Related Questions