检索SQL语句的输出参数

I'm using a table with two columns, customer_id and customer_name.

我正在使用一个包含两列的表，customer_id和customer_name。

customer_name is a simple varchar, customer_id is an auto incrementing primary key.

customer_name是一个简单的varchar，customer_id是一个自动递增的主键。

I want to use my C# application to insert a customer_name, and retrieve the value of the customer_id.

我想使用我的C＃应用程序插入customer_name，并检索customer_id的值。

Just inserting is simply solved by using

只需插入就可以通过使用来解决

using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
{
    using (SqlCommand command = new SqlCommand("INSERT INTO custom_customer (customer_name) VALUES (@name);"))
    {
        command.Parameters.Add(new SqlParameter("name", customer));
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }
}

I found some documentation of the OUTPUT statement, which can be used to retrieve a value.

我找到了一些OUTPUT语句的文档，可用于检索值。

I think the correct syntax would be

我认为正确的语法是

INSERT INTO custom_customer (customer_name) 
OUTPUT Inserted.customer_id 
VALUES (@name);

And I figure I need to use

我认为我需要使用

command.ExecuteReader();

But then I'm stuck. How should I go about getting the value from the query?

但后来我被困住了。我该如何从查询中获取值？

4 个解决方案

#1

First, use the ExecuteNonQuery to write operations. After execute command, you can read the parameter from Parameters collection, since you have set the parameter as a output parameter, for sample:

首先，使用ExecuteNonQuery编写操作。执行命令后，您可以从Parameters集合中读取参数，因为您已将参数设置为输出参数，对于示例：

command.Parameters["name"].Direction = System.Data.ParameterDirection.Output;

command.ExecuteNonQuery();

object name = command.Parameters["name"].Value;

If you want to know what Id the identity column generated after the insert, you could use SCOPE_IDENTITY() sql command, and use the ExecuteScalar() to get the result of command, for sample:

如果您想知道插入后生成的标识列的Id，您可以使用SCOPE_IDENTITY（）sql命令，并使用ExecuteScalar（）获取命令的结果，对于示例：

int id;
using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
{
    string sql = @"INSERT INTO custom_customer (customer_name) 
                                        VALUES (@name); 
                   SELECT SCOPE_IDENTITY();"

    using (SqlCommand command = new SqlCommand(sql))
    {
        command.Parameters.Add(new SqlParameter("name", customer));
        connection.Open();
        id = (int) command.ExecuteScalar();
        connection.Close();
    }
}

#2

You can use ExecuteScalar instead

您可以改用ExecuteScalar

int id = (int) command.ExecuteScalar();

#3

You need to call ExecuteScalar() to get the ID value:

您需要调用ExecuteScalar（）来获取ID值：

int ID= (int) Command.ExecuteScalar();

#4

using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
using (SqlCommand command = new SqlCommand("INSERT INTO custom_customer (customer_name) VALUES (@name);SELECT SCOPE_IDENTITY();"))
{
    command.Parameters.Add(new SqlParameter("name", customer));
    connection.Open();
    int id= (int)Command.ExecuteScalar();
}

#1