检索SQL语句的输出参数 [英] Retrieve output parameter of SQL statement

问题描述

我正在使用一个具有两列的表， customer_id 和 customer_name .

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

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

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

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

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

只需使用

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();
    }
}

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

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

我认为正确的语法应该是

I think the correct syntax would be

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

我认为我需要使用

command.ExecuteReader();

但是后来我被困住了.我应该如何从查询中获取值?

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

推荐答案

首先，使用 ExecuteNonQuery 编写操作.执行命令后，由于已将参数设置为样本的输出参数，因此您可以从 Parameters 集合中读取参数:

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:

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

command.ExecuteNonQuery();

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

如果您想知道插入后生成的标识列是什么 Id ，则可以使用 SCOPE_IDENTITY() sql命令，并使用 ExecuteScalar()获取命令的结果，例如:

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:

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();
    }
}

这篇关于检索SQL语句的输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！