MySQL Bugs: #38922: .NET Typed Dataset Autogenerated UPDATE causes Concurrency Violation

Bug #38922	.NET Typed Dataset Autogenerated UPDATE causes Concurrency Violation
Submitted:	20 Aug 2008 16:41	Modified:	25 Aug 2009 16:45
Reporter:	Matthew Hill	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	2.0.50727	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Typed Dataset in .NET Tableadapter autognerates insert, update and delete methods. The autogenerated sql statement in the designer.cs files specifies the name of the database and the table...

As a comparison the MSSQL connector autogenerated code for these methdos only specifies an agnostic database [dbo].[datatable]  

The problem is that in my application the database name will be different depending on the user... The MSSQL approach essentially overides the database name with whatever value the connection string has as the database...

However the MYSQL connector code does not. No matter what database the actual connection specifies the autognerated Update commands database name is the one it refers to... If that Database doesnt exist it actually gibves an error indicating that the database doesnt exist...if the database does exist it will give the error

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

becuase the data is different.

How to repeat:
Create two databases. (db1   db2)

Create a dupicate table (tblname) in each database.

Three Columns

NameID  int   primary key
Name    varchar
Active  tinyint(1)

add  a record to the second database but leave the first database empty.

In Visual Studio 2008  
Create a class library project. Name is NameDLL
create a Typed Dataset (XSD) file (NameDAL.xsd)
Add a tableadapter to the XSD File. You will need to create a database connection. Create it to db1.

Add two querys  when adding the queries under advanced options select 

"Generate Insert, Update and Delete statements"
"Use optimistic concurrency"

First Query

SELECT     NameID, Name, Active
FROM         tblname

name teh query GetNameData

Second Query

SELECT Active, Name, NameID FROM tblname WHERE (NameID = @NameID)

name teh query GetNameDataByNameID

If you review the autogenerated code for the update method you will notice that the autogenerated methods specify db1  your update method should look like this

this._adapter.UpdateCommand.CommandText = "UPDATE `db2`.`tblname` SET `Name` = @Name, `Active` = @Active WHERE ((`NameID` = @Original_NameID) AND ((@IsNull_Name = 1 AND `Name` IS NULL) OR (`Name` = @Original_Name)) AND (`Active` = @Original_Active))";

Or somethign along those lines.

Now you create a BLL..

This is a class file that is similar to this.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using NameDLL.NameDAL.NameDALTableAdapters;
using NameDLL.NameDAL;

namespace NameDLL.NameBLL
{
    /// <summary>
    /// Summary description for NameBLL
    /// </summary>
    
    public class NameBLL
    {
        private tblnameTableAdapter _tblnameAdapter = null;
        public tblnameTableAdapter NameAdapter
        {
            get
            {
                if (_tblnameAdapter == null)
                    _tblnameAdapter = new tblnameTableAdapter();
                return _tblnameAdapter;
            }
        }

       

        public NameDAL.tblnameDataTable GetNameData()
        {
            return NameAdapter.GetNameData();
        }

        public NameDAL.tblnameDataTable GetNameDataByNameID(int? nameid)
        {
            return NameAdapter.GetNameDataByNameID(nameid.Value);
        }

        public bool UpdateName(int? nameid, string name, bool active)
        {
            NameDAL.tblnameDataTable names = NameAdapter.GetNameDataByNameID(nameid.Value);
            if (names.Count == 0)
            {
                return false;
            }

            NameDAL.tblnameRow namerow = names[0];
            
            
            namerow.Active = active;
            namerow.Name = name;

            int rowsAffected = NameAdapter.Update(namerow);

            return rowsAffected == 1;

        }

Build the class library.

Create an web application project.  
Set the web config to have a connection string to the db2 database.
Add areference to the namedll.dll build object from your class library project.

Add a button and an event handler that references the dll method to update the name table.

something like this.

NameDLL.NameBLL namebll = new NameDLL.NameBLL();

int nameid = 1;
string name = newname;
bool active = true;

bool up = namebll.UpdateName(nameid, name, active);

This should give you the error

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

If you delete the db1 database even though your webconfig connection string specifies db2 you should get an error that the databse doesnt exist.

Remeber it is vital to repeating this error that the datbase that the original tablke adapter is created with be a different database then the one in the applications configuration string..otheriwse it wont work.

Suggested fix:
Like the MSSQL connector the database name should be variableized so that the autogenerated commands use whatever database name exists in the connection string rather than the name of the database that the tableadpater was created pointing to.

Hi Matthew and thanks for your report.

I'm sorry but I lost you half-way through it...
Am I right to assume you are actually posting a feature request to "variablize" database name in c/NET (so that you can do what exactly)?
Isn't the obvious workaround to point different table adapters to different databases? Or have different SP's and table-adapters based on users privileges?
What is the c/NET version you're using?

Hi.

The commented for Mathew is correct, for those programmers who develop code with DataSets strongly Typed: the example is very clear. 

The code SQL related to the operations Select, Update, Delete, Insert from Typed Dataset, must not include the name of the database, since this one, is already defined in the string of connection. The mistake that has been reported makes totally impossible the development of web applications  where the concepts of strongly tiped data are applied. 

The MySQL Team should solve the problem that has been detected for benefit of the whole community.

Thanks Pepito for your clarification. I'm setting this to "Verified" based on your comment.
It is my opinion this should be checked together with Bug#33870.

Same problem still exists in 6.0.4.0 and caused me a *lot* of headache.

This bug makes it totally useless to use app.config/settings files to connect to different databases.

Simple fix:
Open the DeleteCommand/UpdateCommand of every affected TableAdapter in Query Builder and just save it -> Query Builder fixes the problem.

This is the same problem as reported in bug #33870

I receive the same error too.  Also, I don't see the correlation to bug #33870.

Matthew, I tend to believe Reggie as he wrote the SW himself.