Search code examples
sql-serverinsertidentitysqlclient

How can I get inserted ID in SQL Server while using SqlClient class?

So this is a continuation of post: Best way to get identity of inserted row?

That post proposes, and I agree, to use Inserted feature to safely return inserted id column(s).

While implementing this feature, it seems SqlClient of the .net framework does not support this feature, and fails while trying to execute command, I get the following exception:

System.Data.SqlClient.SqlException: 'Cannot find either column "INSERTED" or the user-defined function or aggregate "INSERTED.Id", or the name is ambiguous.'

I'm just using: 

return (T)command.ExecuteScalar();

Where the query is:

INSERT INTO MyTable 
OUTPUT INSERTED.Id 
(Description) 
VALUES (@Description)

And the table just contains 

ID (identity int)
Description (varchar(max))

If impossible to do, is there other safe way without using variables in the middle that might affect performance?

Thanks

Solution

  • You are doing everything correctly, but you have misplaced the OUTPUT clause: it goes after the list of columns and before the VALUES, i.e.

    INSERT INTO MyTable (Description) 
OUTPUT INSERTED.Id 
VALUES (@Description)