Coming from a pure MS backgound, I never thought that @@identity is not backward compatible when executing a batch of sql statements. So for those newbies using MySql database, just like me, here is some tip on executing batch statements using MySql Connector using ADO.Net:
Wrong:
Wrong, won't execute:
1
2 string sql = "insert into Table1 (Field1, Field2)";
3 sql += " values ('123', 'ABC'); select @@identity;";
4
5 // 1. Create command...
6 // 2. then ExecuteScalar
7
8
The correct way:
1
2 string sql = "insert into Table1 (Field1, Field2)";
3 sql += " values ('123', 'ABC'); select LAST_INSERT_ID();";
4
5 // 1. Create command...
6 // 2. then ExecuteScalar
7