Chapter 10 Connector/NET FAQ

Questions

• 10.1: Are all commands executed after a transaction begins automatically enlisted in the transaction?

• 10.2: How do I obtain the value of an auto-incremented column?

Questions and Answers

10.1: Are all commands executed after a transaction begins automatically enlisted in the transaction?

Yes. When a client begins a transaction in classic MySQL, all subsequent commands (on that connection) are part of that transaction until the client commits or rolls back the transaction. To execute a command outside of that transaction, you must open a separate connection.

10.2: How do I obtain the value of an auto-incremented column?

When using CommandBuilder, setting ReturnGeneratedIdentifiers property to true no longer works, as CommandBuilder does not add last_insert_id() by default.

CommandBuilder hooks up to the DataAdapter.RowUpdating event handler, which means it is called for every row. It examines the command object and, if it is the same referenced object, it essentially rebuilds the object, thereby destroying your command text changes.

One approach to solving this problem is to clone the command object so you have a different actual reference:

dataAdapter.InsertCommand = cb.GetInsertCommand().Clone()

This works, but since the CommandBuilder is still connected to the DataAdapter, the RowUpdating event still fires, adversely affecting performance. To stop that, once all your commands have been added you need to disconnect the CommandBuilder from the DataAdapter:

cb.DataAdapter = null;

The last requirement is to make sure the id that is returned by last_insert_id() has the correct name. For example:

SELECT last_insert_id() AS id

A complete working example is shown here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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

namespace GetAutoIncId
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "server=localhost;user=root;database=TestDB;port=3306;password=******;";
            MySqlConnection conn = new MySqlConnection(connStr);

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

                string sql = "SELECT * FROM TestTable";

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

                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                // use Cloned object to avoid .NET rebuilding the object, and
                // thereby throwing away our command text additions.
                MySqlCommand insertCmd = cb.GetInsertCommand().Clone();
                insertCmd.CommandText = insertCmd.CommandText + ";SELECT last_insert_id() AS id";
                insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                da.InsertCommand = insertCmd;
                cb.DataAdapter = null; // Unhook RowUpdating event handler

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

                DataRow row = dt.NewRow();
                row["name"] = "Joe Smith";

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

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

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