<p>I frequently have problems dealing with <code>DataRows</code> returned from <code>SqlDataAdapters</code>. When I try to fill in an object using code like this:</p>

<pre><code>DataRow row = ds.Tables[0].Rows[0];
string value = (string)row;
</code></pre>

<p>What is the best way to deal with <code>DBNull's</code> in this type of situation.</p>

[{'answer_id': 26832, 'author': 'Dan Herbert', 'author_id': 392, 'author_profile': 'https://Stackoverflow.com/users/392', 'pm_score': 6, 'selected': True, 'text': '<p>Nullable types are good, but only for types that are not nullable to begin with.</p>\n\n<p>To make a type "nullable" append a question mark to the type, for example:    </p>\n\n<pre><code>int? value = 5;\n</code></pre>\n\n<p>I would also recommend using the "<code>as</code>" keyword instead of casting. You can only use the "as" keyword on nullable types, so make sure you\'re casting things that are already nullable (like strings) or you use nullable types as mentioned above. The reasoning for this is </p>\n\n<ol>\n<li>If a type is nullable, the "<code>as</code>" keyword returns <code>null</code> if a value is <code>DBNull</code>.</li>\n<li>It\'s <a href="http://www.codeproject.com/Articles/8052/Type-casting-impact-over-execution-performance-in" rel="noreferrer">ever-so-slightly faster than casting</a> though <a href="https://stackoverflow.com/a/496167/392">only in certain cases</a>. This on its own is never a good enough reason to use <code>as</code>, but coupled with the reason above it\'s useful.</li>\n</ol>\n\n<p>I\'d recommend doing something like this</p>\n\n<pre><code>DataRow row = ds.Tables[0].Rows[0];\nstring value = row as string;\n</code></pre>\n\n<p>In the case above, if <code>row</code> comes back as <code>DBNull</code>, then <code>value</code> will become <code>null</code> instead of throwing an exception. Be aware that if your DB query changes the columns/types being returned, <strong>using <code>as</code> will cause your code to silently fail</strong> and make values simple <code>null</code> instead of throwing the appropriate exception when incorrect data is returned so it is recommended that you have tests in place to validate your queries in other ways to ensure data integrity as your codebase evolves.</p>\n'}, {'answer_id': 26841, 'author': 'Pascal Paradis', 'author_id': 1291, 'author_profile': 'https://Stackoverflow.com/users/1291', 'pm_score': 1, 'selected': False, 'text': '<p>You can also test with <a href="http://msdn.microsoft.com/en-us/library/system.convert.isdbnull.aspx" rel="nofollow noreferrer">Convert.IsDBNull (MSDN)</a>.</p>\n'}, {'answer_id': 26853, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 3, 'selected': False, 'text': '<p>Add a reference to <code>System.Data.DataSetExtensions</code>, that adds Linq support for querying data tables.</p>\n\n<p>This would be something like:</p>\n\n<pre><code>string value = (\n    from row in ds.Tables[0].Rows\n    select row.Field&lt;string&gt;(0) ).FirstOrDefault();\n</code></pre>\n'}, {'answer_id': 27075, 'author': 'Manu', 'author_id': 2133, 'author_profile': 'https://Stackoverflow.com/users/2133', 'pm_score': 1, 'selected': False, 'text': '<p>I usually write my own ConvertDBNull class that wraps the built-in Convert class. If the value is DBNull it will return null if its a reference type or the default value if its a value type. \nExample: \n  - <code>ConvertDBNull.ToInt64(object obj)</code> returns <code>Convert.ToInt64(obj)</code> unless obj is DBNull in which case it will return 0.</p>\n'}, {'answer_id': 27082, 'author': 'Daniel Auger', 'author_id': 1644, 'author_profile': 'https://Stackoverflow.com/users/1644', 'pm_score': 4, 'selected': False, 'text': '<p>If you aren\'t using nullable types, the best thing to do is check to see if the column\'s value is DBNull. If it is DBNull, then set your reference to what you use for null/empty for the corresponding datatype.</p>\n\n<pre><code>DataRow row = ds.Tables[0].Rows[0];\nstring value;\n\nif (row["fooColumn"] == DBNull.Value)\n{\n   value = string.Empty;\n}\nelse \n{\n   value = Convert.ToString(row["fooColumn"]);\n}\n</code></pre>\n\n<p>As Manu said, you can create a convert class with an overloaded convert method per type so you don\'t have to pepper your code with if/else blocks.</p>\n\n<p>I will however stress that nullable types is the better route to go if you can use them. The reasoning is that with non-nullable types, you are going to have to resort to "magic numbers" to represent null. For example, if you are mapping a column to an int variable, how are you going to represent DBNull? Often you can\'t use 0 because 0 has a valid meaning in most programs. Often I see people map DBNull to int.MinValue, but that could potentially be problematic too. My best advice is this: </p>\n\n<ul>\n<li>For columns that can be null in the database, use nullable types.   </li>\n<li>For columns that cannot be null in the database, use regular types.  </li>\n</ul>\n\n<p>Nullable types were made to solve this problem. That being said, if you are on an older version of the framework or work for someone who doesn\'t grok nullable types, the code example will do the trick. </p>\n'}, {'answer_id': 27093, 'author': 'Dillie-O', 'author_id': 71, 'author_profile': 'https://Stackoverflow.com/users/71', 'pm_score': 1, 'selected': False, 'text': '<p>For some reason I\'ve had problems with doing a check against DBNull.Value, so I\'ve done things slightly different and leveraged a property within the DataRow object:</p>\n\n<pre><code>if (row.IsNull["fooColumn"])\n{\n   value = string.Empty();\n}\n{\nelse\n{\n   value = row["fooColumn"].ToString;\n}\n</code></pre>\n'}, {'answer_id': 49006, 'author': 'Steve Schoon', 'author_id': 3881, 'author_profile': 'https://Stackoverflow.com/users/3881', 'pm_score': 3, 'selected': False, 'text': "<p>If you have control of the query that is returning the results, you can use ISNULL() to return non-null values like this:</p>\n\n<pre><code>SELECT \n  ISNULL(name,'') AS name\n  ,ISNULL(age, 0) AS age\nFROM \n  names\n</code></pre>\n\n<p>If your situation can tolerate these magic values to substitute for NULL, taking this approach can fix the issue through your entire app without cluttering your code.</p>\n"}, {'answer_id': 66201, 'author': 'Meff', 'author_id': 9647, 'author_profile': 'https://Stackoverflow.com/users/9647', 'pm_score': 3, 'selected': False, 'text': '<p>I always found it clear, concise, and problem free using a version of the If/Else check, only with the ternary operator. Keeps everything on one row, including assigning a default value if the column is null.</p>\n\n<p>So, assuming a nullable Int32 column named "MyCol", where we want to return -99 if the column is null, but return the integer value if the column is not null:</p>\n\n<pre><code>return row["MyCol"] == DBNull.Value ? -99 : Convert.ToInt32(Row["MyCol"]);\n</code></pre>\n\n<p>It is the same method as the If/Else winner above - But I\'ve found if you\'re reading multiple columns in from a datareader, it\'s a real bonus having all the column-read lines one under another, lined up, as it\'s easier to spot errors:</p>\n\n<pre><code>Object.ID = DataReader["ID"] == DBNull.Value ? -99 : Convert.ToInt32(DataReader["ID"]);\nObject.Name = DataReader["Name"] == DBNull.Value ? "None" : Convert.ToString(DataReader["Name"]);\nObject.Price = DataReader["Price"] == DBNull.Value ? 0.0 : Convert.ToFloat(DataReader["Price"]);\n</code></pre>\n'}, {'answer_id': 234189, 'author': 'Dejan', 'author_id': 11471, 'author_profile': 'https://Stackoverflow.com/users/11471', 'pm_score': 0, 'selected': False, 'text': '<p>If you are concerned with getting DBNull when expecting strings, one option is to convert all the DBNull values in the DataTable into empty string.</p>\n\n<p>It is quite simple to do it but it would add some overhead especially if you are dealing with large DataTables. Check this <a href="http://www.ddejan.com/post/2008/10/24/Cleaning-DBNull-values-in-a-DataTable.aspx" rel="nofollow noreferrer">link</a> that shows how to do it if you are interested</p>\n'}, {'answer_id': 534305, 'author': 'Simon', 'author_id': 53158, 'author_profile': 'https://Stackoverflow.com/users/53158', 'pm_score': 1, 'selected': False, 'text': '<p>Brad Abrams posted something related just a couple of days ago\n<a href="http://blogs.msdn.com/brada/archive/2009/02/09/framework-design-guidelines-system-dbnull.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/brada/archive/2009/02/09/framework-design-guidelines-system-dbnull.aspx</a></p>\n\n<p>In Summary "AVOID using System.DBNull. Prefer Nullable instead."</p>\n\n<p>And here is my two cents (of untested code :) )</p>\n\n<pre><code>// Or if (row["fooColumn"] == DBNull.Value)\nif (row.IsNull["fooColumn"])\n{\n   // use a null for strings and a Nullable for value types \n   // if it is a value type and null is invalid throw a \n   // InvalidOperationException here with some descriptive text. \n   // or dont check for null at all and let the cast exception below bubble  \n   value = null;\n}\nelse\n{\n   // do a direct cast here. dont use "as", "convert", "parse" or "tostring"\n   // as all of these will swallow the case where is the incorect type.\n   // (Unless it is a string in the DB and really do want to convert it)\n   value = (string)row["fooColumn"];\n}\n</code></pre>\n\n<p>And one question... Any reason you are not using an ORM? </p>\n'}, {'answer_id': 675894, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>DBNull implements .ToString() like everything else. No need to do anything. Instead of the hard cast, call the object\'s .ToString() method.</p>\n\n<pre><code>DataRow row = ds.Tables[0].Rows[0];\nstring value;\n\nif (row["fooColumn"] == DBNull.Value)\n{\n   value = string.Empty;\n}\nelse \n{\n   value = Convert.ToString(row["fooColumn"]);\n}\n</code></pre>\n\n<p>this becomes:</p>\n\n<pre><code>DataRow row = ds.Tables[0].Rows[0];\nstring value = row.ToString()\n</code></pre>\n\n<p>DBNull.ToString() returns string.Empty</p>\n\n<p>I would imagine this is the best practice you\'re looking for</p>\n'}, {'answer_id': 12726127, 'author': 'NeverHopeless', 'author_id': 751527, 'author_profile': 'https://Stackoverflow.com/users/751527', 'pm_score': 1, 'selected': False, 'text': '<p>You should also look at the extension methods. <a href="http://shahanayyub.wordpress.com/2012/10/04/best-practice-to-check-for-dbnull-using-net/" rel="nofollow">Here</a> are some examples to deal with this scenerio.</p>\n\n<p>Recommended <a href="http://weblogs.asp.net/scottgu/archive/2007/03/13/new-orcas-language-feature-extension-methods.aspx" rel="nofollow">read</a></p>\n'}, {'answer_id': 16580743, 'author': 'Jürgen Steinblock', 'author_id': 98491, 'author_profile': 'https://Stackoverflow.com/users/98491', 'pm_score': 2, 'selected': False, 'text': '<p>It is worth mentioning, that <code>DBNull.Value.ToString()</code> equals <code>String.Empty</code></p>\n\n<p>You can use this to your advantage:</p>\n\n<pre><code>DataRow row = ds.Tables[0].Rows[0];\nstring value = row["name"].ToString();\n</code></pre>\n\n<p>However, that only works for Strings, for everything else I would use the linq way or a extension method. For  myself, I have written a little extension method that checks for DBNull and even does the casting via <code>Convert.ChangeType(...)</code></p>\n\n<pre><code>int value = row.GetValueOrDefault&lt;int&gt;("count");\nint value = row.GetValueOrDefault&lt;int&gt;("count", 15);\n</code></pre>\n'}, {'answer_id': 18943063, 'author': 'João Pinho', 'author_id': 2606833, 'author_profile': 'https://Stackoverflow.com/users/2606833', 'pm_score': 2, 'selected': False, 'text': '<p>Often when working with DataTables you have to deal with this cases, where the row field can be either null or DBNull, normally I deal with that like this:</p>\n\n<pre><code>string myValue = (myDataTable.Rows[i]["MyDbNullableField"] as string) ?? string.Empty;\n</code></pre>\n\n<p>The \'as\' operator returns null for invalid cast\'s, like DBNull to string, and the \'??\' returns\nthe term to the right of the expression if the first is null.</p>\n'}]
['https://Stackoverflow.com/questions/26809', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2191/']