How to get Id of row that is inserting(new)

Question

I have an C# application utilizing a SQL Server database. After inserting a row, I need the ID of the row that was created. I tried select max(id) from mytable, but the result from this is incorrect if another user has inserted a row in the meantime.

This is my code:

public string ConnectionString = @"Data Source=.;Initial Catalog=n_hesabdata;Integrated Security=True";

public void ExecuteNonQuery(string CommandText)   
{      
    try {     
      connection.ConnectionString = ConnectionString;  
      connection.Open();  
      command.CommandText = CommandText;  
      command.Connection = connection;   
      command.ExecuteNonQuery();        
      command.Dispose();   
      connection.Close();    
    } catch (Exception error) {
      connection.Close();  
    }  
}

ExecuteNonQuery("insert into Orders (Ctm_phone, Ctm_FullName) VALUES (" + Ctm_phone.text + ", N'" + Ctm_name.Text + "')");

How do I get the ID of the freshly inserted row?

It's worth noting you are open to SQL Injection. inserting raw data like that — Jonesopolis, May 01 '15 at 20:53
http://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement — Maximus2012, May 01 '15 at 20:53

Steve · Accepted Answer · 2015-05-01T22:47:06.270

You should change your code to something like this

ConnectionString = @"Data Source=.;Initial Catalog=n_hesabdata;Integrated Security=True";
public int ExecuteInsertWithIdentity(string CommandText, List<SqlParameter> prms)   
{      
    using(SqlConnection connection = new SqlConnection(ConnectionString))
    using(SqlCommand cmd = new SqlCommand(CommandText + 
                              ";SELECT SCOPE_IDENTITY();", connection))  
    {  
       connection.Open();
       if(prms != null && prms.Count > 0)
           cmd.Parameters.AddRange(prms.ToArray());
        int lastID = Convert.ToInt32(cmd.ExecuteScalar());        
        return lastID;
    }
}
....

List<SqlParameter> prms = new List<SqlParameter>();  
SqlParameter p1 = new SqlParameter() {ParameterName = "@phone", 
                                      SqlDbType = SqlDbType.NVarChar,
                                      Value = Ctm_phone.text};
SqlParameter p2 = new SqlParameter() {ParameterName = "@Name", 
                                      SqlDbType = SqlDbType.NVarChar,
                                      Value = Ctm_name.Text};
prms.AddRange(new SqlParameter[] {p1, p2});
int lastOrderID = ExecuteInsertWithIdentity(@"insert into Orders  
                                            (Ctm_phone,Ctm_FullName) 
                                            VALUES (@phone, @name)", prms);

In this way, the code that executes the query requires a parameter collection of values. This removes the possibility of Sql Injection. Now the problem of getting back the last ID inserted by the database engine in your Orders table is simply resolved appending the SELECT SCOPE_IDENTITY() to your command. The SqlClient SqlCommand class is capable to execute multiple statements in a single trip to the database and returns the last statement. In this case is the value of the IDENTITY column of the row added to your Orders table from your connection (and not from others connections)

it gives error in: prms.Add("@phone", SqlDbType.NVarChar).Value = Ctm_phone.text; prms.Add("@name", SqlDbType.NVarChar).Value = Ctm_name.Text; ****why? — Akbar Fazeli, May 01 '15 at 21:45
Thanks a lot.Just you forget add connection.open in your code.It works very well now. Thanks Steve(-; — Akbar Fazeli, May 01 '15 at 22:42

score 0 · Answer 2 · answered May 01 '15 at 20:58

Normally you should insert new entries using the entitymanager. When the id attribute it annotated as @Id it should receive the next free value upon MERGEing the object into the table when the value was NULL before.
But when you really want to INSERT explicitly using SQL, then you should split the INSERT into 1.) getting a new id from the sequence 2.) writing then the data into the table (with the sequence nimber from step 1.)

How to get Id of row that is inserting(new)

2 Answers2

Linked