Dapper MySQL return value

Question

i got a problem using dapper and MySql in a ASP.net Identity project. I want to insert a user to a table users and want the autogenerated id from this insertation back. But i get a syntax error and i don´t know why.

this is my code for the method insert:

public void Insert(TUser member)
        {
            var id = db.Connection.ExecuteScalar<int>(@"Insert into users
                                    (UserName,  PasswordHash, SecurityStamp,Email,EmailConfirmed,PhoneNumber,PhoneNumberConfirmed, AccessFailedCount,LockoutEnabled,LockoutEndDateUtc,TwoFactorEnabled)
                            values  (@name, @pwdHash, @SecStamp,@email,@emailconfirmed,@phonenumber,@phonenumberconfirmed,@accesscount,@lockoutenabled,@lockoutenddate,@twofactorenabled)
                            SELECT Cast(LAST_INSERT_ID() as AS UNSIGNED INTEGER)",
                              new
                              {
                                  name = member.UserName,
                                  pwdHash = member.PasswordHash,
                                  SecStamp = member.SecurityStamp,
                                  email = member.Email,
                                  emailconfirmed = member.EmailConfirmed,
                                  phonenumber = member.PhoneNumber,
                                  phonenumberconfirmed = member.PhoneNumberConfirmed,
                                  accesscount = member.AccessFailedCount,
                                  lockoutenabled = member.LockoutEnabled,
                                  lockoutenddate = member.LockoutEndDateUtc,
                                  twofactorenabled = member.TwoFactorEnabled
                              });
            // we need to set the id to the returned identity generated from the db
            member.Id = id;
        }

and this is my table users:

CREATE TABLE `users` (
  `Id` int(36) NOT NULL,
  `Email` varchar(256) DEFAULT NULL,
  `EmailConfirmed` tinyint(1) NOT NULL,
  `PasswordHash` longtext,
  `SecurityStamp` longtext,
  `PhoneNumber` longtext,
  `PhoneNumberConfirmed` tinyint(1) NOT NULL,
  `TwoFactorEnabled` tinyint(1) NOT NULL,
  `LockoutEndDateUtc` datetime DEFAULT NULL,
  `LockoutEnabled` tinyint(1) NOT NULL,
  `AccessFailedCount` int(11) NOT NULL,
  `UserName` varchar(256) NOT NULL,
  `FirstName` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `users`
  MODIFY `Id` int(36) NOT NULL AUTO_INCREMENT;

Error msg: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Cast(LAST_INSERT_ID() as AS UNSIGNED INTEGER)' at line 4

I thought LAST_INSERT_ID is giving me the last auto incerement userid?

Thanks for help

Answer 1

LAST_INSERT_ID will behave as you've described, but you have an unrelated error in the previous insert statement due to not including a semicolon at the end of it. When diagnosing syntax errors in MySQL, the SQL string contained in the error message is commonly immediately after the syntax error.

Consider reorganizing your statements for readability, so that you can identify syntax errors like this more easily. For example:

public void Insert(TUser member)
{
    string sql = @"
        Insert into users
          (UserName, PasswordHash, SecurityStamp, Email, EmailConfirmed, PhoneNumber, PhoneNumberConfirmed, AccessFailedCount, LockoutEnabled, LockoutEndDateUtc, TwoFactorEnabled)
        values 
          (@name, @pwdHash, @SecStamp, @email, @emailconfirmed, @phonenumber, @phonenumberconfirmed, @accesscount, @lockoutenabled, @lockoutenddate, @twofactorenabled);

        select LAST_INSERT_ID();
    ";

    member.Id = db.Connection.ExecuteScalar<int>(sql, new
    {
      name = member.UserName,
      pwdHash = member.PasswordHash,
      SecStamp = member.SecurityStamp,
      email = member.Email,
      emailconfirmed = member.EmailConfirmed,
      phonenumber = member.PhoneNumber,
      phonenumberconfirmed = member.PhoneNumberConfirmed,
      accesscount = member.AccessFailedCount,
      lockoutenabled = member.LockoutEnabled,
      lockoutenddate = member.LockoutEndDateUtc,
      twofactorenabled = member.TwoFactorEnabled
    });
}

Per this answer, you may also be able to remove the cast on LAST_INSERT_ID if you're running a recent MySQL version.