Question only update changed fields?

[amishp11]

In a simple object model I'm trying to wrap some database tables with business objects.

In an effort to save bandwidth and # of fields on an update, I want to track to which properties are updated (set) and only update those to the database. I'm currently doing

            Public Property Description() As String
                Get
                    Return m_sDescription
                End Get
                Set(ByVal value As String)
                    LogChange("Description", m_sDescription, value) '<-------
                    m_sDescription = value.Trim
                End Set
            End Property

Where "LogChange" is a method that will add Parameter1 to a name value collection IF the new value is different from the old value. And then turn around and pass that name value collection into sql stored proc.

This work flow works fine, but I want to say I've seen some code snippets somewhere that do a much better job with this. But unfortunately, I'm unable to locate it.

Any thoughts/advice/snark?

 

[jmcilhinney]

If you're using stored procedures then what you want to do is probably not realistic. A stored procedure has a specific number of parameters, so you can't just pick and choose which field values to pass in. If a field hasn't changed then you could pass null to the sproc, but then how will you differentiate that from an actual null? You'd only be able to do so if the column didn't actually allow nulls. For nullable columns you'd have to add extra boolean parameters that indicated what the null meant.

If you want to be able to vary the number of field values you pass to the database then you would be better off building ad hoc SQL statements. That way the SQL will simply omit any fields/parameters that haven't changed.

 

[amishp11]

That's not really a stumbling block.

I just have the parateters in the storedProc as optional parameters (defaulted). And in the Update statement, wrap the fields with an ColumnName = IsNull(@Variable,ColumnName).

I was just looking for a way to override the property set. So that I don't have to manually call "LogChange" on every NEW property I create.

 

[jmcilhinney]

If you want to keep a record of each property that changes then you've got no choice but to execute some code when each property changes. Exactly how you do it is up to you but it's going to require at least one line in each and every property setter, as you already have.