MySqlUtil - TableAdapters for MySql

• Download MySqlUtil Solution - 677 Kb
• Download demo project - 207 Kb

Introduction

This program reads the MySql informational_schema database and, after the user has selected the target database, creates a C# source file containing typed dataset and TableAdapter classes for the contained tables.

The code produced is similar to that produced for SQL Databases, but it does not include

• XML Serialization
• Designer code

Before the code is generated, the program displays the names of the base identifiers it is going to use, giving the user the chance to change them (MySql seems to be case insensitive and table names such as 'CustomerOrders' seem to be stored as 'customerorders' ).

Once these parameters have been entered, they can be saved to a file (extension .tds) and loaded next time the program is run using the buttons shown above.

Background

The reason I developed this was because I was using SQL Server Express and wanted to change to MySql (which is far easier to deploy I believe) but still use the basic program structure I had developed (ie TableAdapters developed with DataDesigner).

I also found DataDesigner a bit of a pain when I changed the database and kept on losing procedures I had added.

You might find the SQL I have employed in the TableAdapters a bit wanting (which I understand as it is not my strongest attribute), but the idea is you change it to suit yourself.

MySql Connector Net

The program uses MySql Connector Net 1.0.7 as downloaded from the MySql site. However, this version seems to be stuck in a time warp, and some features are missing.

The downloaded solution includes a modified version of this code (MySql Connector Net SE) which adds the following

• support for unsigned numeric types
• bit MySql type
• text MySqlType

This doesn't pretend to be a definitive change of the code, it just reflects changes that I made to cater for my needs. The downloaded program references this version, but you can reference the official version if you want. I found that the easiest way to debug it was to include the project in the MySqlUtil solution (as is the case with the downloaded code) and when the program crashed because of unknown types to debug it.

Using the code

1. Start the program and enter an administrative user and password (ie probably root) and click on the Connect button.
2. When connected, select the target database from the drop-down combo box.
   The program should do its thing and come back with the table (as shown above) detailing what it is going to do with the dataset.
3. Make any changes to the default values. Clicking on the Directory text box, brings up a directory dialog for the output file.
4. Click on the Generate Typed dataSet button and the code file will be produced.
5. Clicking the Save Parameters button will save the connection information and class names etc to a file. Then, in future, when the program starts, clicking the Load Parameters button will bring up an Open File dialog which will allow the previously saved information to be loaded.

Generated Code

Code generation (sort of) follows that generated by DataDesigner for Microsoft SQL Server.

Using the sample above, some sample code generated is

public partial class SampleDataSet : System.Data.DataSet
namespace SampledbDataSetData
{
    #region SampleDataSet Definition
    public partial class SampleDataSet : System.Data.DataSet
    {
        CustomersDataTable tableCustomers;
        
        OrdersDataTable tableOrders;
        DataRelation relationFK_Orders_Customers;
        DataRelation relationFK_Orders_Products;
        
        ProductsDataTable tableProducts;
        ....etc

        // ***************************************************************
        // * CustomersDataTable
        // ***************************************************************
        
        public partial class CustomersDataTable : System.Data.DataTable
        {
            public CustomersRow AddCustomersRow
            (
                        uint customerID,
                        string customerCode,
                        string customerName,
                        bool isActive            )
            {
                CustomersRow rowCustomers = NewCustomersRow();
                rowCustomers.ItemArray = new object[]
                      {
                        customerID,
                        customerCode,
                        customerName,
                        isActive
                      };
                AddCustomersRow ( rowCustomers );
                return rowCustomers;
            }
         ....etc

     public class CustomersTableAdapter : SampledbDataSetTableAdapter
    {
        public CustomersTableAdapter ( string cs ) : base ( cs )
        {
            InitCommands ();
            InitAdapter ();
        }
        
        public virtual int Insert
        (
                        uint customerID,
                        string customerCode,
                        string customerName,
                        bool isActive        )
        {
            Adapter.InsertCommand.Parameters[0].Value = 0;
            Adapter.InsertCommand.Parameters[1].Value = (string) customerCode;
            Adapter.InsertCommand.Parameters[2].Value = (string) customerName;
            Adapter.InsertCommand.Parameters[3].Value = (bool) isActive;
            ConnectionState previousConnectionState = 
                                      Adapter.InsertCommand.Connection.State;
            if (((Adapter.InsertCommand.Connection.State & 
                   ConnectionState.Open) != System.Data.ConnectionState.Open))
                Adapter.InsertCommand.Connection.Open();
            try
            {
                int returnValue = Adapter.InsertCommand.ExecuteNonQuery();
                MySqlCommand cmd = new MySqlCommand("SELECT last_insert_id()",
                                                      Connection );
                returnValue = (int) ((long) cmd.ExecuteScalar ());
                return returnValue;
            }
            finally
            {
                if ((previousConnectionState == ConnectionState.Closed))
                    Adapter.InsertCommand.Connection.Close();
            }
        }
         ....etc

Sample Program

The sample program uses the database depicted in the snapshot above. To use it, it will be necessary to create the sample database from the SQL text file supplied.

Extending the Generated Code

You can add your own code to the generated code without changing MySqlUtil by adding code to one of your project files like this

namespace SampleDataSetData.SampleDataSetTableAdapters
{
    public partial class CustomerTableAdapter
    {
        public int ExecuteSP ( string ccode, string cname, bool active )
        {
            ...... etc
        }
    }
}

History

• July, 2006 - Initial release
• 31 July 2006 - updated downloads