从数据库获取最后插入的 ID 时出现问题 - asp.net
我正在尝试将数据添加到用户表(注册后)。将用户数据添加到用户表后,我不想在“个人资料”表 (1:1) 中创建包含 userID 的行,这就是我在这里使用事务的原因。但问题是,当代码到达“ExecuteScalar()”行时,插入后我无法获取用户的 UserId (@@Identity),我收到的异常是 “对象引用未设置为对象的实例。 ”
string sqlAddUser = string.Format("insert into tbl_users ([UserName],[Email],[Password],[PasswordSalt],[CreatedDate],[IsActivated],[IsLockedOut],[LastLoginDate],[LastLockedOutDate], [NewEmailKey]) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}', '{9}')",
user.UserName, user.Email, user.Password, user.PasswordSalt, user.CreatedDate, user.IsActivated, user.IsLockedOut, user.LastLockedOutDate, user.LastLoginDate, user.NewEmailKey);
SqlCommand cmdAddUser = new SqlCommand(sqlAddUser, conn, transaction);
int result = cmdAddUser.ExecuteNonQuery();
SqlCommand cmdGetLastIdentity = new SqlCommand("SELECT @@IDENTITY", conn, transaction);
int i = (int)cmdGetLastIdentity.ExecuteScalar();
string sqlAddUserProfile = string.Format("insert into tbl_profile (UserId) values ({0})", i);
SqlCommand cmdAddUserProfile = new SqlCommand(sqlAddUserProfile, conn, transaction);
cmdAddUserProfile.ExecuteNonQuery();
transaction.Commit();
return GetUser(username);
}
catch (Exception ex)
{
transaction.Rollback();
}
I am trying to add data to the User table (after registration). Immediately after user data is added to the user table I wan't to create row with userID in 'profile' table (1:1) and that's why I use transactions here. But the problem is I can't get UserId of the user after insert (@@Identity) when code comes to 'ExecuteScalar()' line exception that I receive is "Object reference not set to an instance of an object."
string sqlAddUser = string.Format("insert into tbl_users ([UserName],[Email],[Password],[PasswordSalt],[CreatedDate],[IsActivated],[IsLockedOut],[LastLoginDate],[LastLockedOutDate], [NewEmailKey]) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}', '{9}')",
user.UserName, user.Email, user.Password, user.PasswordSalt, user.CreatedDate, user.IsActivated, user.IsLockedOut, user.LastLockedOutDate, user.LastLoginDate, user.NewEmailKey);
SqlCommand cmdAddUser = new SqlCommand(sqlAddUser, conn, transaction);
int result = cmdAddUser.ExecuteNonQuery();
SqlCommand cmdGetLastIdentity = new SqlCommand("SELECT @@IDENTITY", conn, transaction);
int i = (int)cmdGetLastIdentity.ExecuteScalar();
string sqlAddUserProfile = string.Format("insert into tbl_profile (UserId) values ({0})", i);
SqlCommand cmdAddUserProfile = new SqlCommand(sqlAddUserProfile, conn, transaction);
cmdAddUserProfile.ExecuteNonQuery();
transaction.Commit();
return GetUser(username);
}
catch (Exception ex)
{
transaction.Rollback();
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您可以通过使用 OUTPUT 子句和 INSERT 在单个语句中执行此操作、UPDATE、DELETE 或 MERGE 语句。
您可以输出任何数据库生成的值,并使用 SqlCommand.ExecuteReader() 读取这些值,这将执行插入并将插入的值作为表输出。
注意:不要使用字符串格式化,使用sql参数,否则容易受到sql注入
You can do this in a single statement by using the OUTPUT clause with INSERT, UPDATE, DELETE or MERGE statements.
You can output any database-generated values, and read the values using SqlCommand.ExecuteReader() which will execute the insert and output inserted values as a table.
Note: Don't use string formating, use sql parameters, otherwise you are vulnerable to sql injection
其中一条评论很准确——像这样构造你的插入,并且你要求 SQL 注入。您可能应该这样做:
您可能已经在这样做,但我会将您的操作包装在
using
语句中,以便您正确处置数据库对象。One of the comments was spot on -- construct your inserts like this and you're asking for SQL injection. Here's what you should probably do:
You might already be doing this, but I'd wrap your operations in a
using
statement so you properly dispose of your database objects.您必须在服务器上创建一个存储过程,该存储过程将插入新用户并返回其 id。
You have to create a stored proc on the server that will insert the new user and return it's id.