multiuser update

[csetzkorn]

Dear all,

Could someone please be so kind and provide me with some C# code for a ‘multi-user update situation’ (optimistic concurrency) similar to the one described here:

http://msdn.microsoft.com/library/defau ... rrency.asp

I have difficulties with this bit:

data_adapter.RowUpdated += new MySqlRowUpdatedEventHandler …

Many thanks in advance.

Christian

[Serious]

I have difficulties with this bit:
data_adapter.RowUpdated += new MySqlRowUpdatedEventHandler …

We tested this functionality and found no problems.
In this case MySQLDirect .NET works as common ADO .NET data provider in accordance with all rules described in corresponding documentation. For more information refer to MySQLDirect .NET help, MSDN and any of additional resources.
If you get any specific error (compiler error or runtime exception) when using RowUpdated event, please describe it in detail.

[csetzkorn]

Sorry, but where exactly is an example of multi-user update using MySQLDirect .NET in the help? I would appreciate any sample code using a timestamp and optimistic update. I have simulated a multi-user event but it didn’t work. The value in the dataset has overwritten the NEW value in the database. Perhaps I am doing something wrong … Would it be ok to send some code to the MySQLDirect .NET support team?

[Serious]

The link you've provided contains a good example of a test for optimistic concurrency in C#. This is the exact what you wanted.
The only thing you need to test this example is
1. rename all classes from System.Data.SqlClient namespace to appropriate classes from CoreLab.MySql namespace
2. create table 'Customers'.
If you have troubles with these simple steps here is an example I've tested:

Code: Select all

  static void Main() {

      // create table Customers
      MySqlConnection connection = new MySqlConnection("host=server;port=3307;database=test;user id=root;password=root;");
      MySqlCommand command = new MySqlCommand("CREATE TABLE Customers(CustomerID INTEGER,CompanyName VARCHAR(30),PRIMARY KEY(CustomerID))", connection);
      connection.Open();
      command.ExecuteNonQuery();
      // fill table
      command.CommandText = "insert into Customers values(10, 'fff')";
      command.ExecuteNonQuery();

      MySqlConnection nwindConn = new MySqlConnection("host=server;port=3307;database=test;user id=root;password=root;");

      MySqlDataAdapter custDA = new MySqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID", nwindConn);

      // The Update command checks for optimistic concurrency violations in the WHERE clause.
      custDA.UpdateCommand = new MySqlCommand("UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " +
        "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", nwindConn);
      custDA.UpdateCommand.Parameters.Add("@CustomerID", MySqlType.Int, 5, "CustomerID");
      custDA.UpdateCommand.Parameters.Add("@CompanyName", MySqlType.VarChar, 30, "CompanyName");

      // Pass the original values to the WHERE clause parameters.
      MySqlParameter myParm;
      myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", MySqlType.Int, 5, "CustomerID");
      myParm.SourceVersion = DataRowVersion.Original;
      myParm = custDA.UpdateCommand.Parameters.Add("@oldCompanyName", MySqlType.VarChar, 30, "CompanyName");
      myParm.SourceVersion = DataRowVersion.Original;

      // Add the RowUpdated event handler.
      custDA.RowUpdated += new MySqlRowUpdatedEventHandler(OnRowUpdated);

      DataSet custDS = new DataSet();
      custDA.Fill(custDS, "Customers");

      // Modify the DataSet contents.
      custDS.Tables[0].Rows[0]["CompanyName"] = "my company";

      // modify table
      command.CommandText = "update Customers set Companyname='yo'";
      command.ExecuteNonQuery();

      custDA.Update(custDS, "Customers");

      foreach (DataRow myRow in custDS.Tables["Customers"].Rows)
      {
        if (myRow.HasErrors)
          Console.WriteLine(myRow[0] + "\n" + myRow.RowError);
      }
    }

    protected static void OnRowUpdated(object sender, MySqlRowUpdatedEventArgs args) {

      if (args.RecordsAffected == 0) {
        args.Row.RowError = "Optimistic Concurrency Violation Encountered";
        args.Status = UpdateStatus.SkipCurrentRow;
      }
    }

[csetzkorn]

Could you please be so kind and point out why this doesn't work - doesn't update (the original data source has not been changed):

