How Do YOU Handle NULL Values?

[jfrost10]

Hey guys,

ok, here's the scoop: having a big debate about NULL values in our sql server 2000 database, and it looks like we'll be using them in some occasions.

I was just curious to know how you guys handled yoru null values. For instance:
A field can have no value assigned to it (lets say an integer field), but if you try and assign the empty field to an int, but don't want the integer to store a 0, you want it to store a null, and ultimately save that back to the sql server, how do you set that up?!

Do you make everything of type variant? Or is there another way to tackle nulls on the presentation/logic layer?

Thanks guys,

Jack

 

[chiph]

I would use an additional boolean variable for each field which could be NULL to my form. If the user leaves the field blank, set the flag bMyFieldName_IsNull to true. I would use a Type or a Class to pass the values from the form to my business layer or data layer, where if would look at the flag when populating my SQL via a parameter.
If bMyFieldName_IsNull then put a NULL in the SQL, otherwise put the actual value there from iMyFieldName.

It's a little clunky because it doubles the number of variables you have to keep track of (but that's why you use a struct/class to pass them around). It has the advantages that you're not storing "special" values in with your data (makes reporting easier), and the variables that hold the data can be strongly typed (an int holds a number, as opposed to a variant that could hold anything). It also allows you to have better separation between the presentation parts of your program and the part that does the real work.

Chip H.

 

[jfrost10]

Hey chip,

Thanks for the response. Question for you:

So, if I have a class for employees that has
intEmpID
strEmpName
strEmpLocation

I'd also have to have flags for each of those for is null, and each field on my form would have to be evaluated?

So my form code would be something like:

If TextBox1.Text = "" Then
objEmployee.EmpID_IsNull = True
End If


And in my Object code, when I go to trigger my insert stored proc, I have to evaluate each boolean, so the code would be:

If EmpID_IsNull = True Then
'Code to insert null value as parameter
Else
'Code to insert the actual string variable
End If

???

Just seems like alot of extra code to check each field. If my object had like 50 properties, that would definately be crazy! How would you construct your object/code to handle this, or would you pass everything and let the stored proc deal with it?

Thanks again


jack

 

[getimran]

hi,


Why not work with " DBNULL " Data type which is created for that specific purpose.

LIke :
Dim v_alNull as DBNULL




Bye
Imran Make habit of marking posts as helpful or expert post, if they do solve your problems.

 

[jfrost10]

because its the same problem:

and int can't be converted to dbnull, and dbnull can't be converted to an int.

So if I have
intEmpID
strEmpName
strEmpLocation

then I'm still doing the checks, except instead of a flag I'd pass a dbnull variable.

less code, but still kinda messy though


Jack

 

[olichap]

If you use a dataset you can certainly set the row field to allownull = true (the default value I believe). Then when updating the user input/modified records to your table your null fields should still be null.

"but if you try and assign the empty field to an int"-when I read this I picture a basic user interface consisting of text boxes that the user populates (or doesn't). If a box (or however the user inputs data) is not populated with a proper value just don't set row.field to that value. Then the row values that are defaulted to null, until populated, remain null.

O.

 

[jfrost10]

Hey O,

Problem numero uno: I'm not using a dataset.

I have objects coded that have their properties assigned from the fields in the interface. The values get passed in, assigned to the properties, then are passed through my object's save sub to a stored procedure. If I want to pass nulls, I have to check each property for either "" or 0 before sending, which will require alot of if/else statements.

Thats the problem


Jack

 

[chiph]

Jack -

[tt]If EmpID_IsNull = True Then
'Code to insert null value as parameter
Else
'Code to insert the actual string variable
End If[/tt]

Yes, that's how I'd do it. You could be a little more compact and use the IIf() trinary operator. But it's essentially the same thing.

  myParameter = new SQLParameter("ParameterName", SQLDataType.VarChar, 50)
  myParameter.Value = IIf(MyValue_IsNull, DBNull.Value, MyValue)

Yes, it's ugly. But it doesn't have any limitations regarding type of data you apply it to. You could have a nested class variable that represents a phone number (country code, area code, exchange, number, extension), and just use one IsNull boolean variable for it. That'll save you some time/space, anyway.

One thing you can do to optimize the amount of memory used, is order the member variables by data type. Ints, Longs, types, & enums go first because they're known sizes. Floating points next. Then your object references (they're always 8 bytes). Put your booleans next (compiler might optimize 32 booleans into a single word). Lastly, put your strings, as they vary in size. Seems trivial, but could have a big impact if you have millions of objects.

A guy at work & I have been going 'round and 'round about how to pass data between logical layers in your program. He says to use a dataset, as it's part of the FCL, and why not take advantage of it. I say that you should avoid doing any data access outside the data layer, and using a dataset to pass stuff around doesn't prevent someone in a higher layer from accessing the database directly, bypassing all your rules. I say you should use objects or XML to pass between layers. I think I've almost got him convinced

Chip H.