locked
Get PK Id of newly inserted row using C# RRS feed

  • Question

  • User-734459410 posted

    I have a table that uses a bigint (auto-incremented using the IDENTITY(1,1) specification when the table was created) called "Id" that obviously increments each time a record is added.

    Below is how the PK is defined in the table (this is not the complete definition, but covers the fields that are of importance for the question):

    CREATE TABLE [dbo].[ShipmentDetail](
    	[Id] [bigint] IDENTITY(1,1) NOT NULL,
    [TrackingNumber] [varchar](64) NOT NULL, CONSTRAINT [PK_ShipmentDetail] PRIMARY KEY CLUSTERED ( [Id] ASC )

    Let's say I just added a new record/row to the table.   How can I get the Id of the newly added row?

    Monday, November 27, 2017 4:19 PM

All replies

  • User2103319870 posted

    cbassett

    How can I get the Id of the newly added row?

    You can use SCOPE_IDENTITY to get the inserted id value.

    Sample Code

     //Change the connection string as per your design
                using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SAMPLEDBConnectionString"].ConnectionString))
                {
                    con.Open();
                    //Insert QUery with Scope_Identity
                    using (SqlCommand cmd = new SqlCommand("insert [ShipmentDetail](TrackingNumber) Values(@TrackingNumber); SELECT SCOPE_IDENTITY()   ", con))
                    {
                        cmd.CommandType = CommandType.Text;
                        {
                            //Add parameter values
                            cmd.Parameters.AddWithValue("@TrackingNumber", "Sample123");
                            //Get the inserted query
                            int insertedID = Convert.ToInt32(cmd.ExecuteScalar());
                        }
                    }
                }

    EDIT: Added Link

    Monday, November 27, 2017 4:51 PM
  • User753101303 posted

    Hi,

    See https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-identity-or-autonumber-values#retrieving-sql-server-identity-column-values

    Your INSERT statement will be followed by retrieving SCOPE_IDENTITY with the same possible options than usual :
    - a return value or ExecuteScalar if this is the only value you need
    - or a SELECT Id,Col FROM MyTable WHERE Id=SCOPE_IDENTITY() if you have multiple server side generated columns

    If using Entity Framework it is done for you.

    Monday, November 27, 2017 4:52 PM
  • User2103319870 posted

    An Alternative option is to use Output Clause in your insert query like below

    insert [ShipmentDetail](TrackingNumber) OUTPUT INSERTED.ID Values(@TrackingNumber);

    Sample C# Code

    //Change the connection string as per your design
                using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SAMPLEDBConnectionString"].ConnectionString))
                {
                    con.Open();
                    //Insert QUery with Scope_Identity
                    using (SqlCommand cmd = new SqlCommand("insert [ShipmentDetail](TrackingNumber) OUTPUT INSERTED.ID Values(@TrackingNumber);", con))
                    {
                        cmd.CommandType = CommandType.Text;
                        {
                            //Add parameter values
                            cmd.Parameters.AddWithValue("@TrackingNumber", "Sample123");
                            //Get the inserted query
                            int insertedID = Convert.ToInt32(cmd.ExecuteScalar());
                        }
                    }
                }
    Monday, November 27, 2017 4:57 PM
  • User-335504541 posted

    Hi <g class="gr_ gr_16 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="16" data-gr-id="16">cbassett</g>,<o:p></o:p>

    <o:p> </o:p>

    You could also try to use the @@ IDENTITY to return to the last inserted identification value.<o:p></o:p>

    <o:p></o:p>

    For example:

                string constr = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ToString();//connection string
    con = new SqlConnection(constr); SqlCommand cmd = new SqlCommand("insert into ShipmentDetail(TrackingNumber) values(@TrackingNumber)", con); cmd.Parameters.AddWithValue("@TrackingNumber", "tr1"); con.Open(); cmd.ExecuteNonQuery(); string query2 = "Select @@Identity as newId from ShipmentDetail"; cmd.CommandText = query2; cmd.CommandType = CommandType.Text; cmd.Connection = con; var newId = cmd.ExecuteScalar(); con.Close();

    And here is the reference:

    https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql

    Best Regards,

    Billy

    Tuesday, November 28, 2017 5:34 AM
  • User-734459410 posted

    Can this be doing using the E-F framework do I have to use regular ADO.NET and SQL queries as described above to get the Id value back after the INSERT? I know that you can't technically request the value from the DB server until AFTER the insert occurs obviously.

    Tuesday, November 28, 2017 4:59 PM
  • User2103319870 posted

    cbassett

    Can this be doing using the E-F framework

    In EF, its already implemented for us.Please see sample code below

      AdventureWorks2016CTP3Entities dbContext = new AdventureWorks2016CTP3Entities();
                //Model class
                ShipmentDetail shp = new ShipmentDetail();
                shp.TrackingNumber = "Sample1";
                dbContext.ShipmentDetails.Add(shp);
                dbContext.SaveChanges();
                //Get the inserted id
                int insertedid = Convert.ToInt32(shp.Id);

    Model class details

     public partial class ShipmentDetail
        {
            public long Id { get; set; }
            public string TrackingNumber { get; set; }
        }
    Tuesday, November 28, 2017 5:17 PM