C#/SQL get autoincremented field value

Question

I have a table with autoincremented primary key. In my code I am trying to receive the new autoincremented value when I execute each 'insert' query. Is there a way to do it programatically?

Thanks.

UPD: Assume I have a table: TABLE User ( userID INT NOT NULL AUTO_INCREMENT, name VARCHAR( 25 ) NOT NULL , email VARCHAR( 50 ) NOT NULL , UNIQUE ( userID ) );

And I when I insert new values (name and email) to this table I want automatically receive newly generated userID. Ideally I am looking for any ways to do that with a single transaction and without stored procedures.

Answer 1

Have your sql/stored proc return scope_identity() or if you are using Linq2SQL or EF the entity used for insertion gets the new id.

Answer 2

You could use the SQL statement SELECT scope_identity().

Answer 3

In the stored proc it is:

ALTER    proc [dbo].[SaveBuild](
@ID int = 0 output,
@Name varchar(150)=null,
@StageID int,
@Status char(1)=null
)
as
 SET NOCOUNT ON


    Insert into Builds
    (name, StageID, status)
    values (@Name, @StageID, @Status)

    select @ID = scope_identity()

RETURN @ID

In the C# code you have:

public int SaveBuild(ref int id, ref string Name)
{

    SqlCommand cmd = GetNewCmd("dbo.SaveBuild");

    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
    cmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;

    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
    cmd.Parameters.Add("@StageID", SqlDbType.Int).Value = 0;

    ExecuteNonQuery(cmd);
    id = (int)cmd.Parameters["@ID"].Value;

    return id;

}

Answer 4

Are you limited to building SQL on the client and sending it to the server? Cause if you can use a stored procedure, this is easy to do. In the stored proc, do the insert and then, either

1. Select Scope_Identity() as the last statement in the stored proc., or

2. Use a output parameter to the stored proc, (say named @NewPKValue) and make the last statement:

   Set @NewPKValue = Scope_Identity()

Otherwise, you need to send a batch of commands to the server that include two statements, the insert, and Select Scope_Identity() and execute the batch as though it was a select statement

Answer 5

Dependent upon your situation, you might be better off using table-valued parameters to pass your inserts to a stored procedure, then use OUTPUT INSERTED to return a table-valued parameter from your stored procedure.

It will drastically reduce the number of hits required if you're processing multiple items.