none
c# Insert into Sql

    Question

  • Is there anyway to get the ID of an item inserted into a database at the time of insertion?

    Here's what I mean, I have a database table with 5 columns.  First column is called ID, where sql automatically assigns a number.  When I make the call from c# it looks something like this.

    string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";

    db.Open();
    try
    {
    SqlCommand cmdIns = new SqlCommand(sqlIns, db.Connection);
    cmdIns.Parameters.Add("@name", info);
    cmdIns.Parameters.Add("@information", info1);
    cmdIns.Parameters.Add("@other", info2);
    cmdIns.ExecuteNonQuery();
    cmdIns.Dispose();
    cmdIns = null;
    }
    catch(Exception ex)
    {
    throw new Exception(ex.ToString(), ex);
    }
    finally
    {
    db.Close();
    }

    Now within this statement I am wondering if I can gather the "ID" field. Because some items will be deleted from this table, I would not be able to use the scalar command.  Please advise or send me to a tutorial that explains how to do this.  Thank you much in advance.

    Friday, January 13, 2006 1:49 PM

Answers


  • string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
    db.Open();
    try
    {
     SqlCommand cmdIns = new SqlCommand(sqlIns, db.Connection);
     cmdIns.Parameters.Add("@name", info);
     cmdIns.Parameters.Add("@information", info1);
     cmdIns.Parameters.Add("@other", info2);
     cmdIns.ExecuteNonQuery();
     
     cmdIns.Parameters.Clear();
     cmdIns.CommandText = "SELECT @@IDENTITY";
     
     // Get the last inserted id.
     int insertID = Convert.ToInt32( cmdIns.ExecuteScalar() );
     
     cmdIns.Dispose();
     cmdIns = null;
    }
    catch(Exception ex)
    {
    throw new Exception(ex.ToString(), ex);
    }
    finally
    {
    db.Close();
    }

     

    Friday, January 13, 2006 3:13 PM
    Moderator

All replies

  • sql does have a command that will return that last Identity from a command, @@IDENTITY or SCOPE_IDENTITY which is more specific to stored procedures and functions triggers etc... you should be able to just return this value to some var
    Friday, January 13, 2006 2:22 PM

  • string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
    db.Open();
    try
    {
     SqlCommand cmdIns = new SqlCommand(sqlIns, db.Connection);
     cmdIns.Parameters.Add("@name", info);
     cmdIns.Parameters.Add("@information", info1);
     cmdIns.Parameters.Add("@other", info2);
     cmdIns.ExecuteNonQuery();
     
     cmdIns.Parameters.Clear();
     cmdIns.CommandText = "SELECT @@IDENTITY";
     
     // Get the last inserted id.
     int insertID = Convert.ToInt32( cmdIns.ExecuteScalar() );
     
     cmdIns.Dispose();
     cmdIns = null;
    }
    catch(Exception ex)
    {
    throw new Exception(ex.ToString(), ex);
    }
    finally
    {
    db.Close();
    }

     

    Friday, January 13, 2006 3:13 PM
    Moderator
  • Thanks a bundle that woked.  :D thanks again.,

     

    Friday, January 13, 2006 4:45 PM
  • Hello.

    I'm using DataSet's and TableAdapters to handle the operations with the database. I wonder if there is any way to query the database for the last inserted ID by one TableAdapter.

    I would appreciate any help. Thanks.

     

    Nelson Neves

    Monday, June 05, 2006 11:22 PM
  • This was a great example for a novice like me.

    thank you very much.

    One question, can you substitue @@IDENTITY with SCOPE_IDENTITY?

    cp

    Tuesday, February 20, 2007 8:29 PM
  • You can substitute @@IDENTITY width SCOPE_IDENTITY.  Check out the MSDN documentation for SCOPE_IDENTITY.  Long story short though, in the majority of cases you will want to use SCOPE_IDENTITY.

    Check out this code taken from http://www.davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx for an even slicker way to get the identity:
    string connectionString = "...Northwind...";
    int id = 0;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    using (SqlCommand insertCommand = connection.CreateCommand())
            {
    insertCommand.CommandText
    = "INSERT INTO Shippers
    (CompanyName, Phone) VALUES (@CompanyName,
    @Phone) SET @ShipperID = SCOPE_IDENTITY()
    ";

    SqlParameter companyNameParameter
    = new SqlParameter("@CompanyName", SqlDbType.NVarChar,40);
    companyNameParameter.Value
    = companyName;
    insertCommand.Parameters.Add(companyNameParameter);

    SqlParameter phoneParameter
    = new SqlParameter ("@Phone", SqlDbType.NVarChar, 24);
    if (phone.Length == 0)
    phoneParameter.Value
    = DBNull.Value;
    else
    phoneParameter.Value
    = phone;
    insertCommand.Parameters.Add(phoneParameter);

    SqlParameter shipperIDParameter
    = new SqlParameter("@ShipperID", SqlDbType.Int);
    shipperIDParameter.Direction
    = ParameterDirection.Output;
    insertCommand.Parameters.Add(shipperIDParameter);

    insertCommand.Connection.Open();
    insertCommand.ExecuteNonQuery();

    id
    = (int)shipperIDParameter.Value;
    }
    }

    return id;


    Thursday, February 12, 2009 5:38 PM
  • Note that this is EXTREMELY dangerous.  If there is an insert into ANY table within the database between th eexecution of the insert and the execution of the select, the select will return the wrong identity value.  Instead the insert statement should be combined wiyth the select scope_identy into a single paramterized query, the value of select can be retrieved from an out parameter.
    Tuesday, January 26, 2010 7:26 PM
  • To Dink and any other who is worried about this issue he pointed out:

    I'd say if you are affraid that another proces will insert an item, just make a synchronized block beginning before ExecuteNonQuery and ending after ExecuteScalar.  

    private object forLockingAnObject = new object();

    in the method described above:

    lock(forLockingAnObject 0{

     cmdIns.ExecuteNonQuery();

     
      cmdIns.Parameters.Clear();
      cmdIns.CommandText = "SELECT @@IDENTITY";
     
     // Get the last inserted id.
     i nt insertID = Convert.ToInt32( cmdIns.ExecuteScalar() );

    }

     

    Tuesday, January 04, 2011 1:10 PM
  • Thank you. I think I got something from it.

    Thursday, March 17, 2011 4:32 PM
  • Woo hoo!

     

    Thanks, Pieter!

    Saturday, July 09, 2011 4:07 AM