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

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;

}