CODEWITHSUNDEEP
c#sqlsql-serversql-server-2012
pau Fer
pau Fer

Reputation: 130

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

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();

Upvotes: 0

Views: 84

Answers (4)

Zippy
Zippy

Reputation: 1824

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).

Upvotes: 0

Klaudiusz bryjamus
Klaudiusz bryjamus

Reputation: 326

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();
}

Upvotes: 0

Thomas Krojer
Thomas Krojer

Reputation: 1018

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

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

Upvotes: 1

Related Questions