How To Handle DBNull When Pulling data from DataAdapter

Question

I've imported a MSSQL dataset into the DataSet Designer of Visual Studio but I'm having trouble with non-nullable types. My database allows for null integers (and i'd like to keep it that way if possible), but integers in .NET cannot be null. I would love to use nullable integers in .NET, but that datatype doesn't seem to be an option allowed in the drop-down list of the DataSet Designer's DataType property.

I'd prefer not to manually have to edit the DataSet.Designer class file every time I make a schema change in order to change the data-type to a nullable integer. I just want my property to return Null/Nothing instead of throwing a StrongTypingException when I reference a property that is DBNull.

I see that there is a NullValue column property in the DataSet Designer, but I am not allowed to select "Return Null" unless my datatype is an Object.

Is there a clean way to handle the DBNULL > Null conversion using settings in the designer or a neat wrapper function? I don't want to have to use the IsColumnNameNull() Functions for every column every single time I call one of these properties. I also don't want to have to cast Objects into their actual types every single time.

Let me know if I'm looking at this problem the wrong way. Any feedback is appreciated. Thanks!

msdn.microsoft.com/en-us/library/1t3y8s4s.aspx; Nullable types in .net. — Kevin Cook
– Kevin Cook, Commented Feb 17, 2017 at 20:29
you could also use int32.tryparse and treat all of the null integers as a negative value or some other default, if you want to keep them as normal integers. — Kevin Cook
– Kevin Cook, Commented Feb 17, 2017 at 20:32

Fabio · Accepted Answer · 2017-02-17 20:31:54Z

2

You can use generic extension method .Field<T>("ColumnName") from System.Data.DataSetExtensions assembly.

int? intValue = datarow.Field<int?>("CustomerId"); // c#

Dim intValue As Integer? = datarow.Field(Of Integer?)("CustomerId") ' vb.net

But you can create a class with correct types which represents data of one row instantiate it only once for use.

Or get rid of DataSet, DataTable and DataRow types and use light "POCO" classes with defined types for properties.

By using classes you made your application little bid more scalable for future changes - for example moving from DataSets to some ORM Frameworks, with classes you can change data access without affecting main business logic.

answered Feb 17, 2017 at 20:31

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EMUEVIL Over a year ago

Thanks for an expert perspective. I'll try out some of these solutions.

Collectives™ on Stack Overflow

How To Handle DBNull When Pulling data from DataAdapter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related