Databases
StackOverflow2013
Postsdbo
POMost efficient way to check for DBNull and then assign to a variable?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMost efficient way to check for DBNull and then assign to a variable?
    text
    copied!<p>This question comes up occasionally, but I haven't seen a satisfactory answer.</p> <p>A typical pattern is (row is a <strong>DataRow</strong>):</p> <pre><code> if (row["value"] != DBNull.Value) { someObject.Member = row["value"]; } </code></pre> <p>My first question is which is more efficient (I've flipped the condition):</p> <pre><code> row["value"] == DBNull.Value; // Or row["value"] is DBNull; // Or row["value"].GetType() == typeof(DBNull) // Or... any suggestions? </code></pre> <p><a href="https://stackoverflow.com/questions/184681/is-vs-typeof">This</a> indicates that .GetType() should be faster, but maybe the compiler knows a few tricks I don't?</p> <p>Second question, is it worth caching the value of row["value"] or does the compiler optimize the indexer away anyway?</p> <p>For example:</p> <pre><code> object valueHolder; if (DBNull.Value == (valueHolder = row["value"])) {} </code></pre> <p>Notes:</p> <ol> <li>row["value"] exists.</li> <li>I don't know the column index of the column (hence the column name lookup).</li> <li>I'm asking specifically about checking for DBNull and then assignment (not about premature optimization, etc.).</li> </ol> <p>I benchmarked a few scenarios (time in seconds, 10,000,000 trials):</p> <pre><code>row["value"] == DBNull.Value: 00:00:01.5478995 row["value"] is DBNull: 00:00:01.6306578 row["value"].GetType() == typeof(DBNull): 00:00:02.0138757 </code></pre> <p>Object.ReferenceEquals has the same performance as "=="</p> <p>The most interesting result? If you mismatch the name of the column by case (for example, "Value" instead of "value", it takes roughly ten times longer (for a string):</p> <pre><code>row["Value"] == DBNull.Value: 00:00:12.2792374 </code></pre> <p>The moral of the story seems to be that if you can't look up a column by its index, then ensure that the column name you feed to the indexer matches the DataColumn's name exactly.</p> <p>Caching the value also appears to be nearly <strong>twice</strong> as fast:</p> <pre><code>No Caching: 00:00:03.0996622 With Caching: 00:00:01.5659920 </code></pre> <p>So the most efficient method <em>seems</em> to be:</p> <pre><code> object temp; string variable; if (DBNull.Value != (temp = row["value"])) { variable = temp.ToString(); } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload