mardi 10 février 2015

How can I conditionally prevent a column from being updated in SQLite?

I have a table with CREATED and MODIFIED columns. I only want to insert the CREATED value once and have it be thereafter immutable. I know how to do this in a tedious way (write a "DoesRecordExist()" method and then alter the query and number of query parameters based on that), but surely there is a slicker way to accomplish this. After all, this has to be a common requirement (a "database pattern" if you will).


My code is this:



public void InsertUserSiteRecord(UserSite us)
{
using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText =
String.Format(
@"INSERT INTO UserSite (SiteNum, SerialNum, UserName, Created, Modified)
VALUES (@SiteNum, @SerialNum, @UserName, @Created, @Modified)");
cmd.Parameters.Add(new SQLiteParameter("SiteNum", us.SiteNum));
cmd.Parameters.Add(new SQLiteParameter("SerialNum", us.SerialNum));
cmd.Parameters.Add(new SQLiteParameter("UserName", us.UserName));
cmd.Parameters.Add(new SQLiteParameter("Created", us.Created));
cmd.Parameters.Add(new SQLiteParameter("Modified", us.Modified));

cmd.ExecuteNonQuery();
}
conn.Close();
}
}


...and I want to avoid having to do something like this:



public void InsertUserSiteRecord(UserSite us)
{
using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
if (!RecordExists(us.SiteNum, us.SerialNum, us.UserName))
{
cmd.CommandText =
String.Format(
@"INSERT INTO UserSite (SiteNum, SerialNum, UserName, Created, Modified)
VALUES (@SiteNum, @SerialNum, @UserName, @Created, @Modified)");
else
{
cmd.CommandText =
String.Format(
@"INSERT INTO UserSite (SiteNum, SerialNum, UserName, Modified)
VALUES (@SiteNum, @SerialNum, @UserName, @Modified)");
}
cmd.Parameters.Add(new SQLiteParameter("SiteNum", us.SiteNum));
cmd.Parameters.Add(new SQLiteParameter("SerialNum", us.SerialNum));
cmd.Parameters.Add(new SQLiteParameter("UserName", us.UserName));
if (!RecordExists(us.SiteNum, us.SerialNum, us.UserName))
{
cmd.Parameters.Add(new SQLiteParameter("Created", us.Created));
}
cmd.Parameters.Add(new SQLiteParameter("Modified", us.Modified));

cmd.ExecuteNonQuery();
}
conn.Close();
}
}

private bool RecordExists(String SiteNum, String SerialNum, String UserId)
{
// query the table to see if those three values exist in any record
}


Is there a SQL[ite] construct that is something like:



cmd.Parameters.AddOnlyIfColumnIsEmpty(new SQLiteParameter("Created", us.Created));


? Or how can this be best tackled?


Aucun commentaire:

Enregistrer un commentaire