我在ASP.net Identity项目中使用Dapper和MySql时遇到了问题。我想将用户插入到一个名为“users”的表中,并希望从此插入返回自动生成的ID。但是我得到了语法错误,不知道为什么。
这是我的插入方法代码:
这是我的用户表:
错误信息:您的SQL语法有误,请检查与您的MySQL服务器版本相对应的手册,以获取正确的语法使用方式。位于第4行附近的SELECT Cast(LAST_INSERT_ID() as AS UNSIGNED INTEGER)。
我认为LAST_INSERT_ID应该给我最后一个自动增量用户ID?
谢谢帮助。
这是我的插入方法代码:
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;
}
这是我的用户表:
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;
错误信息:您的SQL语法有误,请检查与您的MySQL服务器版本相对应的手册,以获取正确的语法使用方式。位于第4行附近的SELECT Cast(LAST_INSERT_ID() as AS UNSIGNED INTEGER)。
我认为LAST_INSERT_ID应该给我最后一个自动增量用户ID?
谢谢帮助。