Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteTo get a MySQL AUTO_INCREMENT ID in C#, run the INSERT and read LAST_INSERT_ID() immediately on the same open connection. With Connector/NET, you can issue both statements together and read the result with ExecuteScalarAsync(), or run them as separate commands if multiple statements are not allowed.
Retrieve the ID after an INSERT
MySQL’s LAST_INSERT_ID() returns the automatically generated value from the most recent successful INSERT in the current connection’s session. The MySQL 8.4 Reference Manual documents its behavior, including the result for multi-row inserts: LAST_INSERT_ID().
With Connector/NET, the FAQ demonstrates appending SELECT last_insert_id() AS id to the insert command and reading the result: Connector/NET FAQ. For example:
using var connection = new MySqlConnection(connectionString);
await connection.OpenAsync();
using var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO parent (name) VALUES (@name);
SELECT LAST_INSERT_ID();";
command.Parameters.AddWithValue("@name", name);
var id = Convert.ToInt64(await command.ExecuteScalarAsync());ExecuteScalarAsync() returns the first column of the first row in the result set. Here that is the value selected by LAST_INSERT_ID(). Convert it to the integer type appropriate for your schema; long (Int64) is used above.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →#1 Best Overall
When multiple statements are not allowed
Some Connector/NET configurations or other .NET providers may not allow the insert and select in one command. Run them separately using the same open connection:
using var connection = new MySqlConnection(connectionString);
await connection.OpenAsync();
using var insert = connection.CreateCommand();
insert.CommandText = "INSERT INTO parent (name) VALUES (@name)";
insert.Parameters.AddWithValue("@name", name);
await insert.ExecuteNonQueryAsync();
using var getId = connection.CreateCommand();
getId.CommandText = "SELECT LAST_INSERT_ID()";
var id = Convert.ToInt64(await getId.ExecuteScalarAsync());If later database work must succeed or fail together with the insert, put the operations in a transaction and commit only after that work completes. A historical SitePoint ODBC discussion describes a provider that rejected semicolon-separated statements and worked with separate statements in an ODBC transaction; that is an example of provider-specific behavior, not a rule for every .NET driver.
Keep the same connection and read the value promptly
The generated ID is session-specific. Do not retrieve it by opening a second connection: that connection has a different session and does not hold the first connection’s insert result. Read the ID right after the insert, before running unrelated statements. MySQL’s C API documentation likewise says the generated value is tied to statements on the current client connection and advises calling mysql_insert_id() immediately when saving it: mysql_insert_id().
What happens with failed, empty, or multi-row inserts?
- Failed insert: Handle the database exception; a failed insert did not generate a new key to retrieve.
- No inserted rows: Do not treat the returned session value as a newly created ID. MySQL documents that
LAST_INSERT_ID()remains unchanged when no rows are successfully inserted. - Multi-row insert:
LAST_INSERT_ID()returns the first automatically generated ID, not a list of every ID generated by the statement. If the application needs each inserted row’s ID, this scalar result alone is insufficient.
Check the insert’s outcome and affected-row count as appropriate for your statement, and only use an ID when the operation actually created the row you intend to reference.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Pass the parent ID to a second INSERT
Once the first insert’s ID has been read, bind it as a parameter in the child insert. Use one connection throughout, and use a transaction when the parent and child records must be atomic:
using var transaction = await connection.BeginTransactionAsync();
try
{
using var parent = connection.CreateCommand();
parent.Transaction = transaction;
parent.CommandText = "INSERT INTO parent (name) VALUES (@name)";
parent.Parameters.AddWithValue("@name", name);
await parent.ExecuteNonQueryAsync();
using var getId = connection.CreateCommand();
getId.Transaction = transaction;
getId.CommandText = "SELECT LAST_INSERT_ID()";
var parentId = Convert.ToInt64(await getId.ExecuteScalarAsync());
using var child = connection.CreateCommand();
child.Transaction = transaction;
child.CommandText = "INSERT INTO child (parent_id, value) VALUES (@parentId, @value)";
child.Parameters.AddWithValue("@parentId", parentId);
child.Parameters.AddWithValue("@value", value);
await child.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}Parameter binding keeps the ID as data rather than concatenating it into SQL. The transaction makes the pair of inserts an all-or-nothing operation for transactional tables and a compatible provider.
Quick Recap
Rank #4
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
