MySql and Tableadapters

Update (5/5/2008): You can download the Visual Basic version of the code here

I think writing data access code is one of the most boring and tedious tasks in computer programming, so I welcome any help I can get to avoid doing it. That's were TableAdapters come in. Now if you want to use MySql as your database of choice using the MySql Connector/Net, you have a little problem with the auto-generated Tableadapters: they don't have an update/insert/delete commands so you cannot update/insert/delete (depending on what version of the connector you are using, you may get the insert command but not the update). This is due to a bug that is supposed to be fixed in version 5.2.2 (for VS2008). Anyway this is a workaround until MySql get's the connector fixed.

The trick is that the TableAdapter classes that Visual Studio/the Connector generates are marked as partial classes, sp we can extend the class and add the functionality that is missing (aka Update/Insert/Delete) ourselves. Since we are going to be adding some code by hand to the TableAdapter, the first thing we need to do is tell Visual Studio not to generate the Update commands by going into the advance options of the main query of the TableAdapter and uncheck the "Generate Insert, Update and Delete statements"


Now we need to create a partial class that will be named exactly the same as our generated class. For example, if my table adapter is called Products, the class that is generated will be called ProductsTableAdapter and will be in the namespace MyDataSetTableAdapters where MyDataSet is the name of your xsd data set.

The class will have at least two new methods. The first one I'll call PrepareCommands and will be in charge of setting up the CommandBuilder so we get the update/insert/delete commands sorted out:

    public partial class productsTableAdapter
        private bool m_isInitialized = false;

        private void PrepareCommands()
            if (!m_isInitialized)
                this.ClearBeforeFill = true;
                // get the table name from the generated table mappings
                string tableName = Adapter.TableMappings[0].DataSetTable;
                // create a generic select command
                Adapter.SelectCommand = new MySql.Data.MySqlClient.MySqlCommand(
			"Select * from " + tableName, Connection);
                // now just by creating the commandbuidler associated with the adapter, we
                // get the update, insert and delete commands
                MySql.Data.MySqlClient.MySqlCommandBuilder cb = 
			new MySql.Data.MySqlClient.MySqlCommandBuilder(Adapter);
                m_isInitialized = true;

Note that I'm checking if the TableAdapter hasn't been initialized before, since creating a CommandBuilder is very expensive (it actually has to query the database with the SelectCommand to get the schema) so we only want to do the PrepareCommands when is absolutely necessary.

The next method we need is an Update that takes a typed dataTable as a parameter, in our case a MySqlDS.productsDataTable

        public virtual int Update(MySqlDS.productsDataTable dataTable)
            // call prepare commands to make sure we have the 
            // insert/update/delete commands set up 

            int res = 0;
            MySqlConnection conn = null;
                // open the connection just in case it was not open by the time
                // we get called
                if( Adapter.SelectCommand.Connection.State != ConnectionState.Open )
                    // if we are opening the connection then we should close it! 
                    // so we store it in a local connection
                    conn = Adapter.SelectCommand.Connection;
                res = this.Adapter.Update(dataTable);
                // if we open the connection we should close it
                if (conn != null && conn.State != ConnectionState.Closed)
            // we are done!
            return res;

And that's it, we have a MySql TableAdapter that can add, update and delete and we had to do minimal coding on the data access side. Just one thing, we could do with just that one Update method or we could add overloaded Update methods with DataTabe, DataRow and DataRow[] using the Adapter.Update method.

So until we get a production level 5.2.2 Connector/Net from MySql we need to use some workarounds to make it work for us.


  • Jaime
    Can we get this in VB? Not that it would be too hard to translate but the code on this web page gets cut off on the right.
    Great workaround, I hope I can get it to work.

  • Hi Ryan,

    I've updated the post to include the Visual Basic version. You can download the entire file at the begining of the post

  • The code lines are cut off!
    (checked both in IE7 and FF)

  • I have found an easier workaround for this bug where you don't need to manually write the class! After using the TableAdapter Configuration Wizard to create the SQL for your INSERT, UPDATE, and DELETE methods, copy the text of the SQL statement (I'll use UPDATE for this example), then open up the Properties sheet for the TableAdapter in question. You will see that while the SelectCommand exists, the InsertCommand, UpdateCommand, and DeleteCommand say 'none'. Click on that UpdateCommand 'none' and select 'new'. Then simply paste your working SQL statement into the CommandText and ...tada! It works! You can now use the UPDATE method from where every you need it! Repeat for the INSERT and DELETE accordingly. I've been wrestling this one for over a week now so I danced a jig when I got this to work. Hope this helps!

  • Hello!
    I have MySQL connector 6.0 installed. I am stuck at 1st step selecting DataSource. Can you please tell me what DataSource you choose while establishing connection with MySQL db? I am lost here.

  • Mysql and tableadapters.. Nifty :)

  • Sad to see this issue still persisting.
    I have 2 sql statements (1 is a general get all records, other has a parameter for selective return of records) and cannot get updating to the table done via generated code :(.

  • @dave Which connector version do you have?

Comments have been disabled for this content.