0

I am using ADO.NET and Parametrized Queries (unnamed parameters and ExecuteNonQuery method) to write to both SQL Server and MySQL dBases (the application has to work agnostically with both). With both, the primary keys of all tables are auto-incrementing.

With SQL Server I am able to set "CommandText" to the following in which case the parametrized query and the SELECT execute in an atomic fashion:

INSERT INTO myTable (param1, param2, param3) VALUES (?, ?, ?);SELECT MAX(ID) FROM myTable;

This ensures that the ID (primary key) that is returned is the one corresponding to the parametrized query.

Unfortunately, with MySQL the ";SELECT..." generates a syntax error. How can I execute a parametrized query atomically with a SELECT query using ADO.NET and MySQL?

Thanks in advance for your help!

Andrea
  • 11,801
  • 17
  • 65
  • 72
Akash Sharma
  • 89
  • 3
  • 10
  • You want to use `last_insert_id()` and an interface function that allows you to run two queries at the same time. – Gordon Linoff Mar 18 '18 at 13:38
  • 1
    Note that the `SELECTMAX(ID)` method may not return the just-inserted value if multiple users run the statements at the same time unless you execute the queries in a transaction with a serializable isolation level. MySQL and SQL Server each have proprietary ways to return the assigned value (`SCOPE_IDENTITY()` and `last_insert_id()`) but that will require different queries, which you could abstract as @GordonLinoff suggested. – Dan Guzman Mar 18 '18 at 13:54
  • Thanks a lot for everyone's help! As per everyone's suggestion I am no longer using Select Max(ID).Instead with SQL Server I am adding an OUTPUT clause in the INSERT statement (suggested here: https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value). With MySQL I am using select last_insert_id() as suggested. All good! – Akash Sharma Mar 21 '18 at 20:21

1 Answers1

0

Thanks a lot for everyone's help! As per everyone's suggestion I am no longer using Select Max(ID).

Instead with SQL Server I am adding an OUTPUT clause in the INSERT statement as suggested here: How do I use an INSERT statement's OUTPUT clause to get the identity value?

With MySQL I am using select last_insert_id() as suggested. It works!

Akash Sharma
  • 89
  • 3
  • 10