Is it possible to update SQLite database only those properties that changed?

that way I am updating ALL properties of the Pupil entity. How can I update only those properties that really changed/ got dirty...

I know there exist sort of a pattern adding an IsDirty flag to the model/viewModel but adding for every property such a flag? Then do the whole checking and dynamically building the SQLiteParameter Collection is that not a bit too much overhead?

Would it not be better to just update ALL ?

using (SQLiteTransaction trans = DataAccess.ConnectionManager.BeginTransaction())
{
  using (SQLiteCommand com = new SQLiteCommand(DataAcc开发者_开发技巧ess.ConnectionManager))
  {
     com.Parameters.Add(new SQLiteParameter("@pupilId", pupil.Id));
     com.Parameters.Add(new SQLiteParameter("@firstname", pupil.FirstName));
     com.Parameters.Add(new SQLiteParameter("@lastname", pupil.LastName));
     com.Parameters.Add(new SQLiteParameter("@gender", pupil.Gender));
     com.Parameters.Add(new SQLiteParameter("@street", pupil.Street));
     com.Parameters.Add(new SQLiteParameter("@city", pupil.City));
     com.Parameters.Add(new SQLiteParameter("@postal", pupil.Postal));
     com.Parameters.Add(new SQLiteParameter("@phone", pupil.Phone));
     com.Parameters.Add(new SQLiteParameter("@email", pupil.Email));
     com.Parameters.Add(new SQLiteParameter("@extrainformation",pupil.ExtraInformation));

     com.CommandText = "UPDATE pupil SET firstname = @firstname, lastname = @lastname, gender = @gender, street = @street," + " city = @city, postal = @postal, phone = @phone, email = @email, extrainformation = @extrainformation WHERE pupilId = @pupilId";
     com.ExecuteNonQuery();
    }
     trans.Commit();
} 

---

Yes, you could keep track of every changed property but that won't be much fun and the code could quickly turn very ugly, so I would not advise it.

In this case and looking at the type of data you're updating, I think the added overhead will probably slow your application more down then a single database update will do.

Mind that fetching a single record from the database will cost more time then updating that record, so updating one property or eleven won't make much difference in total. Keeping track of a single object/entity might make sence, but that depends very much on the application and it's usage and how much time you're willing to invest.

---

For a given data model, you could put that IsDirty flag that you mentioned. In most cases this is good enough. But if for some reason you want to have this per property level, then you could go with an IsDirty flag (a bit flag as opposed to a bool flag, for instance). The overhead would be accomplished somewhere in your data model (base) anyway... so a common logic would handle all this overhead for you (write once and use automatically everywhere).

Another approach is to have a 2nd copy per data models you create (a backup copy holding the original values). So then u can check against it.

I would really only consider this if i/o time to the database needs faster than what you're seeing. If you data model has a whole bunch of properties, then it might be a good idea. Otherwise, its probably not worth the time to do it.