MySQL Bugs: #48601: MySqlCommandBuilder ReturnGeneratedIdentifiers Property does not exist

Bug #48601	MySqlCommandBuilder ReturnGeneratedIdentifiers Property does not exist
Submitted:	6 Nov 2009 17:15	Modified:	24 Feb 2010 13:31
Reporter:	Matthew Bilek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET Documentation	Severity:	S2 (Serious)
Version:	6.1	OS:	Any
Assigned to:	Tony Bedford	CPU Architecture:	Any
Tags:	MySQL Connector/NET ReturnGeneratedIdentifiers

Description:
When I attempted to compile my existing code with the MySQL Connector/NET 6.1.2 is receive the following message: 'MySql.Data.MySqlClient.MySqlCommandBuilder' does not contain a definition for 'ReturnGeneratedIdentifiers'.

I have found a link of http://lists.mysql.com/commits/81553 that indicates that the developer Reggie Burnett has committed code to the 6.1 branch that removes ReturnGeneratedIdentifiers code from CommandBuilder.cs program.  He mentions that 'We are now treating the command builder the same as SqlClient'.  But there is no indication of a workaround or another method in obtaining generated identifiers.

Also, MySQL documentation still shows that this property is available and effective in obtaining auto-incremented columns.  http://dev.mysql.com/doc/refman/5.1/en/connector-net-faq.html

How to repeat:
Write a C# program utilizing the command builder object and setting the ReturnGeneratedIdentifiers property to true.  The compile will fail.

Suggested fix:
Back out the code changes of the ReturnGeneratedIdentifiers property in the CommandBuilder.cs program or provide another way to obtain "GeneratedIdentifiers"/auto-incremented information.

Hi Matthew and thanks for your report.

Alas, this is intentional and won't be supported any more... We are doing it like SqlClient does it. Command builder no longer attaches a "select last_inserted_id()" for you but you need to do it manually.

Tony, please note this in manual, where appropriate, if needs be.

Tony,

You commented "Command builder no longer attaches a "select last_inserted_id()" for you but you need to do it manually."

Could you explain this in more detail.

Thanks,
Matthew

The code below will return hero_id as NULL. So ReturnGeneratedIdentifiers is gone. How do we now obtain the last inserted id when using CommandBuilder?

using System;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {

            string connStr = "server=localhost;user=root;database=MarvelComics;port=3306;password=******;";
            MySqlConnection conn = new MySqlConnection(connStr);

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                string sql = "SELECT * FROM Heroes";
                
                MySqlDataAdapter daHero = new MySqlDataAdapter(sql, conn);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(daHero);
                MySqlCommand cmd = new MySqlCommand();

                DataSet dsHero = new DataSet();
                DataView dvHero = new DataView();
                DataRow drRow;

                cmd.Connection = conn;
                cmd.CommandText = sql;

                daHero.SelectCommand = cmd;
                daHero.Fill(dsHero, "Heroes");

                cb.DataAdapter = daHero;
                cb.DataAdapter.SelectCommand.CommandText = "SELECT * FROM Heroes";
                cb.RefreshSchema();

                dvHero = dsHero.Tables["Heroes"].DefaultView;

                drRow = dvHero.Table.NewRow();
                drRow["first_name"] = "Tonci";
                drRow["last_name"] = "Grgin";

                dvHero.Table.Rows.Add(drRow);
                daHero.Update(dsHero, "Heroes");

                System.Console.WriteLine("ID after update: " + drRow["hero_id"]);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            conn.Close();
            Console.WriteLine("Done.");
        }
    }
}

Tony

You need to fetch the command from the commandbuilder.  Like this:

MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
MySqlCommand insertCmd = cb.GetInsertCommand();
insertCmd.CommandText += ";SELECT last_insert_id()";
insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.InsertCommand = insertCmd;

Changed the code as follows but still returning NULL for ID. Can anyone see where I'm going wrong please? Thanks!

using System;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ConsoleApplication4
{
   class Program
   {
       static void Main(string[] args)
       {

           string connStr = "server=localhost;user=root;database=MarvelComics;port=3306;password=******;";
           MySqlConnection conn = new MySqlConnection(connStr);

           try
           {
               Console.WriteLine("Connecting to MySQL...");
               conn.Open();

               string sql = "SELECT * FROM Heroes";

               MySqlDataAdapter daHero = new MySqlDataAdapter(sql, conn);
               MySqlCommandBuilder cb = new MySqlCommandBuilder(daHero);

               MySqlCommand cmd = new MySqlCommand();
               cmd.Connection = conn;
               cmd.CommandText = sql;

               MySqlCommand insertCmd = cb.GetInsertCommand();
               insertCmd.CommandText = insertCmd.CommandText + ";SELECT last_insert_id()";
               insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
               daHero.InsertCommand = insertCmd;

               DataSet dsHero = new DataSet();
               DataView dvHero = new DataView();
               DataRow drRow;

               daHero.SelectCommand = cmd;
               daHero.Fill(dsHero, "Heroes");

               cb.DataAdapter = daHero;
               daHero.SelectCommand.CommandText = "SELECT * FROM Heroes";
               cb.RefreshSchema();

               dvHero = dsHero.Tables["Heroes"].DefaultView;

               drRow = dvHero.Table.NewRow();
               drRow["first_name"] = "Tonci";
               drRow["last_name"] = "Grgin";

               dvHero.Table.Rows.Add(drRow);
               daHero.Update(dsHero, "Heroes");

               System.Console.WriteLine("ID after update: " + drRow["hero_id"]);
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.ToString());
           }

           conn.Close();
           Console.WriteLine("Done.");
       }
   }
}

Tony

This is really caused by a problem in the .net framework.  Essentially what happens is this.

CommandBuilder hooks up to the DataAdapter.RowUpdating event handler (which means it will get called for every row).  It examines the command object and, if it is the same referenced object, it essentially rebuilds the object (throwing away your command text mods). 

First, the thing to understand is that command builder is used by MSFT for generating SQL at design time not run time.  They heavily discourage it's use in serious apps.  

The simple approach to solve this problem is simple to clone the command object so you have a different actual reference.  Like this:
dataAdapter.InsertCommand = cb.GetInsertCommand().Clone()

This will work but since the command builder is still connected the RowUpdating event will still fire and performance will be hit.  To stop that, once all your commands are hooked up you need to do this:

cb.DataAdapter = null;

The last thing you need to do is make sure the id that is returned by last_insert_id has the right name.  There are two ways of connecting that value to the table but the easiest way is simple to give it the right name.  Here is your sample modified to work correctly:

string sql = "SELECT * FROM test";

MySqlDataAdapter daHero = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(daHero);

MySqlCommand insertCmd = cb.GetInsertCommand().Clone();
insertCmd.CommandText += ";SELECT last_insert_id() as hero_id";
insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
daHero.InsertCommand = insertCmd;

// doing this unhooks the RowUpdating event handler 
cb.DataAdapter = null;

DataTable dt = new DataTable();
daHero.Fill(dt);

DataRow row = dt.NewRow();
row["first_name"] = "Tonci";
row["last_name"] = "Grgin";

dt.Rows.Add(row);
daHero.Update(dt);

Thanks Reggie. That worked. I have simplified the example, and updated the FAQ page on this in the main C/NET docs XML sources. Online docs should update in the next 24 hours or so.