Unfortunately, you can't use Scope_Identity() like in your reply to Rob's comment above.
You should only call Scope_Identity() immediately after you've done an INSERT into a table that has an IDENTITY column - since its purpose is to get the identity value generated by the database engine for the inserted row.
As Rob says, doing it in a stored procedure is often the best way. If you use a stored procedure, you could do something like this:
sqlCmd.CommandText = "sp_InsertIntoUploads";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue(...);
.... for all values to store
sqlCmd.Parameters.AddWithValue("@Id", 0).Direction = ParameterDirection.Output;
var id = sqlCmd.ExecuteNonQuery();
And your stored procedure would look like this:
CREATE PROCEDURE dbo.sp_InsertIntoUploads
(
... values to put into the columns...,
@Id INT OUTPUT
)
AS
BEGIN
INSERT INTO uploadsTable(columns...)
VALUES (values...)
SELECT @Id = Scope_Identity()
END
GO
If you prefer
not to use OUTPUT parameters from the stored procedure you can use
ExecuteScalar()
and in the sproc use
SELECT Scope_Identity()
.