将ScopeIdentity用于存储过程中的标识列，asp.net

I've created a stored procedure and I'm inserting values into 3 tables, Users, Workers and Vets. In the Users table, I have an identity column UserID, which is a foreign key to the Workers and Vets table.

我已经创建了一个存储过程,我将值插入3个表,Users,Workers和Vets。在Users表中,我有一个标识列UserID,它是Workers和Vets表的外键。

When inserting into the Workers and Vets tables, I can't seem to retrieve the value of the UserID that was inserted into the Users table.

当插入Workers和Vets表时,我似乎无法检索插入到Users表中的UserID的值。

This is the stored procedure for the vets:

这是兽医的存储过程:

CREATE PROCEDURE [dbo].[InsertIntoVets]
    @PrimeiroNome Varchar(20),
    @NomeDoMeio Varchar(50),
    @Sobrenome Varchar(30),
    @DataDeNascimento Date,
    @EndereçoPostal1 Varchar(120),
    @EndereçoPostal2 Varchar(120),
    @Cidade Varchar(100),
    @Especialidade_id Int,
    @Clinica_id Int,
    @UserID Int OUTPUT 
AS 
BEGIN 
    SET NOCOUNT ON;

    INSERT INTO Veterinarios([Primeiro nome], [Nome do meio], Sobrenome, [Data de Nascimento], [Endereço Postal1], [Endereço Postal2], Cidade, Especialidade_id, Clinica_id, UserID)
    VALUES (@PrimeiroNome, @NomeDoMeio, @Sobrenome, @DataDeNascimento, @EndereçoPostal1, @EndereçoPostal2, @Cidade, @Especialidade_id, @Clinica_id, @UserID)

    SET @UserID = SCOPE_IDENTITY();
END

C# code:

// Vets
SqlCommand sqlCommandVets = new SqlCommand();
sqlCommandVets.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommandVets.CommandText = "InsertIntoVets";

sqlCommandVets.Parameters.Add("@PrimeiroNome", SqlDbType.VarChar).Value = (primeiroNome.Text.Trim());
sqlCommandVets.Parameters.Add("@NomeDoMeio", SqlDbType.VarChar).Value = (nomeDoMeio.Text.Trim());
sqlCommandVets.Parameters.Add("@Sobrenome", SqlDbType.VarChar).Value = (sobrenome.Text.Trim());
sqlCommandVets.Parameters.Add("@DataDeNascimento", SqlDbType.Date).Value = (dataDeNascimento.Text.Trim());
sqlCommandVets.Parameters.Add("@EndereçoPostal1", SqlDbType.VarChar).Value = (enderecoPostal1.Text.Trim());
sqlCommandVets.Parameters.Add("@EndereçoPostal2", SqlDbType.VarChar).Value = (enderecoPostal2.Text.Trim());
sqlCommandVets.Parameters.Add("@Cidade", SqlDbType.VarChar).Value = (cidade.Text.Trim());
sqlCommandVets.Parameters.Add("@Especialidade_id", SqlDbType.Int).Value = (dropDownEspecialidade.SelectedValue);
sqlCommandVets.Parameters.Add("@Clinica_id", SqlDbType.VarChar).Value = (dropDownClinica.SelectedValue);
sqlCommandVets.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;

sqlCommandVets.Connection = con;

Here you have a screenshot of both the Users and Vets table:

在这里,您可以看到Users和Vets表的屏幕截图:

UsersTable AND VetsTable

UsersTable和VetsTable

I hope I was clear and provided enough info.

我希望我很清楚并提供足够的信息。

Thank you in advance, Hugo Silva.

谢谢你,雨果席尔瓦。

3 个解决方案

#1

You don't need an OUTPUT parameter for this. Just change your stored procedure that inserts the user data into

您不需要OUTPUT参数。只需更改插入用户数据的存储过程即可

CREATE PROCEDURE [dbo].[InsertIntoUsers]
    .....
    SELECT SCOPE_IDENTITY();
END

Then you could execute the stored procedure with ExecuteScalar and get the result from the stored procedure that consist of a single row with a single column

然后,您可以使用ExecuteScalar执行存储过程,并从包含单个列的单个行的存储过程中获取结果

int id = Convert.ToInt32(sqlCommandUsers.ExecuteScalar());

At this point you have the value for the UserID column that you want to set in the Vets and Workers table. Just pass it as a normal parameter to your stored procedure.

此时,您具有要在Vets和Workers表中设置的UserID列的值。只需将其作为普通参数传递给存储过程即可。

Also in this context where you have multiple updates to your database you should be sure to insert everything inside a Transaction. If something goes wrong in the insertions of Vets you want to Rollback everything you have done in the Users and Workers table or Commit if all goes well

此外,在您对数据库进行多次更新的上下文中,您应确保在Transaction中插入所有内容。如果在Vets的插入中出现问题,您想要回滚在“用户和工作人员”表中完成的所有操作,或者如果一切顺利,则返回“提交”

#2

When you execute the sqlcommand, use ExecuteScalar instead of ExecuteNonQuery and formulate your stored procedure like it is here

当您执行sqlcommand时,使用ExecuteScalar而不是ExecuteNonQuery并制定您的存储过程,就像它在这里一样

#3

Even though many others will recommend returning via a SELECT statement, I would recommend sticking with the ExecuteNonQuery(). It is more efficient for both the SQL Server as well as your calling application because no database object will need to be created and no object will need to be utilized (directly or transparently) to read it.

尽管许多其他人会建议通过SELECT语句返回,但我建议坚持使用ExecuteNonQuery()。它对SQL Server和调用应用程序都更有效,因为不需要创建数据库对象,也不需要使用任何对象(直接或透明地)来读取它。

I generally explicitly declare my output parameters and then attach them. After the ExecuteNonQuery() you can access the parameters via the .Value property.

我通常显式声明我的输出参数,然后附加它们。在ExecuteNonQuery()之后,您可以通过.Value属性访问参数。

Note 1: .Value is an object, so be aware of the NULL possibility

注1:。Value是一个对象,所以要注意NULL的可能性

SqlParamater opUserID = new SqlParameter("@UserID",sqlDbType.Int);
opUserID.Direction = ParameterDirection.Output;
sqlCommandVets.Parameters.Add (opUserID);

sqlCommandVets.ExecuteNonQuery();
int NewUserID = (int)opUserID.Value;

#1