Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Return ID on INSERT?

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

like image 200
Danpe Avatar asked May 06 '11 10:05

Danpe


People also ask

Does SQL insert return?

An SQL INSERT statement writes new rows of data into a table. If the INSERT activity is successful, it returns the number of rows inserted into the table. If the row already exists, it returns an error.

How do I get the last inserted ID in SQL?

SELECT @@IDENTITY @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope.


2 Answers

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); 
like image 191
AdaTheDev Avatar answered Oct 04 '22 23:10

AdaTheDev


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();         } 
like image 33
Filip De Vos Avatar answered Oct 05 '22 00:10

Filip De Vos