Thursday, December 6, 2007

Make your FormView handle nulls better

The FormView is a very powerful control that is very nice in additional in ASP.Net 2.0. It handles nulls fairly well. See the ADO.Net QuickStart (http://dotnetjunkies.com/QuickStartv20/aspnet/doc/data/advanced.aspx#nulls) for details on this. If you are using custom objects depending on how your custom objects are designed you can just modify the parameters as described on this blog. http://www.livingincode.com/web/blog/post/FormView-and-ObjectDataSource-with-nullable-types.aspx. However, if you have custom objects that for example don't use individual properties as parameters to the CRUD methods and instead pass entity classes, then the trick discussed in this blog won't work. However, someone commented that they added a few lines of code to solve the issue. I found that the few lines of code is the only way that works under my scenario (the one that I just described that didn't work). Here are the two places I had to add a small amount of code that loops through all the values being inserted or updated and if the string from the form is an empty string, then it is converted to a null. The null is then automatically bound to my custom object. The reason I can bind null to int, long, DateTime, etc is because I am using the Nullable versions of these. For example, int?, long?, and DateTime?. In my custom object I then convert nulls to DBNull when I do database inserts and updates. It is clean and easy to implement. protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e) { foreach (DictionaryEntry entry in e.Values) { if (string.IsNullOrEmpty(entry.Value as string)) { e.Values[entry.Key] = null; } } }
protected void FormView1_ItemUpdating(object sender, FormViewInsertEventArgs e) { foreach (DictionaryEntry entry in e.NewValues)
{ if (string.IsNullOrEmpty(entry.Value as string)) { e.NewValues[entry.Key] = null; } } }
Here is the idea of what my custom entity looks like. Note this is a very light weight object. It doesn't really do anything other than hold the table data. In my real object, I have properties that expose the protected variables shown here, but I have not shown here to reduce the amount of clutter here.

public class PersonEntity { protected int? _personID; protected string _firstName; protected string _lastName; protected string _street1; protected string _street2; protected string _city; protected string _state; protected string _zipCode; protected string _createdBy; protected DateTime? _createdDate; protected int? _age; protected int? _height; protected int? _weight; // NOTE: Properties that expose these member variables have been removed to reduce clutter. }

I have a DAL object that actually does all the CRUD operations on the entity. Here is a the method that is used when I need the load the data.

protected PersonEntity FillEntityFromReader(SqlDataReader reader) { PersonEntity entity = null;

try { if (reader != null && !reader.IsClosed) { entity = new PersonEntity(); entity.PersonID = reader[0] as int?; entity.FirstName = reader[1] as string; entity.LastName = reader[2] as string; entity.Street1 = reader[3] as string; entity.Street2 = reader[4] as string; entity.City = reader[5] as string; entity.State = reader[6] as string; entity.ZipCode = reader[7] as string; entity.CreatedBy = reader[8] as string; entity.CreatedDate = reader[9] as DateTime?; entity.Age = reader[10] as int?; entity.Height = reader[11] as int?; entity.Weight = reader[12] as int?; } } catch (Exception ex) { throw ex; }

return entity; } Here is how I check the properties for null so I know whether to convert the parameter to DBNull or not when I am doing update or insert operations. I have just pasted a some example for different data types. I am using customized version of the MS DAAB. In my version the last parameter is what determines if the DAAB inserts DBNull or the value passed. You'll notice string is different animal. It already can be null and does not use the HasValue, instead it uses string.IsNullOrEmpty() method. parameters[1] = _sqlServer.MakeInParam("@FirstName", System.Data.SqlDbType.NVarChar, 50, entity.FirstName, string.IsNullOrEmpty(entity.FirstName)); parameters[9] = _sqlServer.MakeInParam("@CreatedDate", System.Data.SqlDbType.DateTime, 0, entity.CreatedDate, !entity.CreatedDate.HasValue); parameters[10] = _sqlServer.MakeInParam("@Age", System.Data.SqlDbType.Int, 0, entity.Age, !entity.Age.HasValue);

No comments: