I have "Element" objects in my application which are stored in Postgres DB Tables. These elements are edited via various editors in the user interface and the users can open multiple editors at the same time. So, I need to update the data in the editors (for all clients and every open editor) when a change occurred in DB. Anyways, I tried to build a LISTEN/NOTIFY mechanism for this purpose. Like this:
public class Element
{
public int Id { get; set; }
public string Name { get; set; }
public void UpdateInDB()
{
if (Id > 0)
{
using (var cmd = new NpgsqlCommand("UPDATE elements SET name = @v1 WHERE id = @v2", DBOperations.DBConn))
{
cmd.Parameters.AddWithValue("v1", Name);
cmd.Parameters.AddWithValue("v2", Id);
cmd.ExecuteNonQuery();
}
using (var cmdNotify = new NpgsqlCommand("NOTIFY elements_upd , '" + Id.ToString() + "'", DBOperations.DBConn))
{
cmdNotify.ExecuteNonQuery();
}
}
}
public void UpdateFromDB(int id)
{
if (id > 0)
{
using (var cmd = new NpgsqlCommand("SELECT id, name FROM elements", DBOperations.DBConn))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Id = (int)reader.GetValue(0);
Name = (string)reader.GetValue(1);
}
}
}
}
}
public class ElementEditor
{
public Element AssignedElement = null;
public ElementEditor()
{
DBOperations.DBConn.Notification += DBConn_Notification;
using (var cmd = new NpgsqlCommand("LISTEN elements_upd", DBOperations.resourceDBconn))
{
cmd.ExecuteNonQuery();
}
}
private void DBConn_Notification(object sender, NpgsqlNotificationEventArgs e)
{
if (AssignedElement!=null)
{
if (e.Condition == "elements_upd" && e.AdditionalInformation == AssignedElement.Id.ToString())
{
AssignedElement.UpdateFromDB(AssignedElement.Id);
}
}
}
}
The problem is, I cannot call Element.UpdateFromDB() from ElementEditor.DBConn_Notification(). I get the error A command is already in progress: NOTIFY elements_upd , '1'' (if the AssignedElement.Id = 1). I don't want to send all the "Element" data in NOTIFY's payload. I need to send the updated Id and make another query.
This seems like a very basic problem. However I couldn't find a similar question & answer in the site.
Up to now, the only workaround I could come up with is to mark the change in the event handler and using a timer to execute the change (which is disgusting).
So is there a way to create the necessary command synchronization? Or any other suggestion?