How create a row and take his id at the same time in sql server and c#

Question

I'm having a simple problem but cant find a solution:

I'm creating a row in my table client, but I don't know how recovery the id of the table that I just created, for example:

ALTER proc [dbo].[spinsert_client]
@idclient int output,
@name varchar(20),
@surname varchar(40),
as
insert into client(name,surname)
values (@name,@surname)

here I insert a client, now I want recovery that exact same idclient to insert "products" with it without have to manually search this client , I tried recovering the last row of the client table but I realise that if more than one person is using the same database in different computers it can be a problem, so I need create a client and recovery his id at the same time (it is an assumption I don't know). Im using sql server and Visual studio with c#

sorry for my bad English and thanks for the attention

edit------------------- solution:

ALTER proc [dbo].[spinsert_client]
@idclient int output,
@name varchar(20),
@surname varchar(40)
as
insert into client(name,surname)
values (@name,@surname)
Select @@IDENTITY as newId;


them in my c# code:

rpta= SqlCmd.ExecuteScalar().ToString();

Are you trying to say that you want to get an auto-increment primary key ID back after doing an insert? Can you post the code you're using? — SpaceSteak, Aug 06 '15 at 11:00

score 1 · Answer 1 · answered Aug 06 '15 at 11:02

1

The best way is to use the OUTPUT clause. Here is an example that just captures the new id:

ALTER proc [dbo].[spinsert_client] (
    @idclient int output,
    @name varchar(20),
    @surname varchar(40)
)
as
begin
    declare @output table (idclient int);

    insert into client(name, surname)
         output inserted.idclient into @output;
        values (@name, @surname);

    select *
    from @output;
end;  -- spinsert_client

answered Aug 06 '15 at 11:02

Gordon Linoff

1,242,037
58
646
786

that looks good but how I convert that output into a string in my c# programm? edited my answer.Im triying do it with EXECUTESCALAR but always get and error – pau Fer Aug 06 '15 at 12:24
You would either use a function or pass in an `out` parameter. – Gordon Linoff Aug 06 '15 at 23:10

score 0 · Answer 2 · answered Aug 06 '15 at 11:03

In your C# code you have somewhere a sql Statement defined:

string sqlStatement = "INSERT INTO ... (field list) OUTPUT INSERTED.yourfieldwithid values (value list) ";

and use it with ExecuteScalar() for getting the result value (I assume you know how to use Connection and command object)

score 0 · Answer 3 · answered Aug 06 '15 at 11:05

You can do this:

string query = "INSERT INTO client" +
             " (name, surname)" +
             " VALUES (@Name, @Surname);" +
             " SELECT SCOPE_IDENTITY();";

using (var dbconn = new SqlConnection("your connection string here") )
using (var dbcm = new SqlCommand(query, dbconn) )
{
    dbcm.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "name value";
    dbcm.Parameters.Add("@Surname", SqlDbType.VarChar, 40).Value = "surname value";

    dbconn.Open();
    var insertedID = (int)dbcm .ExecuteScalar();
}

score 0 · Answer 4 · edited May 23 '17 at 11:51

Check this SO post explaining every way of retrieving the inserted id from the table.

To answer your question, both OUTPUT_CLAUSE and IDENT_CURRENT can be used in this scenario but i would recomment IDENT_CURRENT because an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back.

Use it like:

using (SqlCommand com = new SqlCommand("INSERT INTO cient(name, surname)"+ "VALUES (@Name, @Surname) SELECT IDENT_CURRENT('client'); ", con))

I would not suggest SCOPE_IDENTITY or @@IDENTITY because it may return wrong values (null) if you're not using SQL Server 2008 R2 SP2 or higher ( source - last row from the page.) especially for your requirement (inserting the value in some other table).

How create a row and take his id at the same time in sql server and c#

4 Answers4