CODEWITHSUNDEEP
c#sql-serverdataset
RegularNormalDayGuy
RegularNormalDayGuy

Reputation: 735

Handling null values when using TableAdapters

I'm using a dataset on my WinForms application which is connected to a SQL Server Database. The issue I'm having is when I'm using a table adapter's stored procedures. I sometimes have a long list of parameters to fill, and the problems occurs when a said parameter can be NULL and it comes from a row of the datatable.

For example, say I have a tableadapter named taTest and a dataTable named dtTest:

dtTestRow row = /* some row I fetched in previous treatment */
taTest.GetSimilarData(dt, row.col1, row.col2, row.col3)

But then, if row.col2 is NULL, the program crashed with a StrongTypingException complaining the column is DbNull. In the database, the NULL values are handled and processed as our Bussiness Logic dictactes, NULL is a possible value.

What I do at the moment is to check for null values before, like this:

dtTestRow row = /* some row I fetched in previous treatment */
if (row.Iscol2Null())
    taTest.GetSimilarData(dt, row.col1, null, row.col3)
else
    taTest.GetSimilarData(dt, row.col1, row.col2, row.col3)

As you can see, it can get tedious very quickly, if col1 and col3 can be NULL too... How can I handle those cases without doing a bunch of Ifs ?

EDIT Here are more details on the problem. All cols are int or string. The corresponding column in the database are INT or VARCHAR(X) (length differs, but it's never MAX). Since we treat NULL values differently, all those columns are nullable.

As for the dataset, the type is the same as in the database, and it is nullable. For ints, I tried changing the NullValue from Throw exception to Null, but I get an error stating that the property is not valid.

For strings, I can change the NullValue property to null and it works fine.

Upvotes: 0

Views: 1176

Answers (1)

Caius Jard
Caius Jard

Reputation: 74730

You need to juggle your null handling settings to be compatible with a) the business null requirements, b) the column nullability considerations and c) the parameter nullability for the GetSimilarData method

Simplistically you can stop the StrongTypingException if the column type is something that can be null without requiring Nullable (e.g. a string) by changing the action shown on the NullValue line in the property grid

ThrowException is the only option for primitive types, so it must be handed differently. Add more information to your question

enter image description here

As you can see, it can get tedious very quickly, if col1 and col3 can be NULL too... How can I handle those cases without doing a bunch of Ifs ?

Inlining it is probably the simplest

  taTest.GetSimilarData(dt, 
    row.Iscol1Null() ? null : ro.col1, 
    row.Iscol2Null() ? null : ro.col2, 
    row.Iscol3Null() ? null : ro.col3
  )

You could also establish your own properties on the typed datarow (double click the table in the designer to open the code behind, and ensure you're adding proeprties to the relevant row type) that do the same:

public int? Col1OrNull {
  get{
    return row.Iscol1Null() ? null : ro.col1;
  }
}

Then use it like:

taTest.GetSimilarData(dt, row.Col1OrNull ...

It's hard to give concrete advice because the definition of your method/stored procedure etc is still unknown. The approach above will wwork, from what I can deduce based on what you posted (GetSimilarData col2 argument will accept null, you claim, so it can't possibly be an int.. Means your datatable column spec is mismatched with respect to the parameter spec)

Note that your Get method seems mislabelled - Get is used for methods that return datatables, and Fill is used for methods that take datatables as a parameter and populate them

Upvotes: 1

Related Questions