[Mono-bugs] [Bug 507663] New: DbDataAdapter/DbCommandBuilder and MySql not working together on Mono
bugzilla_noreply at novell.com
bugzilla_noreply at novell.com
Wed May 27 21:51:57 EDT 2009
http://bugzilla.novell.com/show_bug.cgi?id=507663
User janvanderwatt at gmail.com added comment
http://bugzilla.novell.com/show_bug.cgi?id=507663#c1
Summary: DbDataAdapter/DbCommandBuilder and MySql not working
together on Mono
Classification: Mono
Product: Mono: Class Libraries
Version: 2.4.x
Platform: Other
OS/Version: openSUSE 11.1
Status: NEW
Severity: Major
Priority: P5 - None
Component: Sys.Data
AssignedTo: bnc-blr-team-mono at forge.provo.novell.com
ReportedBy: janvanderwatt at gmail.com
QAContact: mono-bugs at lists.ximian.com
Found By: Community User
Description of Problem:
Using a command builder with MySQL does not work on Mono in the EXPECTED WAY. A
work-around is possible, but this breaks MS.NET code.
https://bugzilla.novell.com/show_bug.cgi?id=377461 is probably related.
Investigation into (re-)writing my own MySQL command builder based on the Mono
2.4 code for the other types of databases lead me to believe that similar
problems would be seen on other databases due to the way that objects/methods
are redefined in the derived CommandBuilder classes, thereby hiding the base
class where the work is actually/should actually be done.
Steps to reproduce the problem:
1. Create a connection to a MySQL database
2. Create a MySQL data adapter
3. Create a MySQL command builder
4. Read a recordset and prepare records to be updated
5. Call adapter.Update()
Actual Results:
System.InvalidOperationException: Auto SQL generation during Update requires a
valid SelectCommand.
at System.Data.Common.DbDataAdapter.Update (System.Data.DataRow[] dataRows,
System.Data.Common.DataTableMapping tableMapping) [0x0028f]
in
/usr/src/packages/BUILD/mono-2.4/mcs/class/System.Data/System.Data.Common/DbDataAdapter.cs:690
Expected Results:
Update the record without error.
How often does this happen?
Always.
Additional Information:
//
---------------------------------------------------------------------------
// Problem #1 (usual case)
//
---------------------------------------------------------------------------
void problem_1() {
MySqlConnection connection = new
MySqlConnection("Server=gdbe.webhop.org;Uid=mono;Pwd=mono;Database=mono;");
MySqlDataAdapter adapter = new MySqlDataAdapter(
"SELECT * FROM people",
connection
);
MySqlCommandBuilder command_builder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
adapter.Fill(table);
grid_view.DataSource = table;
grid_view.DataBind();
table.Rows[0]["name"] = table.Rows[0]["name"];
DataTable changes = table.GetChanges();
Response.Write("Records requiring updates: " + changes.Rows.Count +
"<br />");
// On MS.NET, the following line works without a problem.
// On MONO 2.4, it generates this error:
// System.InvalidOperationException: Auto SQL generation during Update
requires a valid SelectCommand.
// at System.Data.Common.DbDataAdapter.Update (System.Data.DataRow[]
dataRows, System.Data.Common.DataTableMapping tableMapping) [0x0028f]
// in
/usr/src/packages/BUILD/mono-2.4/mcs/class/System.Data/System.Data.Common/DbDataAdapter.cs:690
Int32 records_updated = adapter.Update(changes);
Response.Write("Records updated: " + records_updated + "<br />");
command_builder.Dispose();
adapter.Dispose();
connection.Dispose();
}
//
---------------------------------------------------------------------------
// Problem #2 (workaround for mono, breaks ms.net)
//
---------------------------------------------------------------------------
void problem_2() {
MySqlConnection my_sql_connection = new
MySqlConnection("Server=test.gdbe.webhop.org;Uid=mono;Pwd=mono;Database=mono;");
MySqlDataAdapter my_sql_adapter = new MySqlDataAdapter(
"SELECT * FROM people",
my_sql_connection
);
MySqlCommandBuilder my_sql_command_builder = new
MySqlCommandBuilder(my_sql_adapter);
DbConnection connection = (DbConnection)my_sql_connection;
DbDataAdapter adapter = (DbDataAdapter)my_sql_adapter;
DbCommandBuilder command_builder =
(DbCommandBuilder)my_sql_command_builder;
DataTable table = new DataTable();
adapter.Fill(table);
grid_view.DataSource = table;
grid_view.DataBind();
table.Rows[0]["name"] = table.Rows[0]["name"];
DataTable changes = table.GetChanges();
Response.Write("Records requiring updates: " + changes.Rows.Count +
"<br />");
// On MONO 2.4, adding the following (unnecessary?) line allows the
update to work without a problem.
// On MS.NET, the update generates this error:
// Item has already been added. Key in dictionary: '@Original_id' Key
being added: '@Original_id'
// at
System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs
rowUpdatedEvent, DataRow dataRow) +1455242
// System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +732
// System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping) +40
// System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
+169
// The point is that setting the update command explicitly should not
be necessary
adapter.UpdateCommand =
(MySqlCommand)command_builder.GetUpdateCommand(true);
Int32 records_updated = adapter.Update(changes);
Response.Write("Records updated: " + records_updated + "<br />");
command_builder.Dispose();
adapter.Dispose();
connection.Dispose();
}
//
---------------------------------------------------------------------------
// MySQL database
//
---------------------------------------------------------------------------
The server shown can be used for resolving this issue.
The db/table schema is as follows:
CREATE DATABASE `mono`;
USE `mono`;
/*Table structure for table `people` */
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(40) NOT NULL,
`surname` varchar(80) default NULL,
`friends` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*Data for the table `people` */
insert into `people`(`id`,`name`,`surname`,`friends`) values (1,'jan','van der
watt',50),(2,'john','doe',NULL),(3,'jane',NULL,NULL);
//
---------------------------------------------------------------------------
// Inheritance
//
---------------------------------------------------------------------------
I could be mistaken, but I think the issue here lies in the fact that the
DbCommandBuilder class is NOT really used my the SqlCommandBuilder class (who
replaces ALL the base class functionality), but it IS probably used by the
MySqlCommandBuilder, and the bug is probably in the DbCommandBuilder base
class.
If you cast a SqlCommandBuilder to DbCommandBuilder, you _should_ see similar
errors - I cannot confirm this.
//
---------------------------------------------------------------------------
// Suggestions on providing consistent behaviour
//
---------------------------------------------------------------------------
System.Data.Common.DbCommandBuilder
Line 47 defines: DbDataAdapter _dbDataAdapter;
Line 421 defines:
public DbDataAdapter DataAdapter {
get { return _dbDataAdapter; }
set { if (value != null) _dbDataAdapter = value; }
}
Line 476 defines:
private DbCommand SourceCommand {
get {
if (_dbDataAdapter != null)
return _dbDataAdapter.SelectCommand;
return null;
}
}
System.Data.SqlClient.SqlCommandBuilder
Line 56 (re)defines: SqlDataAdapter adapter;
It should use the base class _dbDataAdapter and cast SqlDataAdapter to
DbDataAdapter.
Line 102 (re)defines:
public new SqlDataAdapter DataAdapter {
get { return adapter; }
set {
if (adapter != null)
adapter.RowUpdating -= new SqlRowUpdatingEventHandler
(RowUpdatingHandler);
adapter = value;
if (adapter != null)
adapter.RowUpdating += new SqlRowUpdatingEventHandler
(RowUpdatingHandler);
}
}
The event handling should probably go into the base class
AND
It should just use the base class property.
Line 244 (re)defines:
private SqlCommand SourceCommand {
get {
if (adapter != null)
return adapter.SelectCommand;
return null;
}
}
It should just use the base class property.
And so on.
--
Configure bugmail: http://bugzilla.novell.com/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA contact for the bug.
More information about the mono-bugs
mailing list