Get the Id of Inserted Row Using C#

Execute Insert command and return inserted Id in Sql

The following solution will work with sql server 2005 and above. You can use output to get the required field. inplace of id you can write your key that you want to return. do it like this

FOR SQL SERVER 2005 and above

    using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con))
    {
        cmd.Parameters.AddWithValue("@na", Mem_NA);
        cmd.Parameters.AddWithValue("@occ", Mem_Occ);
        con.Open();

        int modified =(int)cmd.ExecuteScalar();

        if (con.State == System.Data.ConnectionState.Open) 
            con.Close();

        return modified;
    }
}

FOR previous versions

    using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ)  VALUES(@na,@occ);SELECT SCOPE_IDENTITY();",con))
    {
        cmd.Parameters.AddWithValue("@na", Mem_NA);
        cmd.Parameters.AddWithValue("@occ", Mem_Occ);
        con.Open();

        int modified = Convert.ToInt32(cmd.ExecuteScalar());

        if (con.State == System.Data.ConnectionState.Open) con.Close();
            return modified;
    }
}

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

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)

how to get id of last inserted row

So a little trick to do this is to change you statement to this:

INSERT INTO tbl (...) VALUES (...); SELECT LAST_INSERT_ID()

and then insert the row with ExecuteScalar which will return you the id; you can then update the object accordingly. So the entire snippet might look like this:

using (var conn = new MySqlConnection(connString))
using (var cmd = new MySqlCommand(sql, conn))
{
    var result = cmd.ExecuteScalar();
    int id;
    if (int.TryParse(result, out id))
    {
        // set the object's id here
    }
}

How to get the autoincremented ID inserted row using SQLiteCommand object

select last_insert_rowid();

And you will need to execute it as a scalar query.

string sql = @"select last_insert_rowid()";
long lastId = (long)command.ExecuteScalar(sql); // Need to type-cast since `ExecuteScalar` returns an object.

Get the id of inserted row using C#

[Edit: added "select" before references to last_insert_id()]

What about running "select last_insert_id();" after your insert?

MySqlCommand comm = connect.CreateCommand();
comm.CommandText = insertInvoice;
comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', "  
    + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ");";
    + "select last_insert_id();"

int id = Convert.ToInt32(comm.ExecuteScalar());

---

Edit: As duffymo mentioned, you really would be well served using parameterized queries like this.

---

Edit: Until you switch over to a parameterized version, you might find peace with string.Format:

comm.CommandText = string.Format("{0} '{1}', {2}, {3}, {4}, {5}); select last_insert_id();",
  insertInvoice, invoiceDate.ToString(...), bookFee, adminFee, totalFee, customerID);

How can I retrieve the ID of the inserted row using ExecuteScalar function and the SCOPE_IDENTITY() function

First things first you need to use Store Procedures instead of just writing your SQL statements inside your application.

Second what you want is to run SCOPE_IDENTITY as part of your insert statement like

  INSERT INTO tblFiles (FileName, FilePath) 
         VALUE(@FileName, @FilePath);

  SELECT  SCOPE_IDENTITY() 

And then change the execution statement in .NET to ExecuteScalar

var result = cmd.ExecuteScalar();

How to retrieve inserted id after inserting row in SQLite using C#?

// IDbCommand cmd
cmd.ExecuteScalar();
return ((SQLiteConnection) cmd.Connection).LastInsertRowId;

Very easy :)

---

Related Topics