MySqlCommandBuilder in C#

Question

I want to insert data to mysql database using commandbuilder at the same time i want to retrive the id of each recode that i inserted to the table.

id is autoincrement.

Answer 1

MySqlCommand has a member called LastInsertedId which will give you the last inserted Id.

MySqlCommand cmd= _conn.CreateCommand();
cmd.CommandText = "your insert query here";
cmd.ExecuteNonQuery();
var insertedId= cmd.LastInsertedId;

Answer 2

@Jack J Jun
I use below code. the data is from local db and inserting to web db . Record may increase to 1000

  public ArrayList ExportItems(MySqlConnection connection, ArrayList arrListItem)  
        {  
            if (connection.State == ConnectionState.Open)  
                connection.Close();  
            connection.Open();  
            ArrayList items = new ArrayList();  
            try  
            {  
                int itemid;  
                
                 cmd = new MySqlCommand();  
                cmd.Connection = connection;  
                cmd.CommandType = CommandType.Text;  
                cmd.CommandText = "INSERT INTO items(name,detail,status,created_at,updated_at) VALUES(@NAME,@DETAIL,@STATUS,@CREATEDDATE,@MODIFIEDDATE);";  
                for (int i = 0; i < arrListItem.Count; i++)  
                {  
                    ItemDetails itemdetails = (ItemDetails)arrListItem[i];  
                    cmd.Parameters.Add("@NAME", MySqlDbType.VarChar).Value = itemdetails.itemName;  
                    cmd.Parameters.Add("@DETAIL", MySqlDbType.VarChar).Value = itemdetails.discription;  
                    cmd.Parameters.Add("@STATUS", MySqlDbType.VarChar).Value = itemdetails.status;  
                    cmd.Parameters.Add("@CREATEDDATE", MySqlDbType.DateTime).Value = DateTime.Now;  
                    cmd.Parameters.Add("@MODIFIEDDATE", MySqlDbType.DateTime).Value = DateTime.Now;  
                    cmd.ExecuteNonQuery();  
                    itemid = Convert.ToInt32(cmd.LastInsertedId);  
                    itemdetails.itemID = itemid;  
                    cmd.Parameters.Clear();  
                    items.Add(itemdetails);  
                }  
  
                return items;  
            }  
            catch (Exception ex)  
            {  
                resultString = ex.Message;  
                return null;  
            }  
        }