Long overdue...

Way back in February I gave a webinar on using our Visual Studio integration product with MySQL.  At the time, our code did not integrate very well with the TableAdapter wizard.  To be precise, you really couldn't use stored procedures with a table adapter at all.  During the broadcast, someone asked me about using stored procedures and I told him that I would blog about it once I got it working.  Well, here we are.

Actually, we've had it working for a couple of weeks now but I'm just now getting to the blog post.  You know how it is.  There were several problems at play here but the biggest issues were the fact that we needed to provide an enumerator object for columns that would be returned by a stored procedure.  The second issue was needing to use the DbProviderSpecificTypeProperty attribute on our provider specific type property on the parameter class.  Let's look at each one of these in more detail.

During the TableAdapter wizard, there is a page where you select the procedures you want to use for each operation (select, update, insert, and delete).  Once you choose the procedure for the select operation, the wizard wants to enumerate the columns that will be returned from this procedure.  It does this because, later, it will want to map parameters to the other procedures to these columns.  To support this, we needed an enumerator object.  MySQL doesn't have the ability to execute a procedure but only return the metdata without making any other changes.  Therefore, when we execute the procedure to return the metadata, any other changes the procedure makes will happen.  So it is important to not do anything in this procedure except return the required columns.  Don't write any logs, update any counts, etc.

The second issue was the wizard was unable to properly determine the types of the parameters.  This is typical Microsoft since the provider includes a parameters schema collection that provides all this information.  That's too easy.  Instead the wonderful designers at Microsoft created a new attribute named DbProviderSpecificTypeProperty.  This allows the wizard to reflect on the parameter class, determine what property should be set with the provider specific type value, and set it.  It could have  used the generic DbType property but I guess that's just too easy.

So, to sum up, Connector/Net 5.1.2 now includes the full ability to use stored procedures to manage the data in a datatable or dataset.