ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

In a transactional connection you might get the error mentioned above. It has a simple solution but we encountered it when switching from Oracle connection to Sql connection.

Oracle Data Access component (ODP Dot Net) actually runs very well without setting command transaction. The following code will work fine with an oracle database (just demonstration, syntax may change)

     using(OracleCommand = oracleConnection.CreateCommand())
     {

          oracleConnection.Open();
          OracleTransaction =  oracleConnection .BeginTransaction();


           OracleCommand.CommandText = "Procedure Name";
           OracleCommand.CommandType = CommandType.StoredProcedure;
           OracleCommand.Parameters.Clear();
           OracleCommand.Parameters.Add(...Add Parameters...);
           OracleCommand.ExecuteNonQuery();
     }

But in sql connection, we get "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized." error, since we actually did not set the transaction property.

OdpDotNet actually handles this but SqlConnection and SqlCommand can not. So we change our Sql code block with transaction version. Which is as follows,



     using (SqlConnection conn = new SqlConnection(connectionAddress))
     {
          SqlTransaction trans = null;
          try
          {
               conn.Open();
               trans = conn.BeginTransaction();
               using (SqlCommand comm = conn.CreateCommand())
               {
                    comm.CommandText = item.Sql;
                    comm.Transaction = trans;
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.Parameters.Clear();
                    comm.Parameters.AddWithValue(parametreItem.Key, parametreItem.Value);
                    comm.ExecuteNonQuery();
               }
               trans.Commit();
          }
          catch (Exception ex)
          {
               trans.Rollback();
               throw ex;
          }
          finally
          {
               trans.Dispose();
               conn.Close();
          }
     }


If there is any mistakes let me know, so I can fix :)

Regards,
Mehmet.