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

        [System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
        public virtual int Update(MySqlDS.productsDataTable dataTable)
        {
            // call prepare commands to make sure we have the 
            // insert/update/delete commands set up 
            PrepareCommands();

            int res = 0;
            MySqlConnection conn = null;
            try
            {
                // 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;
                    conn.Open();
                }
                res = this.Adapter.Update(dataTable);
            }
            finally
            {
                // if we open the connection we should close it
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            // 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.