Retrieve output parameter of SQL statement

Question

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

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

I want to use my C# application to insert a customer_name, and retrieve the value of the 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.

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?

Answer 1

You can use ExecuteScalar instead

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