current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

4 Answers 4

up vote 2 down vote accepted

First, use the ExecuteNonQuery to write operations. After execute command, you can read the parameter from Parameters collection, since you have setted 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 identity column the insert has generated, 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();
    }
}
share|improve this answer
    
I think OP wants to return inserted id and not the same value he is passing as paramter –  rs. Dec 4 '13 at 17:12
    
I did some edits. :) –  Felipe Oriani Dec 4 '13 at 17:18
    
The second answer here was very helpful, just one thing for future readers: replace SELECT SCOPE_IDENTITY() with SELECT CONVERT(int, SCOPE_IDENTITY()). Casting the return value of simply SCOPE_IDENTITY() into an int yields a format exception, since the standard return value is numeric(38,0), which is unhandled. –  Nattfrosten Dec 6 '13 at 9:49
up vote 2 down vote

You can use ExecuteScalar instead 

int id = (int) command.ExecuteScalar();
share|improve this answer
up vote 2 down vote

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

int ID= (int) Command.ExecuteScalar();
share|improve this answer
up vote 1 down vote 
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();
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.