Click here to Skip to main content
15,867,568 members
Articles / Database Development / MySQL
Article

MySqlUtil - TableAdapters for MySql

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

Sample Image

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

C#
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

C#
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

License

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 21:13
dosdemon21-Apr-07 21:13 
AnswerRe: Need same functionality, for Oracle? Pin
Ian Semmel30-Apr-07 13:51
Ian Semmel30-Apr-07 13:51 
GeneralNeed some help using the code.. Pin
dyngoman13-Feb-07 3:38
dyngoman13-Feb-07 3:38 
GeneralRe: Need some help using the code.. Pin
Ian Semmel13-Feb-07 8:10
Ian Semmel13-Feb-07 8:10 
GeneralRe: Need some help using the code.. Pin
dyngoman13-Feb-07 22:41
dyngoman13-Feb-07 22:41 
GeneralRe: Need some help using the code.. Pin
Ian Semmel14-Feb-07 10:36
Ian Semmel14-Feb-07 10:36 
GeneralRe: Need some help using the code.. Pin
dyngoman18-Feb-07 20:15
dyngoman18-Feb-07 20:15 
QuestionMySQL Connector - Can i use your version ? Pin
rhada551-Aug-06 22:44
rhada551-Aug-06 22:44 
AnswerRe: MySQL Connector - Can i use your version ? Pin
Ian Semmel1-Aug-06 23:38
Ian Semmel1-Aug-06 23:38 
QuestionREFERENCED_TABLE_SCHEMA ? Pin
polatouche1-Aug-06 14:36
polatouche1-Aug-06 14:36 
AnswerRe: REFERENCED_TABLE_SCHEMA ? Pin
Ian Semmel1-Aug-06 23:06
Ian Semmel1-Aug-06 23:06 
I am using version 5.0.22-community-max-nt.

REFERENCED_TABLE_SCHEMA (and REFERENCED_TABLE_NAME) are in the KEY_COLUMN_USAGE table of information_schema on my system.

The other points are probably just coding errors on my part, I will check them out.

There is actually an update in the system somewhere as I had a big hole in the update command parameter code.


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.