Writing and locking a table in C # WinForms with PostgreSql and ADO.NET

Question

I am using the .NET Framework 4.6.1, WinForms, PostgreSQL 6.4beta4 and Npgsql and ADO.NET.

My current application is a multi-user application in which all users connect to the same database.

Data is bound to controls using a DataTable, BindingSource, and BindingNavigator.

I want to avoid the fact that two users can edit a DataRow at the same time. Since I want to implement this in a more general approach, I thought about creating a descendant of the DataTable and added the LockMode property (None, Row, Table) .

I found that you can use the ColumnChanged event in conjunction with a RowState to detect data changes.

Now I know if the user adds a new value, editing (RowState = modified) an existing one or just looks (RowState = Unchanged) in the record.

So I'm looking for a solution to block the DataRow when the user starts editing it. In the application, I want to display a message after the user goes (using the Bindingnavigator or programmatically) into a locked record.

Most of the solutions I found target MySql server similar to this one: How do I perform row locking? or Scope and IsolationLevel transaction lock table .

However, I am looking for a PostgreSQL solution, so even articles on this topic from MS ( https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx ) cannot be here used.

I would appreciate it if someone with experience in PostgreSQL and ADO.NET could help me here. Thanks.

Answer 1

To do this, you need to synchronize your customers.

I would add an extra column with null values ​​( RowIsBeeingEdited ), indicating the start time for editing the row. I would set the line editable / not on client app start of row [ RowIsBeeingEdited ].

I would also execute two signals: {UserStartedEditingRow} and {UserFinishedEditingRow}, which will apply to all clients, indicating that client X has started / finished editing line Y.

At the beginning of the edit line, I would set the line [ RowIsBeeingEdited ] = {now} and send a {UserStartedEditingRow} signal. At the end of editing, I would set the line [RowIsBeeingEdited] = null and send a signal {UserFinishedEditingRow}. Currently, active clients should receive both signals and set the line for editing / no.

Hope that makes some sense.

Answer 2

The suggestion provided by @lokusking sounds good. Easy maintenance and expandability.