Questions
21.2.9.1: How do I obtain the value of an auto-incremented column?
Questions and Answers
21.2.9.1: 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 will get 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 will work, but since the CommandBuilder is still connected to the DataAdapter, the RowUpdating event will still fire and performance will be hit. 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."); } } }
User Comments
Add your own comment.