MYSql Implementing Transactions.

In my last project, I needed to develop a class that simplifies the execution of multiples queries against my MySQL database. I developed the class for ease of use and "do more for less". What I want to show here is how to:

Step 1: Create DBUtility class and add these "ExecuteInsertSqlWithTrns" and "ProcessCommandParameters" methods in it. 

public class DBUtility
{
    public static long ExecuteInsertSqlWithTrns(string sql, Dictionary<string, object> parameters, MySqlConnection connection)
    {
        using (MySqlCommand command = new MySqlCommand(sql, connection))
        {
            ProcessCommandParameters(parameters, command);

            command.ExecuteNonQuery();
            return command.LastInsertedId;
        }
    }
    private static void ProcessCommandParameters(Dictionary<string, object> parameters, MySqlCommand command)
    {
        if (parameters != null)
        {
            foreach (var parameter in parameters)
            {
                if (!parameter.Key.StartsWith("@"))
                {
                    command.Parameters.Add(new MySqlParameter { ParameterName = "@" + parameter.Key, Value = parameter.Value });
                }
                else
                {
                    command.Parameters.Add(new MySqlParameter { ParameterName = parameter.Key, Value = parameter.Value });
                }
            }
        }
    }
}

Step 2: Now let's consume this generic function in business model to execute multiple insert queries:-

public class ItemRepository
{
    Dictionary<string, object> _sqlParams;
    public long InsertItem(CreateDriverModel model)
    {
        using (MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            using (MySqlTransaction trans = connection.BeginTransaction())
            {
                try
                {
                    _sqlParams = new Dictionary<string, object>();
                    _sqlParams["@FirstName"] = model.FirstName;
                    _sqlParams["@LastName"] = model.LastName;
                    _sqlParams["@Email"] = model.Email;
                    long itemId = DBUtility.ExecuteInsertSqlWithTrns("Insert into item values (firstname, lastname, email) values (@FirstName, @LastName, @Email)", _sqlParams, connection);

                    _sqlParams = new Dictionary<string, object>();
                    _sqlParams["ItemId"] = itemId;
                    _sqlParams["Location"] = Convert.ToInt32(EnumUtility.DriverStatus.Active);
                    long itemLocationId = DBUtility.ExecuteInsertSqlWithTrns("Insert into itemLocation values (ItemId, Location) values (@ItemId, @Location)", _sqlParams, connection);
                    trans.Commit();
                    return itemId;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
        }
    }
}