Pass DEFAULT keyword to non-nullable column with SqlBulkCopy

c# sqlbulkcopy sql-server

Question

Is it possible to send the DEFAULT keyword from an IDataReader to SqlBulkCopy?
Essentially the same as

INSERT INTO MyTable(NonNullableWithDefault)
VALUES (DEFAULT)

The target tables in my application contain columns that are not nullable and have a default constraint on them.
The data that has to be uploaded contains both values and empty columns.
It should apply the default constraint to rows when there is no value (DBNull).

As mentioned in this response, the SqlBulkCopy either expects you to submit a value or not supply a ColumnMapping at all.

Alternatives I'm thinking about

  • dividing the data to be provided in several column mappings (complex)
  • Check the database for defaults, then populate the datareader with them (slow)
  • Create a separate dump table without any restrictions (complex, maybe slow)

I know I'm not the only one that has this issue.
What do individuals utilize in a situation like that?

UPDATE

After considerable tinkering, I currently use a single query to get my whole database structure, which I then send 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 default value string's parsing is ugly and probably unsuitable for complex situations. But in our situation, it takes care of everything.

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:

  • Workaround for non-nullable default values
  • Unambiguous validation messages (like max length)
  • handle case-matching for column names

Disadvantages

  • processing a string of default values
  • cannot resolve default values on the sql side (like sprocs)
1
0
6/13/2017 8:02:09 AM

Accepted Answer

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

No, unfortunately:-( Should a column beNOT NULL Each row must include a value for that column, and your bulk insert must map to it.

Background: A bulk insert is performed at the TDS level by delivering a SQL INSERT statement that is written using the only external tools syntax, followed by a TDS architecture that contains column information before row data. None of them allow for the expression "if aNULL A non-monetary valueNULL able column, treat theNULL as aDEFAULT ."

What do people use in such a scenario?

Hum...irritating it's when there seems to be a built-in, straightforward answer but all the alternatives are unpleasant. :-/ It's hard to propose a course of action without knowing more about your circumstances.

There is also the placeholder value strategy. Replace the "before the insert"NULL with a placeholder value for any that ought to be defaulted. Run an update (or updates) after the insert to replace any placeholders with database-generated default values, such asUPDATE ... SET Col1 = DEFAULT WHERE Col1 = *placeholder* ). Although it wouldn't need to know how to calculate the default values, your application would need to know which non-nullable columns had them. It is a possibility, but I don't like it since it employs'magic numbers.

2
5/29/2017 3:29:51 PM


Related Questions





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