MySQL Bugs: #14412: MySqlCommandBuilder does not automatically open connections for update

Bug #14412	MySqlCommandBuilder does not automatically open connections for update
Submitted:	27 Oct 2005 21:46	Modified:	6 Nov 2007 15:27
Reporter:	Don Lopez	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	1.0	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
The MySqlCommandBuilder object creates update/insert/delete commands but does not automatically open the connection.  E.g:

Dim oDA As New MySql.Data.MySqlClient.MySqlDataAdapter("SELECT " & _
                "* FROM tblAltDates WHERE OrdID = '" & pOrdID & "'", Me.MySQLConnection1)

Dim CustCB As New MySql.Data.MySqlClient.MySqlCommandBuilder(oDA)
Dim dnDS As New DataSet()
-------------------------------------
Later on.....
-------------------------------------
Dim dnCDS As DataSet = dnDS.GetChanges
oDA.Update(dnCDS)

----------------------------
Fails, telling me that the connection must be valid and open.  The workaround is to put MySQLConnection1.Open() before the update and MySQLConnection1.Close() after the update.

The stock ADO.NET stuff under System.Data does this portion automatically, which is convenient because the name of the connection for the data adapter in question isn't always obvious once you get to the point where you want to do an update.

This only applies to update commands created with the command builder, manually entered update commands work perfectly.

How to repeat:
1)  Create a new MySQLConnection Object
2)  Create a new MySQLDataAdapter Object with SQL command text and a DB connection
3)  Create a MySQLCommandBuilder object using the data adapter in 2)
4)  Fill a data set, make changes, etc.
5)  Attempt to run MysqlDataAdapter.Update with the new dataset

I am not sure that MySqlCommandBuilder must automatically open connection. Could you please write here where I can read about the topic ?

Vasily,

It was my understanding that the MySQL.NET connector was intended to be fully compatible with existing ADO.NET objects. I ran into the problem when converting from the stock objects under System.Data to the MySQL provided Mysql.Data objects.

Basically, using the System.Data objects provided in the .NET framework (in my case OLEDB objects), you can use the command builder to automatically generate update/delete/insert statements from a SELECT statement given when you create the data adapter. Afterwards, the data adapter behaves exactly like one that was created with manually entered update/delete/insert statements.

Using the Mysql.Data objects, the data adapter behaves differently if you use the command builder to generate the update/delete/insert statements. Instead of just being able to run .Update on the data adapter, you have to manually run .Open() and then .Close(). However, if you specify the update/delete/insert statements manually, the MySQL.Data objects behave just like the System.Data objects.

This seems to be a bug related to the command builder method of the MySQLDataAdapter. The command is built correctly, but the .Update() method requires that you manually open the connection first, which isn't typical.

Again, not sure about a source for the documentation, but this can be repeated pretty easily if you have VS.NET handy. If this isn't a declared feature of the MySQL.NET connector, feel free to downgrade this to a feature request, not critical but makes migration slightly tedious in some cases.

This is a duplicate of 8131 which has been fixed.