I dont particulalrly like working with nulls. I know there is an option in SF for repacing null values with a different value. I am considering goint back a step to the database set up. I notice there is a property on each filed called 'Default Vlaue or Binding' . I have played around with this and noticed that if i insert ' ' (Space within single quotes) for chrarcter and date fields, or 0 (Zero) fro numeric fields, it seems to default to a Non Null when I add a record (In SSMS at any rate) i.e. Spaces for character fields and Zero for numeric fields, which is just what I want (Similar to VFP if you add a record)

I would welcome any suggestions, if this is a good idea or if there are any 'Gotchas' down the line.

My policy is to let the requirements guide this sort of decision.



- If the field MUST have data, then I setup the column to be NOT NULL in SQL and the field in the BO to require data (no nulls). I then use the SetDefaultValues event handler in the BO to make sure these fields are loaded appropriately. For things like a CreatedBy field, I'd setup the SetDefaultValues event handler within the BO itself to set it to the current user. For FKs, I usually handle it at the form level, were I'll know the FK value. The idea is that: a) the database won't ever have missing data and b) the BO will require that I deal with setting the data.



- If the field is optional, but I need to track if the user entered any data, then I'll use a NULL column in SQL and typically will use the replace when getting/setting the value in the BO. I.e. if the field is a string, I'll set it to return String.Empty if the field is NULL and to write NULL if the user enters String.Empty. This makes coding way simpler, but keeps the database cleaner too.



I used to use more DEFAULT constraints within SQL (and sometimes triggers), but the problem there is that the application won't know about these defaults until AFTER the record has been saved. I find it much easier to use the SetDefaultValues event handler to set defaults, which are set when the new record in the BO is created and thus show in any bound controls immediately.



Hope that makes sense and helps out with your question.

Hi Greg. thanks for yur detailed reply

If I use defaults within Sql, does this cause any issues in the BO , when I am saving, testing etc. What you seem to be saying is that, if I for instance Add a record, and I have a Default of Zero set up in Sql only , The BO will initially be populated with NUll (Unless I have a default set in SF). I guess if this is the case , and within the BO I try to , for instance , add two fields, once which may be a Null, this might give some errors

Hi Gerard,



Take a look at: http://forum.strataframe.net/Topic10223-6-1.aspx



This has worked very well for us for quite a while now.



Our policy on nulls is not to allow them unless they are really necessary, e.g. the dispatch date on a product can be null if it hasn't been dispatched yet therefore nulls are permitted.



In the BO mapper we set every column's Null Type to "Use Nullable Generic" (in the wizard) then go through and change all string (reference type) fields to: "Return Alternate on Nulls" and set the alternate value to String.Empty. And that's it - everything just works from then on and we can exercise control at the database level.



Cheers, Peter

Yeah, this can get confusing. It helps to understand how things are working (at least it helped me). The first thing to remember/understand is that the BO is essentially disconnected from the database, thus when you create a new record in the BO, it isn't talking to the database just yet. As I understand it, the process of creating a new row is something like this (kind of medium level view) when you add a new record to a BO from a maintenance form:



1. Add() is called on BO.

2. BO creates a DataRow and adds it to the underlying DataTable within the BO (standard ADO stuff). The values of the items in the row are all NULL.

3. The SetDefaultValues event is raised. You can set the values of items within the row at this time, before anybody will be able to access them.

4. The BO will update any bound controls. During this process the strongly typed properties of the BO are accessed. It is here that we will get errors if the value within the underlying DataRow is NULL and the property is just doing a straight access of that item. I.e. if the property is an int, and the value in the DataRow is NULL, a invalid casting exception is thrown as .NET can't cast a NULL into an int. The code would look something like this (this would be in a property called MyColumn):







When you setup the Return Alternate on Null or Use NullableGeneric options in the BO mapper, all it is doing is adding code to the property Get/Set (depending on setup) that handles the value being null in the DataRow. Something like this when you use Return Alternate on Null:







5. The user then enters information into some UI control.

6. Via binding, the value in the BO is updated. This is done by accessing the strongly typed properties and would use code something like:







You'll never get into trouble here (unless the property has a rule to not allow nulls, in which case the BO would handle raising that error and notifying the user), because the value within the DataRow is an object...it accepts anything. If you setup the field to set a null on alternate value, the code changes a bit to:







7. The user continues to enter data into bound controls, resulting in updating of the BO's underlying DataTable to have the appropriate values.

8. The Save button is clicked (or Save() is called).

9. The BO the produces a SQL call, which could be either a SQL statement or a call to a sproc. It will include the values of every field in the BO (minus those excluded via the FieldsToExcludeFromInsert property).

10. The SQL call is sent to SQL server to be executed. This is where any defaults setup at the db level come into play (and triggers, constraints, etc.). As you can see, the BO won't even know that these defaults have been set until it is refreshed with the data for this record (I'm a little fuzzy on if that happens automatically or if you'd need to make a call to have it happen).



Based on how I understand this works, you can see that even if you have defaults set at the db, the BO will throw an exception long before the db has a chance to set that default. This means that you have to also set defaults within the SetDefaultValues event handler. Now, the code posted by Peter at http://forum.strataframe.net/Topic10223-6-1.aspx shows a nice way to set those defaults from the defaults defined within the db. Kind of the best of both worlds.



I hope that helps you understand how defaults work (this is of course assuming I got the process pretty close to right).

Greg. many thanks for that very descriptive analysis of the steps, in plain english, of what happens.

I think to get the best of both worlds, I will probably set the default in the database, as well as set the defaults in SF.

I have very seldom come across a situation where I need nulls, and I think it is best to avois tme if at all possible.

Glad that helped.



Be sure to take a look at the code in the post referenced. In it, Peter is setting the default values in the BO from the defaults set in the database. That way you only have to maintain the defaults in one place. I'm not sure if it would work in you situation, but be sure to check it out.