Return value from SQL Server Insert command using c#

by

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

You can use SqlCommand.ExecuteScalar to execute the insert command and retrieve the new ID in one query.

using (var con = new SqlConnection(ConnectionString)) {
    int newID;
    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newID = (int)insertCommand.ExecuteScalar();
    }
}

More Related Contents:

  1. Calling stored procedure with return value
  2. SQL command INSERT is working but the data not appear in table
  3. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  4. Call a stored procedure with parameter in c#
  5. Insert 2 million rows into SQL Server quickly
  6. ExecuteNonQuery: Connection property has not been initialized.
  7. How to pass sqlparameter to IN()? [duplicate]
  8. How do I return multiple result sets with SqlCommand?
  9. SqlConnection SqlCommand SqlDataReader IDisposable
  10. SqlBulkCopy from a List
  11. Use of SqlParameter in SQL LIKE clause not working
  12. How to run multiple SQL commands in a single SQL connection?
  13. Connection timeout for SQL server
  14. MultipleActiveResultSets=True or multiple connections?
  15. WHERE IN (array of IDs)
  16. How can I retrieve a list of parameters from a stored procedure in SQL Server
  17. Creating a SQL Server table from a C# datatable
  18. SQL: Update a row and returning a column value with 1 query
  19. How to keep single SQL Server connection instance open for multiple request in C#?
  20. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  21. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  22. Using SqlDataAdapter to insert a row
  23. What size do you use for varchar(MAX) in your parameter declaration?
  24. Add WHERE clauses to SQL dynamically / programmatically
  25. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  26. Fastest method for SQL Server inserts, updates, selects
  27. Why does the SqlParameter name/value constructor treat 0 as null?
  28. How do I connect to a SQL database from C#?
  29. Passing array to a SQL Server Stored Procedure
  30. How to give ADO.NET Parameters

Leave a Comment