insert the values using Mysqldataadapter.update( )

string InsertQuery = ""; 
                     MySqlTransaction transaction;
               MySqlConnection con1 = new MySqlConnection();
                     MySqlDataAdapter ODA;
                     string server = loggerConnectionString.servername;
con1.ConnectionString ="server="localhost";uid=root;pwd=;database=globasys;";

                     con1.Open();
                     transaction = con1.BeginTransaction();

                     ODA = new MySqlDataAdapter();

                     InsertQuery = "Insert into tr_config_unit_params " + Environment.NewLine;
                     InsertQuery += "(unit_param_id,unit_id, unit_param_desc, unit_param_opc_progid, unit_param_host, unit_param_link, unit_param_data_type, unit_param_type) " + Environment.NewLine;
                     InsertQuery += " VALUES(@unit_param_id,@unit_id,@unit_param_desc, @unit_param_opc_progid, @unit_param_host, @unit_param_link, @unit_param_data_type, @unit_param_type)";;
                     ODA.InsertCommand =new   MySqlCommand(InsertQuery, con1);
                     ODA.InsertCommand.Parameters.Add("@unit_param_id", MySqlDbType.Int32);
                     ODA.InsertCommand.Parameters.Add("@unit_id"", MySqlDbType.Int32);
                     ODA.InsertCommand.Parameters.Add("@unit_param_desc", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_opc_progid", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_host", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_link", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_data_type", MySqlDbType.Int32);
                     ODA.InsertCommand.Parameters.Add("@unit_param_type", MySqlDbType.Int32);

                     ODA.InsertCommand.Transaction = transaction;

                     DataSet ds = new DataSet();
                     ds = dt;

                  int y=   ODA.Update(dt,"tr_config_unit_params");


                     transaction.Commit();
                  con1.Close();
 

i have insert the 150000 rows using Mysqldataadapter.update() but the query execute successfully but there is no row insert into the database tabl开发者_运维问答e in MYSQL

thanks in advance.....

Update function of MySqlDataAdapter is overloaded. Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet.

So, you can use following code:

string conn = "server="localhost";uid=root;pwd=;database=globasys;";

using (MySqlConnection con1 = new MySqlConnection(conn))
{
con1.Open();

MySqlTransaction transaction = con1.BeginTransaction();

string InsertQuery = "Insert into tr_config_unit_params " + Environment.NewLine;
InsertQuery += "(unit_param_id,unit_id, unit_param_desc, unit_param_opc_progid, unit_param_host, unit_param_link, unit_param_data_type, unit_param_type) " + Environment.NewLine;
InsertQuery += " VALUES(@unit_param_id,@unit_id,@unit_param_desc, @unit_param_opc_progid, @unit_param_host, @unit_param_link, @unit_param_data_type, @unit_param_type)";;

MySqlCommand command = new MySqlCommand(InsertQuery, con1);
command.Transaction = transaction;

try
{
    //dt is a DataTable
    foreach(DataRow dataRow in dt)
    {
        command.Parameters.Clear()
        command.Parameters.AddWithValue("@unit_param_id", dataRow["unit_param_id"]);
        command.Parameters.AddWithValue("@unit_id", dataRow["unit_id"]);
        command.Parameters.AddWithValue("@unit_param_desc", dataRow["unit_param_desc"]);
        command.Parameters.AddWithValue("@unit_param_opc_progid", dataRow["unit_param_opc_progid"]);
        command.Parameters.AddWithValue("@unit_param_host", dataRow["unit_param_host"]);
        command.Parameters.AddWithValue("@unit_param_link", dataRow["unit_param_link"]);
        command.Parameters.AddWithValue("@unit_param_data_type", dataRow["unit_param_data_type"]);
        command.Parameters.AddWithValue("@unit_param_type", dataRow["unit_param_type"]);

        command.ExecuteNonQuery();
    }

    transaction.Commit();
}
catch(MySqlException mySqlEx)
{
    transaction.Rollback();

    throw mySqlEx;
}
}