What's the best way to write "nulls" to SQL database when presenting options in combobox?

Question

Hi Guys,

I'm writing a front end to a database, presenting options as comboboxes. And I need to be able to return nulls to the database.

I have a solution at the moment which involves 'fudging' a record into the query to populate the Datatable, then detecting the selection and hard coding null into my update statement if this item has been selected.

--GetDataByAvailableTechs query 
            SELECT     tblLEMSCredentialsId, TechNumber
            FROM         tblLEMSCredentials AS aLEMSCreds
            UNION ALL
            SELECT     '99999' AS Expr1, '<NONE>' AS Expr2

.

            //populate combo box
            BV_LEMSHIPDataSet.tblLEMSCredentialsDataTable dtAvailableTechs = taLEMSCreds.GetDataByAvailableTechs(selectedSerial);  
            cboTechNumber.DataSource = dtAvailableTechs;

.

            //Save back to DB
            if (lemsCredsID == 99999)
            {
                taDevice.UpdateQuery_Restage(null, selectedSerial);
            }
            else
            {
                taDevice.UpdateQuery_Restage(lemsCredsID, selectedSerial);
            }

Can anyone suggest a better way of doing this please? I need to make the application cope with another 5 similar fields and don't want to create a multitude of if else versions of my update.

G

Answer 1

In the past I've used a similar approach but used minus numbers for the NULL values - that way you can you treat anything less than zero as NULL in one if statement.

This assumes that your ID's in the table are always positive though :)

Answer 2

You may put an string value as the combobox 'null value', for example: "thenullvalue" then parse combobox value to nullable int type (int?). For proper selection you will get the desired value, for 'null selection' you will get null (parsing "thenullvalue" to int will give you null) :)

Then save the value to database just as:

int? lemsCredsID = parseInt(combobox.Value);

 //Save back to DB
taDevice.UpdateQuery_Restage(lemsCredsID, selectedSerial);

Depending of your db provider you might also need to convert nullable type null value to DBNull, like this:

taDevice.UpdateQuery_Restage((lemsCredsID==null ? DBNull : (int)lemsCredsID), selectedSerial);

Hope this helps.

Answer 3

Null object pattern. Make it return an empty string for the UI and provide a method call to extract the value when updating the database. In normal cases this will return your true value but the null object will override this behaviour to give you your null.

No magic numbers required and all you need to do is add this value to the start of the "bindable values" when you bind your control to the datasource.

Answer 4

If the value is intended to be null, pass null to the database would be a good solution, this reduces the extra logic handling for special values for nulls and it can be quite risky and ambiguous.

The view or application layer should be able to handle null either returned from database or passing into it.

e.g. you can convert null to string.Empty before binding the control as well as you can convert string.Empty to null before passing into the database.