C # / SQL get auto-increment field value
I have a table with an auto-incrementing primary key. In my code I am trying to get a new value with auto-increments on every insert request. Is there a way to do this programmatically?
Thank.
UPD: Suppose 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 when I insert new values (name and email) into this table, I want to get the newly created user ID automatically. Ideally I am looking for any ways to do this with a single transaction and no stored procedures.
source to share
Get your sql / stored proc return scope_identity () , or if you are using Linq2SQL or EF, the object used to insert gets a new identity.
source to share
In the saved proc, this 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 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;
}
source to share
Depending on your situation, you might find it helpful to use table parameters to pass your inserts to the stored procedure, then use OUTPUT INSERTED to return the stored table parameter from the stored procedure.
This will drastically reduce the number of requests required if you are processing multiple items.
source to share
Are you limited to creating SQL on the client and sending it to the server? The reason is if you can use a stored procedure it is easy to do. In your saved proc, do an insert and then,
- Select Scope_Identity () as the last statement in the saved proc. or
-
Use an output parameter for the saved proc (e.g. named @NewPKValue) and make the last statement:
Set @NewPKValue = Scope_Identity ()
Otherwise, you need to send a batch of commands to the server, which includes two statements, insert and Select Scope_Identity (), and execute the batch as if it were a select statement
source to share