ADO.Net Transaction Not Working

Question

I have the following code (I'm new to .NET transactions) and it does seem to be executing at all. It's called through a webservice and is difficult for me to debug:

 public void UpdateJailFiles(List<RMS.NameFile> names, RMS.Incident incident, int currentDate, int currentTime, int incidentKey)
    {
        String library = RMSLIBRARY;

        IDbTransaction transaction;

        using (IDbConnection conn = Connection)
        {
            conn.Open();
            transaction = conn.BeginTransaction();
            try
            {
                names.ForEach(nameFile =>
                    {
                        IDbCommand command = conn.CreateCommand();
                        command.CommandText = "call " + library + ".SP_NAMEFILE_UPDATE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandTimeout = 0;
                        command.Transaction = transaction;

                        IDbDataParameter param = command.CreateParameter();
                        param.ParameterName = "@INCIDENTKEY";
                        param.DbType = DbType.Int32;
                        param.Value = nameFile.IncidentKey;
                        command.Parameters.Add(param);

                        ///Many more params...

                        command.ExecuteNonQuery();
                    });

                IDbCommand command2 = conn.CreateCommand();
                command2.CommandText = "call " + library + ".SP_INCIDENTFILE_UPDATE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                command2.CommandType = CommandType.StoredProcedure;
                command2.Transaction = transaction;

                IDbDataParameter param1b = command2.CreateParameter();
                param1b.ParameterName = "@CASENUMBER";
                param1b.DbType = DbType.String;
                param1b.Value = incident.CaseNumber;
                command2.Parameters.Add(param1b);

                IDbDataParameter param2b = command2.CreateParameter();
                param2b.ParameterName = "@INCIDENTFROMDATE";
                param2b.DbType = DbType.Int32;
                param2b.Value = incident.IncidentFromDate;
                command2.Parameters.Add(param2b);

                ///Many more params...

                command2.ExecuteNonQuery();

                transaction.Commit();
            } // end try
            catch (Exception)
            {
                transaction.Rollback();
            }
        } // end using

    }

See anything wrong with this code?

Answer 1

Your use of a property (Connection) to load up the using statement is suspect to me. Typically using is used on a new, local object instance like so:

    using (IDbConnection conn = new MyConnectionClass())
    {
    }

When you exit the using statement, your IDbConnection gets Dispose() called on it, but I don't see anywhere that the property it is updated to reflect that status. Is that what you want?

You may also need to Dispose() your IDbCommand, it's not clear here - but for SqlCommand, that's true.

It could also be helpful to wrap the whole code that accesses the DB in a try..except so you can diagnose and/or propagate ANY DB exception.