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