CODEWITHSUNDEEP
c#.netsql-servervb.nett-sql
EMUEVIL
EMUEVIL

Reputation: 512

How To Handle DBNull When Pulling data from DataAdapter

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!

Upvotes: 0

Views: 1043

Answers (1)

Fabio
Fabio

Reputation: 32445

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.

Upvotes: 2

Related Questions