Search code examples
c#sqlbulkcopybitwise-or

Bitwise OR to define SqlBulkCopyOptions?

So I asked one of my senior Dev for help to figure out Transaction scope related problem and he came to my desk and used a different overload on SqlBulkCopy and for SqlBulkCopyOptions parameter he did something like below:

SqlBulkCopyOptions options = (SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints);

It works now, but I don't get what a bitwise or means here. I thought I had some understanding of it, never really used it, but this usage made me scratch my head. Yep, I didn't ask my senior to explain it to me.. I was hoping someone could help me understand what the statement does. Most of the bitwise or examples on the net are with some numbers, which I get (I think), but this?

Solution

  • An enum can be used as a flag if it has the [Flags] attribute and has enum values which are independent of each other:

    The definition of SqlBulkCopyOptions is here: https://github.com/Microsoft/referencesource/blob/master/System.Data/System/Data/SqlClient/SqlBulkCopyOptions.cs

    Ignore the use of bitshift syntax. The actual values are:

    Name              Hex  Dec  Pow 2   Binary
Default          =  0 =  0 =    0 = 00000000
KeepIdentity     =  1 =  1 =    1 = 00000001
CheckConstraints =  2 =  2 =    2 = 00000010
TableLock        =  4 =  4 =    3 = 00000100
KeepNulls        =  8 =  8 =    4 = 00001000
FireTriggers     = 10 = 16 =    5 = 00010000
UseInternalTxn   = 20 = 32 =    6 = 00100000

    Observe that each value is the next power of 2, which means in binary (last column) their bits are completely mutually exclusive.

    This means you can combine them in a way that lets you see each value is set, for example, if you want KeepIdentity and TableLock, that's 0x01 with 0x04. We use the OR operator, but on a per-bit basis, which gives us the behaviour we want:

    In binary:

    00000001
00000100 OR
--------
00000101

    Observe how the first and third bits are now 1.

    Thus, (KeepIdentity | TableLock == 5).

    This approach does not work for enum values which are not powers of two, for example, if KeepIdentity had a value of 1 and CheckConstraints had a value of 2 but TableLock had a value of 3, then in binary they are:

    00000001 KeepIdentity
00000010 CheckConstraints
00000011 TableLock

    Observe that by analysing the bits of 00000011 it is impossible to determine if this is the combination of KeepIdentity and CheckConstraints, or a single TableLock value. This is why flags enum values must be: 1. Powers of 2, and 2: mutually-exclusive (with exceptions for shorthand and combination values).