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.

Answer 1

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

Answer 2

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(); }

Answer 3

Thanks a bundle that woked.  :D thanks again.,

 

Answer 4

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

Answer 5

This was a great example for a novice like me.

thank you very much.

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

cp

Answer 6

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;

Answer 7

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.

Answer 8

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() );

}

 

Answer 9

Thank you. I think I got something from it.

Answer 10

Woo hoo!

 

Thanks, Pieter!