Click here to Skip to main content
15,499,011 members
Articles / Database Development / MySQL
Posted 26 Jul 2006


47 bookmarked

MySqlUtil - TableAdapters for MySql

Rate me:
Please Sign up or sign in to vote.
4.59/5 (7 votes)
3 Aug 20063 min read
A program which generates Typed DataSets and TableAdapters for MySQL databases

Sample Image


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.


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;

        // ***************************************************************
        // * 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[]
                AddCustomersRow ( rowCustomers );
                return rowCustomers;

     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 = 
            if (((Adapter.InsertCommand.Connection.State & 
                   ConnectionState.Open) != System.Data.ConnectionState.Open))
                int returnValue = Adapter.InsertCommand.ExecuteNonQuery();
                MySqlCommand cmd = new MySqlCommand("SELECT last_insert_id()",
                                                      Connection );
                returnValue = (int) ((long) cmd.ExecuteScalar ());
                return returnValue;
                if ((previousConnectionState == ConnectionState.Closed))

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


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


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Comments and Discussions

QuestionNeed same functionality, for Oracle? Pin
dosdemon21-Apr-07 22:13
Memberdosdemon21-Apr-07 22:13 
Nice little app!

I need to create the same thing which works with Oracle 10g instead of MySQL. Any clues on how I can achieve that? Thanks.

AnswerRe: Need same functionality, for Oracle? Pin
Ian Semmel30-Apr-07 14:51
MemberIan Semmel30-Apr-07 14:51 
GeneralNeed some help using the code.. Pin
dyngoman13-Feb-07 4:38
Memberdyngoman13-Feb-07 4:38 
GeneralRe: Need some help using the code.. Pin
Ian Semmel13-Feb-07 9:10
MemberIan Semmel13-Feb-07 9:10 
GeneralRe: Need some help using the code.. Pin
dyngoman13-Feb-07 23:41
Memberdyngoman13-Feb-07 23:41 
GeneralRe: Need some help using the code.. Pin
Ian Semmel14-Feb-07 11:36
MemberIan Semmel14-Feb-07 11:36 
GeneralRe: Need some help using the code.. Pin
dyngoman18-Feb-07 21:15
Memberdyngoman18-Feb-07 21:15 
QuestionMySQL Connector - Can i use your version ? Pin
rhada551-Aug-06 23:44
Memberrhada551-Aug-06 23:44 
AnswerRe: MySQL Connector - Can i use your version ? Pin
Ian Semmel2-Aug-06 0:38
MemberIan Semmel2-Aug-06 0:38 
polatouche1-Aug-06 15:36
Memberpolatouche1-Aug-06 15:36 
Ian Semmel2-Aug-06 0:06
MemberIan Semmel2-Aug-06 0:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.