Return ID on INSERT?
Solution 1
You just need to add @ID to the params collection and then retrieve it like this,
cmd.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//Now just read the value of: cmd.Parameters["@ID"].value
Or, if you prefer this syntax:
SqlParameter param = new SqlParameter("@ID", SqlDbType.Int, 4);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
Solution 2
Since SQL Server 2008 there was an OUTPUT clause added to the syntax of TSQL. This allows you to have the data which is affected by the DML query added to the Tabular Data Stream.
Although querying SCOPE_IDENTITY() after a query returns the correct information it forces SQL Server to execute 2 queries where the output clause limits this to one query.
Knowing this, the query being executed can be altered as follows (Assuming [Id]
is the name of the identity):
INSERT INTO MagicBoxes (OwnerID, [Key], Name, [Permissions], Active, LastUpdated)
OUTPUT INSERTED.Id
VALUES (@OwnerID, @BoxKey, @BoxName, 0, 1, @Date)
Another issue is not disposing of the SqlCommand
. Most of the Sql...
objects in ADO.net implement IDisposable
and should be disposed of properly.
Bringing everything together I would implement this piece of code as follows:
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
using (var cmd = new SqlCommand(@"
INSERT INTO MagicBoxes (OwnerID, [Key], Name, [Permissions], Active, LastUpdated)
OUTPUT INSERTED.Id
VALUES (@OwnerID, @BoxKey, @BoxName, 0, 1, @Date) ", conn))
{
cmd.Parameters.AddRange(new[]
{
new SqlParameter("@OwnerID", SqlDbType.Int).Value = OwnerID,
new SqlParameter("@BoxKey", SqlDbType.VarChar).Value = BoxKey,
new SqlParameter("@BoxName", SqlDbType.VarChar).Value = BoxName,
new SqlParameter("@Date", SqlDbType.DateTime).Value = DateTime.Now
});
conn.Open();
var id = (int)cmd.ExecuteScalar();
}
Solution 3
You have two options; you could declare an Output
parameter called @ID
; or - you could change the end to SELECT SCOPE_IDENTITY()
and just use:
int id = (int)cmd.ExecuteScalar();
I prefer the formal parameter approach, but ExecuteScalar
works well.
Solution 4
Add this to your parameters
SqlParameter IDParameter = new SqlParameter("@ID",SqlDbType.Int);
IDParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(IDParameter);
After executing you can retrieve the id
int id = (int)IDParameter.Value;
Comments
-
Danpe almost 2 years
I have an INSERT query and I want the DB to return the ID of the row I just inserted.
sqlString = "INSERT INTO MagicBoxes (OwnerID, Key, Name, Permissions, Active, LastUpdated) VALUES (@OwnerID, @BoxKey, @BoxName, 0, 1, @Date) SET @ID = SCOPE_IDENTITY();"; cmd = new SqlCommand(sqlString, con); cmd.Parameters.AddWithValue("@OwnerID", OwnerID); cmd.Parameters.AddWithValue("@BoxKey", BoxKey); cmd.Parameters.AddWithValue("@BoxName", BoxName); cmd.Parameters.AddWithValue("@Username", Username); cmd.Parameters.AddWithValue("@Date", DateTime.Now); cmd.ExecuteNonQuery();
I currently have this, I'm not sure what I need to do next...
-
Danpe about 13 years
Argument 4: cannot convert from 'System.Data.ParameterDirection' to 'string'
-
AdaTheDev about 13 years@Danpe = apologies, corrected above - I always find added output parameters a bit more clunky than it needs to be
-
Marc Gravell about 13 yearshaving a stored procedure wouldn't change anything; this is about the argument/result passing, which would be identical
-
FIre Panda about 13 yearsYeah you are right, I meant to say that call SP and from SP return the last seed value after insertion.
-
causa prima over 10 yearsI can't make this work, after executing the command the parameter is null. Another answer on here uses ExecuteScalar and seems to produce the right value.
-
Nate Reynolds over 6 yearsThis is the best answer.