Problem with getting last inserted ID from database - asp.net
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})",开发者_如何学Python i);
SqlCommand cmdAddUserProfile = new SqlCommand(sqlAddUserProfile, conn, transaction);
cmdAddUserProfile.ExecuteNonQuery();
transaction.Commit();
return GetUser(username);
}
catch (Exception ex)
{
transaction.Rollback();
}
You can do this in a single statement by using the OUTPUT clause with INSERT, UPDATE, DELETE or MERGE statements.
cmd.CommandText = "INSERT INTO tbl_user (...) OUTPUT inserted.Id VALUES (...)";
var id = Convert.ToInt32(cmd.ExecuteScalar());
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
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:
var cmdAddUser = new SqlCommand(@"insert into tbl_users (
[UserName],
[Email],
[Password],
[PasswordSalt],
[CreatedDate],
[IsActivated],
[IsLockedOut],
[LastLoginDate],
[LastLockedOutDate],
[NewEmailKey])
values (@p1, @p2, @p3, @p4, @p4, @p6, @p7, @p8, @p9, @p10);
select SCOPE_IDENTITY();", conn, transaction);
cmdAddUser.Parameters.AddWithValue("@p1", user.UserName);
cmdAddUser.Parameters.AddWithValue("@p2", user.Email);
cmdAddUser.Parameters.AddWithValue("@p3", user.Password);
cmdAddUser.Parameters.AddWithValue("@p4", user.PasswordSalt);
cmdAddUser.Parameters.AddWithValue("@p5", user.CreateDate);
cmdAddUser.Parameters.AddWithValue("@p6", user.IsActivated);
cmdAddUser.Parameters.AddWithValue("@p7", user.IsLockedOut);
cmdAddUser.Parameters.AddWithValue("@p8", user.LastLoginDate);
cmdAddUser.Parameters.AddWithValue("@p9", user.LastLockedOutDate);
cmdAddUser.Parameters.AddWithValue("@p10", user.NewEmailKey);
var newId = (int)cmdAddUser.ExecuteNonQuery();
// Then proceed with your profile operations using the result
You might already be doing this, but I'd wrap your operations in a using
statement so you properly dispose of your database objects.
You have to create a stored proc on the server that will insert the new user and return it's id.
精彩评论