Retrieve output parameter of SQL statement

ado.net, c#, output-parameter, sql, sql-server

Solution

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;

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

Problem

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?

Original source