SPROC to update record: how to handle unchanged values

Question

I'm calling a update SPROC from my DAL, passing in all(!) fields of the table as parameters. For the biggest table this is a total of 78. I pass all these parameters, even if maybe just one value changed.

This seems rather inefficent to me and I wondered, how to do it better.

I could define all parameters as optional, and only pass the ones changed, but my DAL does not know which values changed, cause I'm just passing it the model - object.

I could make a select on the table before updateing and compare the values to find out which ones changed but this is probably way to much overhead, also(?)

I'm kinda stuck here ... I'm very interested what you think of this.

edit: forgot to mention: I'm using C# (Express Edition) with SQL 2008 (also Express). The DAL I wrote "myself" (using this article). Its maybe not the latest state of the art way (since its from 2006, "pre-Linq" so to say but Linq works only for local SQL instances in Express anyways) of doing it, but my main goal was learning C#, so I guess this isn't too bad.

Answer 1

If you can change the DAL (without changes being discarded once the layer is "regenerated" from the new schema when changes are made), i would recomend passing a structure containing the column being changed with values, and a structure kontaing key columns and values for the update.

This can be done using hashtables, and if the schema is known, should be fairly easy to manipulate this in the "new" update function.

If this is an automated DAL, these are some of the drawbacks using DALs

Answer 2

You could implement journalized change tracking in your model objects. This way you could keep track of any changes in your objects by saving the previous value of a property every time a new value is set.
This information could be stored in one of two ways:

1. As part of each object's own private state
2. Centrally in a "manager" class.

In the first solution, you could easily implement this functionality in a base class and have it run in all model objects through inheritance.

In the second solution, you need to create some kind of container class that will keep a reference and a unique identifier to any model object that is created and record all changes in its state in a central store.
This is similar to the way many ORM (Object-Relational Mapping) frameworks achieve this kind of functionality.

Answer 3

There are off the shelf ORMs that support these kinds of scenarios relatively well. Writing your own ORM will leave you without many features like this.

I find the "object.Save()" pattern leads to this kind of behavior, but there is no reason you need to follow that pattern (while I'm not personally a fan of object.Save(), I feel like I'm in the minority).

There are multiple ways your data layer can know what changed and most of them are supported by off the shelf ORMs. You could also potentially make the UI and/or business layer's smart enough to pass that knowledge into the data layer.

Two options that I prefer:

1. Generating/hand coding update methods that only take the set of parameters that tend to change.
2. Generating the update statements completely on the fly.