Click here to Skip to main content
Click here to Skip to main content

MySqlUtil - TableAdapters for MySql

, 3 Aug 2006
Rate this:
Please Sign up or sign in to vote.
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

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

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

About the Author


Comments and Discussions

 
QuestionNeed same functionality, for Oracle? Pinmemberdosdemon21-Apr-07 21:13 
AnswerRe: Need same functionality, for Oracle? PinmemberIan Semmel30-Apr-07 13:51 
GeneralNeed some help using the code.. Pinmemberdyngoman13-Feb-07 3:38 
GeneralRe: Need some help using the code.. PinmemberIan Semmel13-Feb-07 8:10 
GeneralRe: Need some help using the code.. Pinmemberdyngoman13-Feb-07 22:41 
I looked at the samples and I created a simple data base with a simple table:
 
DROP TABLE IF EXISTS `sample`.`users`;
CREATE TABLE `sample`.`users` (
`idusers` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) NOT NULL,
`pass` varchar(45) NOT NULL,
PRIMARY KEY (`idusers`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 
Then i write this code :
ds = new SampleDataSet();
usersTable = new SampleDataSet.UsersDataTable();
usersTable = ds.users;
usersTA = new SampleDataSetTableAdapters.UsersTableAdapter(mySqlConnectionString);
usersTA.Fill(usersTable);
SampleDataSet.UsersRow newRow = usersTable.NewUsersRow();
newRow.idusers = 0;
newRow.name = "testUser";
newRow.pass = "testPass";
adaptor.Insert(newRow);

I am using the 1.0.8 version of the .Net Connector. But nomather what version i try it still generates the same mysql error : Parameter '?idusers' must be defined.
GeneralRe: Need some help using the code.. PinmemberIan Semmel14-Feb-07 10:36 
GeneralRe: Need some help using the code.. Pinmemberdyngoman18-Feb-07 20:15 
QuestionMySQL Connector - Can i use your version ? Pinmemberrhada551-Aug-06 22:44 
AnswerRe: MySQL Connector - Can i use your version ? PinmemberIan Semmel1-Aug-06 23:38 
QuestionREFERENCED_TABLE_SCHEMA ? Pinmemberpolatouche1-Aug-06 14:36 
AnswerRe: REFERENCED_TABLE_SCHEMA ? PinmemberIan Semmel1-Aug-06 23:06 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 3 Aug 2006
Article Copyright 2006 by Ian Semmel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid