2

I faced a problem when I try to use the ExecutedScalar() command.

I built a web application, in the web App the user need to fill his details like name, family, hobbies (array), etc.

After the user pushing on the submit button, I collect all the information the user fill and create an object Person and connect to DB and update the personTbl table.

personTbl - I have an ID (identity(1,1)) filed, that means every time I add a new person he gets from SQL Server a new ID.

My question is:

How can I add a new person to DB and at the same time to get is ID from SQL Server?

I use ExecuteNonQuery() to insert the new person, but at the same time, I need to retrieve his ID (using ExecutedScalar()) because I need to use her to do another insert command to a different table.

GSerg
  • 76,472
  • 17
  • 159
  • 346
Gavriel
  • 51
  • 5
  • 1
    Possible duplicate of [Execute Insert command and return inserted Id in Sql](https://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql) – Igor Dec 07 '18 at 10:16

1 Answers1

0

You can use OUTPUT clause.

Refer Output Inserted

e.g.

 using(SqlCommand cmd=new SqlCommand("INSERT INTO table1(column1,column2,column3) output INSERTED.ID VALUES(val1,val2,val3)",con))
    {       
        con.Open();
        int id=(int)cmd.ExecuteScalar();
        con.Close();
        return id;
    }
Chetan Sanghani
  • 2,058
  • 2
  • 21
  • 36