ASP.NET MySQL update multiple records

Question

I have a web page that needs to update multiple records. This page gets all the information and then begins a transaction sending multiple UPDATE queries to the data base.

foreach row
{
Prepare the query
 Hashtable Item = new Hashtable();
 Item.Add("Id", Id);
 Item.Add("Field1", Field1);
 Item.Add("Field2", Field2);
 Item.Add("Field3", Field3);
...
}

Then we launch the ytransaction DO CHANGES()

public void execute_NonQuery_procedure_transaction(string StoredProcedure, List<Hashtable> Params)
{
    using (MySqlConnection oConnection = new MySqlConnection(ConfigurationManager.AppSettings[DB]))
    {
        MySqlTransaction oTransaction;
        bool HasErrors = false;

        oConnection.Open();
        oTransaction = oConnection.BeginTransaction();

        try
        {
            MySqlCommand oCommand = new MySqlCommand(StoredProcedure, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.Transaction = oTransaction;

            foreach (Hashtable hParams in Params)
            {
                oCommand.Parameters.Clear();

                IDictionaryEnumerator en = hParams.GetEnumerator();
                while (en.MoveNext())
                {
                    oCommand.Parameters.AddWithValue("_" + en.Key.ToString(), en.Value);
                    oCommand.Parameters["_" + en.Key.ToString()].Direction = ParameterDirection.Input;
                }

                oCommand.ExecuteNonQuery();

            }
        }
        catch (Exception e)
        {
            HasErrors = true;
            throw e;
        }
        finally
        {
            if (HasErrors) 
                oTransaction.Rollback();
            else
                oTransaction.Commit();

            oConnection.Close();
        }
    }
}

Is there another way to do this or this is the most efficient way?

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

It depends on the situation, like if you have multiple row updates or adding new rows or deleting some rows or a combination of these, which modifies the database table then, the efficient way to do this is to have Batch Update...

Please go through this link Batch Update

Hope this helps...

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 28, 2011 at 7:25

Bravo

3,4814 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Davide Piras · Accepted Answer · 2011-09-28 07:18:15Z

1

it looks fine to me, you could eventually do not clear the Command.Parameters list but just assign the values on following iterations but probably this leads to no visible improvements.

pay attention your throw is wrong, in C# don't use throw e; but simply throw;.

answered Sep 28, 2011 at 7:18

Davide Piras

44.7k10 gold badges103 silver badges150 bronze badges

Collectives™ on Stack Overflow

ASP.NET MySQL update multiple records

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related