public void OnUpdate( Object source, DataListCommandEventArgs e )
{
string select_command_string = "select paper_uid, text, time_stamp from papers where paper_uid = @paper_uid;";
MySqlConnection connection = new MySqlConnection( Global.getConnectionString() );
MySqlParameter parameter;

MySqlDataAdapter data_adapter = new MySqlDataAdapter( select_command_string, connection );

parameter = new MySqlParameter( "@paper_uid", MySqlType.Int );
parameter.Direction = ParameterDirection.Input;
parameter.Value = 1;
data_adapter.SelectCommand.Parameters.Add( parameter );

data_adapter.DeleteCommand = new MySqlCommand( "delete from papers where paper_uid = @paper_uid;", connection );

parameter = new MySqlParameter( "@paper_uid", MySqlType.Int, 0, "paper_uid" );
parameter.Direction = ParameterDirection.Input;
parameter.Value = 1;
data_adapter.DeleteCommand.Parameters.Add( parameter );

data_adapter.InsertCommand = new MySqlCommand( "insert into papers ( text ) values ( @text );", connection );

parameter = new MySqlParameter( "@text", MySqlType.Text );
parameter.Direction = ParameterDirection.Input;
parameter.Value = ((Dart.PowerWEB.TextBox.HtmlBox) (e.Item.FindControl("HtmlBox1"))).Text;

data_adapter.InsertCommand.Parameters.Add( parameter );

data_adapter.UpdateCommand = new MySqlCommand( "update papers set text = @text where paper_uid = @paper_uid where time_stamp = @old_time_stamp;", connection );

parameter = new MySqlParameter( "@paper_uid", MySqlType.Int );
parameter.Direction = ParameterDirection.Input;
parameter.SourceVersion = DataRowVersion.Original;
parameter.SourceColumn = "paper_uid";
data_adapter.UpdateCommand.Parameters.Add( parameter );

parameter = new MySqlParameter( "@old_time_stamp", MySqlType.Int );
parameter.Direction = ParameterDirection.Input;
parameter.SourceVersion = DataRowVersion.Original;
parameter.SourceColumn = "time_stamp";

data_adapter.UpdateCommand.Parameters.Add( parameter );

parameter = new MySqlParameter( "@text", MySqlType.Text );
parameter.Direction = ParameterDirection.Input;
parameter.SourceVersion = DataRowVersion.Current;
parameter.SourceColumn = "text";
data_adapter.UpdateCommand.Parameters.Add( parameter );

data_adapter.RowUpdated += new MySqlRowUpdatedEventHandler(OnRowUpdated);

DataSet dataset = new DataSet();
data_adapter.Fill( dataset, "papers" );

try
{
dataset.Tables[ "papers" ].Rows[ 0 ][ "text" ] = ((Dart.PowerWEB.TextBox.HtmlBox) (e.Item.FindControl("HtmlBox1"))).Text;
data_adapter.Update( dataset, "papers" );
}
catch( Exception exception )
{
Response.Write( "Exception " + exception.ToString() );
}

paper_details_datalist.EditItemIndex = -1;
this.bindPaperDetailsGrid();
}

[csetzkorn]

sorry there were two errors. here is the other code. it works but it doesn't detect it when the original data source was changed by another process. Any pointers? Many many thanks ....

public void OnUpdate( Object source, DataListCommandEventArgs e )
{
string select_command_string = "select paper_uid, text, time_stamp from papers where paper_uid = @paper_uid;";
MySqlConnection connection = new MySqlConnection( Global.getConnectionString() );
MySqlParameter parameter;

MySqlDataAdapter data_adapter = new MySqlDataAdapter( select_command_string, connection );

parameter = new MySqlParameter( "@paper_uid", MySqlType.Int );
parameter.Direction = ParameterDirection.Input;
parameter.Value = 1;
data_adapter.SelectCommand.Parameters.Add( parameter );

data_adapter.DeleteCommand = new MySqlCommand( "delete from papers where paper_uid = @paper_uid;", connection );

parameter = new MySqlParameter( "@paper_uid", MySqlType.Int, 0, "paper_uid" );
parameter.Direction = ParameterDirection.Input;
parameter.Value = 1;
data_adapter.DeleteCommand.Parameters.Add( parameter );

data_adapter.InsertCommand = new MySqlCommand( "insert into papers ( text ) values ( @text );", connection );

parameter = new MySqlParameter( "@text", MySqlType.Text );
parameter.Direction = ParameterDirection.Input;
parameter.Value = ((Dart.PowerWEB.TextBox.HtmlBox) (e.Item.FindControl("HtmlBox1"))).Text;
data_adapter.InsertCommand.Parameters.Add( parameter );

data_adapter.UpdateCommand = new MySqlCommand( "update papers set text = @text where paper_uid = @paper_uid AND text = @old_text;", connection );

parameter = new MySqlParameter( "@paper_uid", MySqlType.Int );
parameter.Direction = ParameterDirection.Input;
parameter.SourceVersion = DataRowVersion.Original;
parameter.SourceColumn = "paper_uid";
data_adapter.UpdateCommand.Parameters.Add( parameter );

parameter = new MySqlParameter( "@old_text", MySqlType.Text );
parameter.Direction = ParameterDirection.Input;
parameter.SourceVersion = DataRowVersion.Original;
parameter.SourceColumn = "text";
data_adapter.UpdateCommand.Parameters.Add( parameter );

parameter = new MySqlParameter( "@text", MySqlType.Text );
parameter.Direction = ParameterDirection.Input;
parameter.SourceVersion = DataRowVersion.Current;
parameter.SourceColumn = "text";
data_adapter.UpdateCommand.Parameters.Add( parameter );

data_adapter.RowUpdated += new MySqlRowUpdatedEventHandler(OnRowUpdated);

DataSet dataset = new DataSet();
data_adapter.Fill( dataset, "papers" );

try
{
dataset.Tables[ "papers" ].Rows[ 0 ][ "text" ] = ((Dart.PowerWEB.TextBox.HtmlBox) (e.Item.FindControl("HtmlBox1"))).Text;
data_adapter.Update( dataset, "papers" );
}
catch( Exception exception )
{
Response.Write( "Exception " + exception.ToString() );
}

paper_details_datalist.EditItemIndex = -1;
this.bindPaperDetailsGrid();
}

[Serious]

The problem might be here, but it is hard to say without your table definition:

Code: Select all

parameter = new MySqlParameter( "@paper_uid", MySqlType.Int );
parameter.Direction = ParameterDirection.Input;
parameter.SourceVersion = DataRowVersion.Original; // remove this
parameter.SourceColumn = "paper_uid";
data_adapter.UpdateCommand.Parameters.Add( parameter );

In case you want to learn how to make it right way please use DataAdapter design-time features. When you generate INSERT/UPDATE/DELETE commands choose all fields from list as key fields.

[csetzkorn]

Dear all,

I am sorry for all the stuff above. Everything actually works fine. There was a problem with my program logic (only filled the dataset after pressing the update button ... thus couldn't detect simulated concurancy situation )

Thanks again for your help.

Chris