Bitwise OR to define SqlBulkCopyOptions?

bitwise-or c# sqlbulkcopy

Question

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?

Accepted Answer

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).


Popular Answer

The principle is exactly the same with an enum as it is with numbers, as the underlying type of an enum is always an integral type. If you look at the declaration of SqlBulkCopyOptions, you'll see that its members have underlying values that are powers of two so that they can be combined in this way.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why