CODEWITHSUNDEEP
asp.netmysqltransactions
John Mathison
John Mathison

Reputation: 914

ASP.NET MySQL update multiple records

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?

Upvotes: 0

Views: 492

Answers (2)

Bravo
Bravo

Reputation: 3451

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...

Upvotes: 2

Davide Piras
Davide Piras

Reputation: 44605

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;.

Upvotes: 1

Related Questions