code_junkie

Most efficient way to check for DBNull and then assign to a variable?

Nov 11th, 2011
73
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.38 KB | None | 0 0
  1. if (row["value"] != DBNull.Value)
  2. {
  3. someObject.Member = row["value"];
  4. }
  5.  
  6. row["value"] == DBNull.Value; // Or
  7. row["value"] is DBNull; // Or
  8. row["value"].GetType() == typeof(DBNull) // Or... any suggestions?
  9.  
  10. object valueHolder;
  11. if (DBNull.Value == (valueHolder = row["value"])) {}
  12.  
  13. row["value"] == DBNull.Value: 00:00:01.5478995
  14. row["value"] is DBNull: 00:00:01.6306578
  15. row["value"].GetType() == typeof(DBNull): 00:00:02.0138757
  16.  
  17. row["Value"] == DBNull.Value: 00:00:12.2792374
  18.  
  19. No Caching: 00:00:03.0996622
  20. With Caching: 00:00:01.5659920
  21.  
  22. object temp;
  23. string variable;
  24. if (DBNull.Value != (temp = row["value"])
  25. {
  26. variable = temp.ToString();
  27. }
  28.  
  29. object value = row["value"];
  30.  
  31. int columnIndex = row.GetOrdinal("Foo");
  32. string foo; // the variable we're assigning based on the column value.
  33. if (row.IsDBNull(columnIndex)) {
  34. foo = String.Empty; // or whatever
  35. } else {
  36. foo = row.GetString(columnIndex);
  37. }
  38.  
  39. int i; // can be reused for every field.
  40. string foo = (row.IsDBNull(i = row.GetOrdinal("Foo")) ? null : row.GetString(i));
  41. int bar = (row.IsDbNull(i = row.GetOrdinal("Bar")) ? -1 : row.GetInt32(i));
  42.  
  43. oSomeObject.IntMemeber = oRow["Value"] as int? ?? iDefault;
  44. oSomeObject.StringMember = oRow["Name"] as string ?? sDefault;
  45.  
  46. public static T? GetValue<T>(this DataRow row, string columnName) where T : struct
  47. {
  48. if (row.IsNull(columnName))
  49. return null;
  50.  
  51. return row[columnName] as T?;
  52. }
  53.  
  54. public static string GetText(this DataRow row, string columnName)
  55. {
  56. if (row.IsNull(columnName))
  57. return string.Empty;
  58.  
  59. return row[columnName] as string ?? string.Empty;
  60. }
  61.  
  62. int? id = row.GetValue<int>("Id");
  63. string name = row.GetText("Name");
  64. double? price = row.GetValue<double>("Price");
  65.  
  66. oSomeObject.IntMember = If(TryConvert(Of Integer)(oRow("Value")), iDefault)
  67. oSomeObject.StringMember = If(TryCast(oRow("Name"), String), sDefault)
  68.  
  69. Function TryConvert(Of T As Structure)(ByVal obj As Object) As T?
  70. If TypeOf obj Is T Then
  71. Return New T?(DirectCast(obj, T))
  72. Else
  73. Return Nothing
  74. End If
  75. End Function
  76.  
  77. Convert.IsDBNull()
  78.  
  79. int? myValue = (Convert.IsDBNull(row["column"]) ? null : (int?) Convert.ToInt32(row["column"]));
  80.  
  81. if (row["value"] != DBNull.Value)
  82. someObject.Member = row["value"];
  83.  
  84. public static IsDBNull<T>(this object value, T default)
  85. {
  86. return (value == DBNull.Value)
  87. ? default
  88. : (T)value;
  89. }
  90.  
  91. public static IsDBNull<T>(this object value)
  92. {
  93. return value.IsDBNull(default(T));
  94. }
  95.  
  96. IDataRecord record; // Comes from somewhere
  97.  
  98. entity.StringProperty = record["StringProperty"].IsDBNull<string>(null);
  99. entity.Int32Property = record["Int32Property"].IsDBNull<int>(50);
  100.  
  101. entity.NoDefaultString = record["NoDefaultString"].IsDBNull<string>();
  102. entity.NoDefaultInt = record["NoDefaultInt"].IsDBNull<int>();
  103.  
  104. ///<summary>
  105. /// Handles operations for Enumerations
  106. ///</summary>
  107. public static class DataRowUserExtensions
  108. {
  109. /// <summary>
  110. /// Gets the specified data row.
  111. /// </summary>
  112. /// <typeparam name="T"></typeparam>
  113. /// <param name="dataRow">The data row.</param>
  114. /// <param name="key">The key.</param>
  115. /// <returns></returns>
  116. public static T Get<T>(this DataRow dataRow, string key)
  117. {
  118. return (T) ChangeTypeTo<T>(dataRow[key]);
  119. }
  120.  
  121. private static object ChangeTypeTo<T>(this object value)
  122. {
  123. Type underlyingType = typeof (T);
  124. if (underlyingType == null)
  125. throw new ArgumentNullException("value");
  126.  
  127. if (underlyingType.IsGenericType && underlyingType.GetGenericTypeDefinition().Equals(typeof (Nullable<>)))
  128. {
  129. if (value == null)
  130. return null;
  131. var converter = new NullableConverter(underlyingType);
  132. underlyingType = converter.UnderlyingType;
  133. }
  134.  
  135. // Try changing to Guid
  136. if (underlyingType == typeof (Guid))
  137. {
  138. try
  139. {
  140. return new Guid(value.ToString());
  141. }
  142. catch
  143.  
  144. {
  145. return null;
  146. }
  147. }
  148. return Convert.ChangeType(value, underlyingType);
  149. }
  150. }
  151.  
  152. if (dbRow.Get<int>("Type") == 1)
  153. {
  154. newNode = new TreeViewNode
  155. {
  156. ToolTip = dbRow.Get<string>("Name"),
  157. Text = (dbRow.Get<string>("Name").Length > 25 ? dbRow.Get<string>("Name").Substring(0, 25) + "..." : dbRow.Get<string>("Name")),
  158. ImageUrl = "file.gif",
  159. ID = dbRow.Get<string>("ReportPath"),
  160. Value = dbRow.Get<string>("ReportDescription").Replace("'", "'"),
  161. NavigateUrl = ("?ReportType=" + dbRow.Get<string>("ReportPath"))
  162. };
  163. }
  164.  
  165. public static class DataExtensions
  166. {
  167. /// <summary>
  168. /// Gets the value.
  169. /// </summary>
  170. /// <typeparam name="T">The type of the data stored in the record</typeparam>
  171. /// <param name="record">The record.</param>
  172. /// <param name="columnName">Name of the column.</param>
  173. /// <returns></returns>
  174. public static T GetColumnValue<T>(this IDataRecord record, string columnName)
  175. {
  176. return GetColumnValue<T>(record, columnName, default(T));
  177. }
  178.  
  179. /// <summary>
  180. /// Gets the value.
  181. /// </summary>
  182. /// <typeparam name="T">The type of the data stored in the record</typeparam>
  183. /// <param name="record">The record.</param>
  184. /// <param name="columnName">Name of the column.</param>
  185. /// <param name="defaultValue">The value to return if the column contains a <value>DBNull.Value</value> value.</param>
  186. /// <returns></returns>
  187. public static T GetColumnValue<T>(this IDataRecord record, string columnName, T defaultValue)
  188. {
  189. object value = record[columnName];
  190. if (value == null || value == DBNull.Value)
  191. {
  192. return defaultValue;
  193. }
  194. else
  195. {
  196. return (T)value;
  197. }
  198. }
  199. }
  200.  
  201. int number = record.GetColumnValue<int>("Number",0)
  202.  
  203. public String TryGetString(SqlDataReader sqlReader, int row)
  204. {
  205. String res = "";
  206. try
  207. {
  208. res = sqlReader.GetString(row);
  209. }
  210. catch (Exception)
  211. { }
  212. return res;
  213. }
  214.  
  215. static void Main(string[] args)
  216. {
  217. object number = DBNull.Value;
  218.  
  219. int newNumber = number.SafeDBNull<int>();
  220.  
  221. Console.WriteLine(newNumber);
  222. }
  223.  
  224.  
  225.  
  226. public static T SafeDBNull<T>(this object value, T defaultValue)
  227. {
  228. if (value == null)
  229. return default(T);
  230.  
  231. if (value is string)
  232. return (T) Convert.ChangeType(value, typeof(T));
  233.  
  234. return (value == DBNull.Value) ? defaultValue : (T)value;
  235. }
  236.  
  237. public static T SafeDBNull<T>(this object value)
  238. {
  239. return value.SafeDBNull(default(T));
  240. }
  241.  
  242. public static class DBH
  243. {
  244. /// <summary>
  245. /// Return default(T) if supplied with DBNull.Value
  246. /// </summary>
  247. /// <typeparam name="T"></typeparam>
  248. /// <param name="value"></param>
  249. /// <returns></returns>
  250. public static T Get<T>(object value)
  251. {
  252. return value == DBNull.Value ? default(T) : (T)value;
  253. }
  254. }
  255.  
  256. DBH.Get<String>(itemRow["MyField"])
  257.  
  258. someObject.Member = ( DBNull.Value.Equals(row["value"]) ? null : row["value"]);
  259. or
  260. if (! DBNull.Value.Equals(row["value"]) someObject.Member = row["value"];
  261.  
  262.  
  263. or sometimes
  264. someObject.Member = ( DBNull.Value.Equals(row["value"]) ? someObject.Member : row["value"]);
Add Comment
Please, Sign In to add comment