使用scope_Identity()检索最后插入的ID [英] Retrieving the last inserted ID using scope_Identity()
问题描述
我正在尝试使用SCOPE_IDENTITY插入最后一个ID，但出现错误.我正在将SQL Server 2012与Visual Studio Express 2013一起使用
I am trying to get the last ID inserted using the SCOPE_IDENTITY but I got an error. I am using SQL server 2012 with Visual Studio Express 2013
这是我的代码
protected void PerformInscription(string sEmail, string sPassword,string sName)
{
bool buserIdAuthenticated = false;
string salt = null;
string passwordHash = pwdManager.GeneratePasswordHash(txtPassword.Text, out salt);
SqlConnection sqlConnection;
sqlConnection = new SqlConnection();
try
{
sqlConnection.ConnectionString = sqlDataSource1.ConnectionString;
string insertStatement = "INSERT INTO [User] "
+ "(email, hash, salt, name) "
+ "VALUES (@email, @hash, @salt, @name);"
+ "SELECT SCOPE_IDENTITY() AS id_user;";
SqlCommand insertCommand = new SqlCommand(insertStatement, sqlConnection);
insertCommand.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = sEmail;
insertCommand.Parameters.Add("@hash", SqlDbType.VarChar, 50).Value = passwordHash;
insertCommand.Parameters.Add("@salt", SqlDbType.VarChar, 50).Value = salt;
insertCommand.Parameters.Add("@nom", SqlDbType.VarChar, 50).Value = sName;
sqlConnection.Open();
int count = insertCommand.ExecuteNonQuery();
int User_ID = Convert.ToInt32(insertCommand.Parameters["@id_user"].Value);
Session["Id_user"] = User_ID;
insertCommand.Dispose();
if (count >= 1)
{
buserIdAuthenticated = true;
Session["userIdAuthenticated"] = buserIdAuthenticated;
Response.Redirect("../pages/Welcome.aspx");
}
}
catch (SqlException ex)
{
lblMessage.Text = ex.Message;
}
finally
{
sqlConnection.Close();
}
}
}
我想捕获定义为自动增量身份的最后一个user_id.
I want to catch the last user_id which was define as Identity for the auto-increment.
这是我遇到的错误
类型为"System.IndexOutOfRangeException"的异常发生在System.Data.dll，但未在用户代码中处理.
An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code.
其他信息:参数名称为'@id_user'的SqlParameter不包含在此SqlParameterCollection中.
Additional information: An SqlParameter with ParameterName '@id_user' is not contained by this SqlParameterCollection.
我决定将两个请求分开.1次插入和1次选择
I decided to splitted the two request. 1 insert and 1 select
SqlConnection sqlConnection;
sqlConnection = new SqlConnection();
try
{
sqlConnection.ConnectionString = sqlDataSource1.ConnectionString;
string insertStatement = "INSERT INTO [User] "
+ "(email, hash, salt, name) "
+ "VALUES (@email, @hash, @salt, @name)";
SqlCommand insertCommand = new SqlCommand(insertStatement, sqlConnection);
insertCommand.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = sEmail;
insertCommand.Parameters.Add("@hash", SqlDbType.VarChar, 50).Value = passwordHash;
insertCommand.Parameters.Add("@salt", SqlDbType.VarChar, 50).Value = salt;
insertCommand.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = sName;
sqlConnection.Open();
int count = insertCommand.ExecuteNonQuery();
insertCommand.Dispose();
if (count >= 1)
{
string selectStatement = "SELECT SCOPE_IDENTITY() AS id_user";
SqlCommand selectCommand = new SqlCommand(selectStatement, sqlConnection);
selectCommand.Parameters.Add("@Id_user", SqlDbType.Int, 0, "Id_user");
int newID = (int)selectCommand.ExecuteScalar();
int User_ID = Convert.ToInt32(selectCommand.Parameters["@Id_user"].Value);
Session["Id_user"] = User_ID;
buserIdAuthenticated = true;
Session["userIdAuthenticated"] = buserIdAuthenticated;
Response.Redirect("../pages/Bienvenue.aspx");
}
}
catch (SqlException ex)
{
lblMessage.Text = ex.Message;
}
finally
{
sqlConnection.Close();
}
}
}
推荐答案
您不应将查询分为两个查询.您应该在插入查询中选择/设置结果.
You should not split the query in two queries. You should select/set the result within the insert query.
我为您提供了一个示例:
第一种方法是使用 select
private void Method1()
{
string sEmail = "test@test.com";
string passwordHash = "#$@#$@!#@$$@#!#@$!#@$!";
string salt = "????";
string sName = "John";
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
try
{
sqlConnection.Open();
string insertStatement = "INSERT INTO [User] "
+ "(email, hash, salt, name) "
+ "VALUES (@email, @hash, @salt, @name)"
+ "SELECT SCOPE_IDENTITY()";
using (SqlCommand insertCommand = new SqlCommand(insertStatement, sqlConnection))
{
insertCommand.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = sEmail;
insertCommand.Parameters.Add("@hash", SqlDbType.VarChar, 50).Value = passwordHash;
insertCommand.Parameters.Add("@salt", SqlDbType.VarChar, 50).Value = salt;
insertCommand.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = sName;
int userId = Convert.ToInt32(insertCommand.ExecuteScalar());
Trace.WriteLine("User created with id: " + userId);
}
}
catch (SqlException ex)
{
Trace.WriteLine(ex.Message);
//lblMessage.Text = ex.Message;
}
}
第二种方法是定义一个输出参数，这样您就可以返回多个值.
The second method is defining an output parameter, this way you can return multiple values.
private void Method2()
{
string sEmail = "test@test.com";
string passwordHash = "#$@#$@!#@$$@#!#@$!#@$!";
string salt = "????";
string sName = "John";
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
try
{
sqlConnection.Open();
string insertStatement = "INSERT INTO [User] "
+ "(email, hash, salt, name) "
+ "VALUES (@email, @hash, @salt, @name)"
+ "SET @user_id = SCOPE_IDENTITY()";
using (SqlCommand insertCommand = new SqlCommand(insertStatement, sqlConnection))
{
insertCommand.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = sEmail;
insertCommand.Parameters.Add("@hash", SqlDbType.VarChar, 50).Value = passwordHash;
insertCommand.Parameters.Add("@salt", SqlDbType.VarChar, 50).Value = salt;
insertCommand.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = sName;
insertCommand.Parameters.Add("@user_id", SqlDbType.Int).Direction = ParameterDirection.Output;
insertCommand.ExecuteNonQuery();
int userId = Convert.ToInt32(insertCommand.Parameters["@user_id"].Value);
Trace.WriteLine("User created with id: " + userId);
}
}
catch (SqlException ex)
{
Trace.WriteLine(ex.Message);
//lblMessage.Text = ex.Message;
}
}
您可以做的最好是，如果查询是静态的，请将这些查询放入存储过程中.这样可以加快查询速度.
The best you can do is, if the queries are static, putting these queries within stored procedures. This will speed-up the queries.
这篇关于使用scope_Identity()检索最后插入的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
