In a post from a long time ago, Eric Gunnerson asks about persisting complex objects. I’m working on a project that is using the CSLA.NET framework. One of the early decisions we made was how to get data updates back to the database. We chose to use Eric’s preferred method, which is change tracking. Eric also likes using serialization. That’s built into CSLA.NET (as well as .NET, of course), so we didn’t have to make a deliberate decision to use serialization.
One decision to make with persisting the objects is the actual mechanism for the updates. We ruled out using a dataset because we wanted to avoid the extra overhead that comes with a dataset. The examples from Rocky’s book all use stored procedures. The stored procedures have to have a parameter for every field. This means you are passing every field to the database, even when they did not change. Another option is dynamic SQL.
I had done some research in the past on the debate over stored procedures vs. dynamic SQL. One of the key issues in the debate is controlling access to the database. In some environments, dynamic SQL might mean that you have to give users access to all tables, and that might not be desirable. With an internal business application, the security can be handled through account impersonation in Enterprise Services or ASP.NET. Only one account needs to be given access to the database. In our environment, dynamic SQL seemed like a viable choice.
When profiling updates with “old-fashioned” ADO Recordsets, I observed that the recordsets generate dynamic SQL. The SQL was bloated, but reliable, and worked well with the optimistic concurrency model. I wanted to use this model, but in a more efficient manner.
In its simplest form, here is the format of a SQL statement that updates a record and also provides optimistic concurrency protection:
Set Field1 = NewValue
Where PrimaryKey = KeyValue And Field1 = OriginalValue
With our decision to use dynamic SQL inside the business object, the business object will already have most of the information needed to generate this statement. It has to know what table(s) are used to load the data. It has to know what columns are used to load the data, including the primary key value. It has to know the current value to update the database. The only optional piece is the original value. The original value is usually overwritten when the user makes changes.
One option to manage this is to double the number of variables. For each m_Field1, you would also have an m_orig_Field1, for example. This technique forces the business object developer to write two lines of code when setting each field value.
Since the update statement could be manufactured mechanically with all the necessary information, we felt there should be a way to encapsulate the necessary information. We have created “smart data” classes for the basic data types (string, integer, double, date, and boolean). Each smart data type knows the column name it is associated with, the original database value, and the current value. It turns out this is enough for efficient updates.
These smart data objects share a common interface. The interface defines three read-only properties: Column name, original value, and current value. A DataUpdater object uses this interface to generate the update statement.
Within the business object, the following lines of code are used to update a sample business object:
Dim updater As New DataUpdater(“MyTable”, m_KeyValue)
Within the updater, the SQL statement is generated and run against the database. As an added bonus, the smart data objects which have a concept of an “empty” value (i.e. string and date) also handle translating the empty value into a null value for the database.
The smart data objects are also easy to use when fetching the data. Before the smart data objects, fetch code looked like “m_Field1 = dr.GetString(“Field1”). dr refers to a SafeDataReader, which is part of the CSLA.NET framework. With smart data, the line is written as m_Field1 = New SmartString(dr, “Field1”).
The smart data object handles all the details of retrieving the value from the data reader and setting the original and current values.
We’ve added other capabilities to the smart data objects that help our Windows Forms classes, too. But that is outside of the persistence topic. These smart data objects are working very well for us. They do create more overhead, but nothing like a dataset. If you have a similar environment, perhaps you can benefit from this concept as well.