question

MousmiNishad-0663 avatar image
0 Votes"
MousmiNishad-0663 asked MousmiNishad-0663 commented

MySqlCommandBuilder in C#

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.

dotnet-csharpwindows-formsdotnet-adonet
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MousmiNishad-0663, there is no need to use MySqlCommandBuilder when we insert data to mysql database. why do you want to use MySqlCommandBuilder? Is the id a self-growing ID or a manually added ID?

0 Votes 0 ·

i use insert query for inserting.. but i have 100+ rows to be inserted to database in single execution. so i think by using commandbuilder will be great option.

0 Votes 0 ·

@MousmiNishad-0663 , where is from the 100+ rows, is some random data or from file? I think use Stored procedure or the loop will be a good choice to insert the data to database. Could you provide some code you are writing with me?

0 Votes 0 ·
sreejukg avatar image
0 Votes"
sreejukg answered

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;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MousmiNishad-0663 avatar image
0 Votes"
MousmiNishad-0663 answered MousmiNishad-0663 commented

@JackJJun-MSFT
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;
             }
         }
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MousmiNishad-0663, If you think the method may be two slow for huge data, you can consider the method in the link Most efficient way to insert Rows into MySQL Database.


0 Votes 0 ·

any other solution?

0 Votes 0 ·